这个答案怎么样？
修改要点：
- 不幸的是，现阶段，Spreadsheet 的内置函数无法与 Google Apps Script 一起使用。
- 当我在伪脚本中看到你的公式时，我认为
MATCH(foo, IMPORTRANGE("address of sheet", "A:A",0)可能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.
- 从你的问题来看，我无法理解你想如何运行脚本以及
address of sheet。因此，在这个答案中，我想建议使用脚本编辑器运行脚本。
当上述几点反映到您问题中的伪脚本时，它会变成如下所示。该样本的流程如下。
Flow:
- 取回床单。
- Retrieve values of
a and b.
- 使用检索结果值
a and b.
示例脚本：
请将以下脚本复制并粘贴到电子表格的脚本编辑器中。并且，请设置变量url1, url2 and url3, foo and bar。当您运行脚本时，请运行以下函数myFunction使用脚本编辑器。这样，您就可以在控制台看到检索到的值，并且该值也将作为示例放入活动单元格中。
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.
}
- 不幸的是，从你的问题来看，我无法理解是否每个
address of sheet是相同的 URL。因此，在上面的示例脚本中，可以使用 3 个不同的 URL。另外，可以使用相同的 3 个 URL。
-
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")表示第一个选项卡的“A:A”address of sheet.
-
return sheet3.getRange("C:Z").offset(a - 1, b - 1).getValue(); is return INDEX(IMPORTRANGE("address of sheet", "C:Z"),a,b).
Note:
-
当所有的值address of sheet在你的伪脚本中是活动的电子表格，abc可以修改如下。在这种情况下，您可以将此函数用作自定义函数，例如=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 的情况下使用此脚本。
参考：
- Google 表格中的自定义函数 https://developers.google.com/apps-script/guides/sheets/functions
- 文本查找器 https://developers.google.com/apps-script/reference/spreadsheet/text-finder
Added:
与其他方法一样，为了使用openByUrl在自定义功能中，我建议使用Web Apps。
Usage:
请执行以下流程。
1. 准备脚本。
Web Apps 的示例脚本是 Google Apps 脚本。请将以下脚本（Google Apps 脚本）复制并粘贴到脚本编辑器中。该脚本适用于 Web 应用程序。并请设置 URL。关于baseUrl，请在Web Apps部署后进行设置。
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 应用程序。
- 在脚本编辑器上，通过“发布”->“部署为 Web 应用程序”打开一个对话框。
- Select "Me" for "Execute the app as:".
- Select “任何人，甚至匿名” for “谁有权访问该应用程序：”.
- 在这种情况下，不需要请求访问令牌。我认为我建议使用此设置来测试此解决方法。
- 当然，您也可以使用访问令牌。当您使用访问令牌时，请包含 Drive API 的范围之一，例如
https://www.googleapis.com/auth/drive.readonly.
- 而且，我认为键值可以用作查询参数而不是访问令牌。
- 单击“部署”按钮作为新的“项目版本”。
- Automatically open a dialog box of "Authorization required".
- 单击“查看权限”。
- 选择自己的帐户。
- 单击“此应用程序未经验证”处的“高级”。
- 点击“转到###项目名称###（不安全）”
- 单击“允许”按钮。
- 单击“确定”。
- Copy the URL of Web Apps. It's like
https://script.google.com/macros/s/###/exec.
- 当您修改 Google Apps 脚本时，请重新部署为新版本。这样，修改后的脚本就会反映到Web Apps中。请小心这一点。
3. 使用自定义函数测试Web Apps。
请将您的 Web 应用程序的 URL 设置为baseUrl上述 Google Apps 脚本并将 Web Apps 重新部署为新版本。这样，最新的脚本就会反映到 Web 应用程序中。
并且，作为该脚本的测试，请输入=abc(foo, bar)到一个细胞。至此，函数abc运行并使用 UrlFetchApp 检索结果值。
Note:
- 当您修改Web Apps的脚本时，请将Web Apps重新部署为新版本。这样，最新的脚本就会反映到Web Apps中。请小心这一点。
参考：
- Web Apps https://developers.google.com/apps-script/guides/web
- 通过 Google Apps 脚本利用 Web Apps https://github.com/tanaikech/taking-advantage-of-Web-Apps-with-google-apps-script
