自定义公式以查看其他GSheet [英] Custom Formula to look at a different GSheet

问题描述

我仍在尝试使用脚本来解决问题，因此，如果这是很基本的话，我深表歉意.

I am still trying to wrap my head around using script, so my apologies if this is basic.

我有一个摘要电子表格，可以从数百个其他文档中提取值.这个使用了太多的importrange公式，但是其中许多现在是#REF或...正在加载.

I have a summary Spreadsheet that pulls values from hundreds of other documents. This used (too many) importrange formulas, but many of these are now #REF or ...Loading.

我正在尝试创建一个自定义公式，该公式将模仿importrange函数并返回值.我认为最困难的部分是在更新另一个文档时更新公式，但是我一开始就陷入困境.

I am trying to create a custom formula that will mimic the importrange function and return the values. I thought the hard part would be getting the formulas to update when another document was updated, but I have fallen right at the start.

这是我的代码.

function GetRngValue(ShtURL, GetRng) {
// find id from shtURL? - check if the workbook was updated in the last minute
Logger.log(ShtURL);
var Source = SpreadsheetApp.openByUrl(ShtURL);
// IF Source lastupdated > ThisSht lastupdated then  [ Ensure update runs at startup though ]
if (DriveApp.getFileById(Source.getId()).getLastUpdated() > DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getLastUpdated()) {
  
  var shtNm = GetRng.substring(1,find("!")-1);
  Logger.log(shtNm);
  var myRng = GetRng.substring(find("!")+1,len(GetRng));
  Logger.log(myRng);
  TargetRng =  Source.getSheetByName(shtNm)
  var TargetRng = Source.getSheetByName(shtNm).getRange("'"+myRng+"'").getValues;
  Logger.log(TargetRng);
  return TargetRng;
 };
 Logger.log("First - "+DriveApp.getFileById(Source.getId()).getLastUpdated()+" meets last - "+DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getLastUpdated());
Logger.log("Ending");

}

我遇到了错误:例外:您无权调用SpreadsheetApp.openByUrl.所需权限: https://www.googleapis.com/auth/spreadsheets (第4行)

I am getting the error: Exception: You do not have permission to call SpreadsheetApp.openByUrl. Required permissions: https://www.googleapis.com/auth/spreadsheets (line 4).

该单元格中的公式为:= GetRngValue(""https://docs.google.com/spreadsheets/d/1axNelBTg4j..etc..7xJx2lSk/edit"，"(摘要！B32").谁能指出我正确的方向?我可以从自定义公式中引用另一个GSheet吗?

The formula in the cell is: =GetRngValue("""https://docs.google.com/spreadsheets/d/1axNelBTg4j..etc..7xJx2lSk/edit""", """Summary!B32""") Can anyone point me in the right direction? Can I not reference another GSheet from a custom formula?

推荐答案

如 TheMaster 所述，您会更好在没有自定义功能的情况下关闭.与通过脚本更新电子表格相比，它们施加了更多的限制，并且速度较慢–将 IMPORTRANGE()函数替换为 GetRngValue()函数不会对您有多大帮助甚至有可能.如果您要提取数百个其他文档中的值"，那么无论如何都应该进行大修.

As mentioned by TheMaster, you're better off without custom functions here. They place a lot more restrictions and are slower than if your spreadsheet were updated via script–replacing your IMPORTRANGE() functions with GetRngValue() wouldn't help you much if it were even possible. If you're pulling "values from hundreds of other documents", then it's likely you're well due for an overhaul anyway.

在有关自定义函数的文档中自定义功能从不要求用户授权访问个人数据".这包括电子表格数据，以后会更明确:

In the documentation for custom functions, it's stated that "custom functions never ask users to authorize access to personal data". This includes spreadsheet data and is later made more explicit:

无法打开其他电子表格( SpreadsheetApp.openById()或 SpreadsheetApp.openByUrl()).

这篇关于自定义公式以查看其他GSheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！