触发函数和导入范围 [英] Triggered Functions and importrange

问题描述

我有一个谷歌应用程序脚本，用于比较以Google表单提交的信息和使用导入区域功能带入Google电子表格的信息。如果我手动运行脚本，该脚本完美工作，但如果它从表单提交触发器运行，它会报告它没有从使用导入范围的单元格获取正确的信息。有没有办法解决这个问题，就像有一个快速的功能，我可以添加强制导入范围来更新？我已经尝试添加长达10分钟的等待时间，但并未改变结果。

I have a google apps script that compares information submitted in a google form to information that is brought into the google spreadsheet using the importrange function. The script works perfectly if I run it manually but if it runs from the form submit trigger it reports that it isn't getting the correct information from the cells using importrange. Is there a way around this, like is there a quick function I can add that forces import range to update? I have already tried to add a wait time of up to 10 minutes and it hasn't changed the results.

谢谢

Thanks

推荐答案

问题在于如何使用像 importRange 这样的函数。实际上，它们需要与使用它们登录的用户帐户相同的访问权限，以避免安全漏洞（想象一下您拥有的电子表格编辑器，您可以访问其他尚未与其共享的电子表格）。

The problem is with how functions like importRange works. Actually, they require the same access/permissions as the user account logged in using them, to avoid security holes (imagine an editor of a spreadsheet you own accessing other spreadsheets you have not shared with them).

正因为如此（至少这是我的想法），所以只有在打开电子表格后才会对这些公式进行评估。关闭它并让脚本在触发器上运行时，它不会在预期的单元上找到任何结果。

Because of this (well, at least that's what I think), these formulas are only evaluated when you have the spreadsheet opened. When you close it and leave the script to run on a trigger, it will not find any result on the expected cells.

一个简单的解决方法是获取数据使用脚本本身并使用 importRange 退出。这很容易做到，例如

An easy workaround for this is to fetch the data using the script itself and quit using importRange. It's very easy to do, e.g.

var values = SpreadsheetApp.openById('external-spreadsheet-key').
  getSheetByName('Sheet1').getRange('A1:D100').getValues();
SpreadsheetApp.getActive().getSheetByName('DestSheet').
  getRange(1,1,values.length,values[0].length).setValues(values);

显然，电子表格的关键字，表格的名称和范围仅仅是一个例子。

Obviously that the spreadsheet key, sheet's names and ranges are just an example here.

这篇关于触发函数和导入范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！