In Google sheets Script, I'm trying to copy value from one cell in Sheet A to another cell in sheet B.
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.
But when I call the function from the target sheet with formula =avgUtilisationPerWeek()
, it reports #ERROR
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.
This is the source code:
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 Script setValue permission
Transferring cell data in Google Script?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…