GAS中的调用表功能 [英] calling sheet function in GAS

查看：47
本文介绍了GAS中的调用表功能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是否可以在google-app-script中调用工作表函数?

Is there a way of calling sheet function in google-app-script?

我想调用MATCH()，IMPORTRANGE()和INDEX()函数来计算函数定义中的返回值，如下所示.

I want to call MATCH(), IMPORTRANGE(), and INDEX() functions to compute the return value in function definition like below.

function abc(foo, bar) {
  a = MATCH(foo, IMPORTRANGE("address of sheet", "A:A"),0)
  b = MATCH(bar, IMPORTRANGE("address of sheet", "B:B"),0)

  return INDEX(IMPORTRANGE("address of sheet", "C:Z"),a,b)
}





应用@Tanaike提供的示例 

function doGet(e) {
  const [sheet1, sheet2, sheet3] = [e.parameter.url1, e.parameter.url2, e.parameter.url3].map(f => SpreadsheetApp.openByUrl(f).getSheets()[0]);
  const a = sheet1.getRange("A:A").createTextFinder(e.parameter.foo).findNext().getRow();
  const b = sheet2.getRange("B:B").createTextFinder(e.parameter.bar).findNext().getRow();
  const res = sheet3.getRange("C:Z").offset(a - 1, b - 1).getValue();
  return ContentService.createTextOutput(res);
}

function abc(foo, bar) {
  const url1 = "https://docs.google.com/spreadsheets/d/###/edit";
  const url2 = "https://docs.google.com/spreadsheets/d/###/edit";
  const url3 = "https://docs.google.com/spreadsheets/d/###/edit";
  const baseUrl = "https://script.google.com/a/###/macros/s/###/exec";
  
  const res = UrlFetchApp.fetch(`${baseUrl}?url1=${encodeURIComponent(url1)}&url2=${encodeURIComponent(url2)}&url3=${encodeURIComponent(url3)}&foo=${foo}&bar=${bar}`);
  console.log(res.getContentText())
  return res.getContentText();
}

在上面基本上是样本的副本，abc函数返回#VALUE错误，长度为50,000个字母.
控制台日志如下所示开始.

With above which is basically the copy of sample, the abc function returns #VALUE error with length of 50,000 letters.
The console log begins like below.

Logging output too large. Truncating output. 
<!DOCTYPE html>
<html lang="en">
  <head>
  <meta charset="utf-8">
  <meta content="width=300, initial-scale=1" name="viewport">

推荐答案

此答案如何?

  • 不幸的是，在当前阶段，电子表格的内置功能无法与Google Apps脚本一起使用.
  • 当我在您的伪脚本中看到您的公式时，我认为 MATCH(foo，IMPORTRANGE("sheet of address"，"A:A"，0)可能是MATCH(foo，IMPORTRANGE(工作表地址"，"A:A")，0).
  • 因此，为了将您的公式转换为Google Apps脚本，需要使用 openByUrl 来实现 IMPORTRANGE(工作表地址"，"A:A"，0).如果要将此转换后的脚本用作自定义函数，则在这种情况下，由于权限的原因，自定义函数不能使用 openByUrl .因此，在这种情况下，需要使用脚本编辑器，自定义菜单和电子表格中的按钮来运行脚本.这是Google方面的当前规范.请注意这一点.
    • 从您的问题中，我无法理解您要如何运行脚本以及有关工作表地址.因此，在这个答案中，我想建议使用脚本编辑器来运行脚本.
    • Unfortunately, in the current stage, the built-in functions of Spreadsheet cannot be used with Google Apps Script.
    • When I saw your formula in your pseudo script, I thought that MATCH(foo, IMPORTRANGE("address of sheet", "A:A",0) might be MATCH(foo, IMPORTRANGE("address of sheet", "A:A") ,0).
    • So, in order to convert your formulas to Google Apps Script, it is required to use openByUrl for achieving IMPORTRANGE("address of sheet", "A:A",0). If you want to use this converted script as the custom function, in this case, the custom function cannot use openByUrl due to the permission. So in this case, it is required to run the script using the script editor, the custom menu and the button in Spreadsheet. This is the current specification at Google side. Please be careful this.
      • From your question, I couldn't understand about how do you want to run the script and about address of sheet. So in this answer, I would like to propose to run the script with the script editor.

      当上述要点反映到您的问题的伪脚本中时，它将变为以下内容.该示例的流程如下.

      When above points are reflected to the pseudo script in your question, it becomes as follows. The flow of this sample is as follows.

      1. 检索工作表.
      2. 检索 a 和 b 的值.
        • 在这种情况下，我使用了TextFinder.

      示例脚本:

      请复制以下脚本并将其粘贴到电子表格的脚本编辑器中.并且，请设置 url1 ， url2 和 url3 ， foo 和 bar .运行脚本时，请使用脚本编辑器运行 myFunction 的功能.这样，您就可以在控制台上看到检索到的值，并且该值也将作为示例放入活动单元格中.

      Sample script:

      Please copy and paste the following script to the script editor of the Spreadsheet. And, please set the variables of url1, url2 and url3, foo and bar. When you run the script, please run the function of myFunction with the script editor. By this, you can see the retrieved value at the console and also, the value is also put to the active cell as the sample.

      function abc(foo, bar) {
  const url1 = "https://docs.google.com/spreadsheets/d/###/edit"; // Please set this.
  const url2 = "https://docs.google.com/spreadsheets/d/###/edit"; // Please set this.
  const url3 = "https://docs.google.com/spreadsheets/d/###/edit"; // Please set this.

  // 1. Retrieve sheets.
  const [sheet1, sheet2, sheet3] = [url1, url2, url3].map(e => SpreadsheetApp.openByUrl(e).getSheets()[0]);  // Modified

  // 2. Retrieve values of `a` and `b`.
  const a = sheet1.getRange("A:A").createTextFinder(foo).findNext().getRow();
  const b = sheet2.getRange("B:B").createTextFinder(bar).findNext().getRow();
  
  // 3. Retrieve the result value using `a` and `b`.
  return sheet3.getRange("C:Z").offset(a - 1, b - 1).getValue();
}

// Please run this function.
function myFunction() {
  const foo = "###"; // Please set this.
  const bar = "###"; // Please set this.
  const res = abc(foo, bar);
  console.log(res)
  
  SpreadsheetApp.getActiveRange().setValue(res); // Heare, the value is put to the active cell.
}

      • 不幸的是，根据您的问题，我无法理解每个工作表的地址是否都是相同的URL.因此，在上面的示例脚本中，可以使用3个不同的URL.另外，可以使用相同的3个URL.
      • const a = sheet1.getRange("A:A").createTextFinder(foo).findNext().getRow(); 和 const b = sheet2.getRange("; B:B).createTextFinder(bar).findNext().getRow(); 是 a = MATCH(foo，IMPORTRANGE(" sheet address，" A:A，0)和 b = MATCH(bar，IMPORTRANGE(工作表的地址"，"B:B"，0).
        • IMPORTRANGE(工作表地址"，"A:A")表示"A:A"工作表地址中第一个标签的位置.

          • Unfortunately, from your question, I couldn't understand whether each address of sheet is the same URL. So in above sample script, 3 different URLs can be used. Also, the same 3 URLs can be used.
          • const a = sheet1.getRange("A:A").createTextFinder(foo).findNext().getRow(); and const b = sheet2.getRange("B:B").createTextFinder(bar).findNext().getRow(); are a = MATCH(foo, IMPORTRANGE("address of sheet", "A:A",0) and b = MATCH(bar, IMPORTRANGE("address of sheet", "B:B",0), respectively.
            • IMPORTRANGE("address of sheet", "A:A") means "A:A" of the 1st tab in address of sheet.

              • 当伪脚本中的 sheetaddress 的所有值均为活动电子表格时，可以如下修改 abc .在这种情况下，您可以将此函数用作自定义函数，例如 = abc(foo，bar).

              • When all values of address of sheet in your pseudo script are the active Spreadsheet, abc can be modified as follows. In this case, you can use this function as the custom function like =abc(foo, bar).

                function abc(foo, bar) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    const a = sheet.getRange("A:A").createTextFinder(foo).findNext().getRow();
    const b = sheet.getRange("B:B").createTextFinder(bar).findNext().getRow();
    return sheet.getRange("C:Z").offset(a - 1, b - 1).getValue();
  }

            • 请在启用V8的情况下使用此脚本.

            • Please use this script with enabling V8.

              作为另一种方法，为了在自定义函数中使用 openByUrl ，我想建议使用Web Apps.

              As other method, in order to use openByUrl in the custom function, I would like to propose to use Web Apps.

              请执行以下流程.

              Web Apps的示例脚本是Google Apps脚本.请复制以下脚本(Google Apps脚本)并将其粘贴到脚本编辑器中.该脚本适用于Web Apps.并且请设置URL.关于 baseUrl ，请在部署Web Apps之后进行设置.

              Sample script of Web Apps is a Google Apps Script. Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps. And please set the URLs. About baseUrl, please set it after the Web Apps is deployed.

              function doGet(e) {
  const [sheet1, sheet2, sheet3] = [e.parameter.url1, e.parameter.url2, e.parameter.url3].map(f => SpreadsheetApp.openByUrl(f).getSheets()[0]);
  const a = sheet1.getRange("A:A").createTextFinder(e.parameter.foo).findNext().getRow();
  const b = sheet2.getRange("B:B").createTextFinder(e.parameter.bar).findNext().getRow();
  const res = sheet3.getRange("C:Z").offset(a - 1, b - 1).getValue();
  return ContentService.createTextOutput(res);
}

function abc(foo, bar) {
  const url1 = "https://docs.google.com/spreadsheets/d/###/edit";
  const url2 = "https://docs.google.com/spreadsheets/d/###/edit";
  const url3 = "https://docs.google.com/spreadsheets/d/###/edit";
  const baseUrl = "https://script.google.com/macros/s/###/exec";
  
  const res = UrlFetchApp.fetch(`${baseUrl}?url1=${encodeURIComponent(url1)}&url2=${encodeURIComponent(url2)}&url3=${encodeURIComponent(url3)}&foo=${foo}&bar=${bar}`);
  return res.getContentText();
}

              2.部署Web应用.

              1. 在脚本编辑器上，通过发布"打开对话框.->部署为网络应用".
              2. 选择我" 作为将应用程序执行为:" .
                • 通过这种方式，脚本以所有者身份运行.

              • 在这种情况下，不需要请求访问令牌.我认为我建议使用此设置来测试此变通办法.
              • 当然，您也可以使用访问令牌.使用访问令牌时，请包括Drive API的作用域之一，例如 https://www.googleapis.com/auth/drive.readonly .
              • 而且，我认为可以将键值而不是访问令牌用作查询参数.

                1. 点击部署"按钮作为新的项目版本".
                2. 自动打开需要授权"对话框.
                1. Click "Deploy" button as new "Project version".
                2. Automatically open a dialog box of "Authorization required".
                1. 点击查看权限".
                2. 选择自己的帐户.
                3. 点击高级"在此应用未验证"中.
                4. 点击转到###项目名称###(不安全)"
                5. 点击允许"按钮.

              • 点击确定".
              • 复制Web应用程序的URL.就像 https://script.google.com/macros/s/###/exec .
                • 修改Google Apps脚本后，请重新部署为新版本.这样，修改后的脚本将反映到Web Apps.请注意这一点.

                  • 3.使用自定义功能测试Web应用.

                  请将您的Web Apps的URL设置为上述Google Apps脚本的 baseUrl ，然后将Web Apps重新部署为新版本.这样，最新的脚本就会反映到Web应用程序中.

                  3. Testing Web Apps using custom function.

                  Please set the URL of your Web Apps to baseUrl of the above Google Apps Script and redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps.

                  并且，作为对此脚本的测试，请将 = abc(foo，bar)放入单元格中.这样，将运行功能 abc ，并使用UrlFetchApp检索结果值.

                  And, as the test of this script, please put =abc(foo, bar) to a cell. By this, the function abc is run and the result value is retrieved using UrlFetchApp.

                  • 修改Web应用程序的脚本后，请重新部署Web应用程序为新版本.这样，最新脚本将反映到Web Apps.请注意这一点.

                  这篇关于GAS中的调用表功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆