GoogleSheet脚本单元格值设置 [英] GoogleSheet Script cell value setting
问题描述
在Google表格脚本中，我试图将值从表格A中的一个单元格复制到表格B中的另一个单元格.
In Google sheets Script, I'm trying to copy value from one cell in Sheet A to another cell in sheet B.
问题是，当我在oogle Sheets脚本编辑器中启动avgUtilisationPerWeek()函数时，代码正在执行此工作，它将按定义的方式设置单元格的值.
The problems is, when I start avgUtilisationPerWeek() function in oogle Sheets script editor, the code is doing the job, it sets the values of cells as it is defined.
但是当我使用公式=avgUtilisationPerWeek()从目标工作表调用该函数时，它将报告#ERROR
But when I call the function from the target sheet with formula =avgUtilisationPerWeek(), it reports #ERROR
您无权调用setValue(第108行).
You do not have permission to call setValue (line 108).
在自定义函数中，我试图设置工作表中其他单元格的值，而不仅仅是设置公式所在的单元格.
In the custom function, I'm trying to set the value of other cells in the sheet, not just the cell where the formula is placed.
这是源代码:
function avgUtilisationPerWeek(){
for(var i = 1; i < 14; i++) {
Utilities.sleep(3000);
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('D1').setValue(i);
t1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('L4').getValue();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Graph Q1 2017').getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('24')).setValue(t1);
v = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('L5').getValue();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Graph Q1 2017').getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('29')).setValue(v);
g = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('L6').getValue();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Graph Q1 2017').getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('34')).setValue(g);
t2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('L7').getValue();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Graph Q1 2017').getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('39')).setValue(t2);
}
}
我读了这篇文章，但没有找到解决方法.
I read this articles, but didnt find the solution.
推荐答案
Google关于在工作表中使用公式的准则特别指出:
只读(可以使用大多数get *()方法，但不能使用set *()). 无法打开其他电子表格(SpreadsheetApp.openById()或SpreadsheetApp.openByUrl()).
Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).
和:
如果您的自定义函数抛出错误消息您没有调用X服务的权限."，则该服务需要用户授权，因此无法在自定义函数中使用.
If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.
要使用除上面列出的服务以外的其他服务，请创建一个运行Apps脚本功能的自定义菜单，而不是编写一个自定义功能.通过菜单触发的功能将在必要时询问用户授权，因此可以使用所有Apps Script服务.
To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.
因此，按照David的建议，您应该使用自定义菜单，而不是公式.
Therefore as suggested by David, you should use a custom menu, and not a formula.
这篇关于GoogleSheet脚本单元格值设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
