0

I found a solution on how to reduce the execution time here but I'm unable to implement it through batching as discussed here. [question]:Google app script timeout ~ 5 minutes?

Sorry, I am completely new to scripts. The script below loops through all sheets and changes the sheetname to the value in A1. 

function onEdit() {
var ss = SpreadsheetApp.getActive();
var allsheets = ss.getSheets();

// Array holding the names of the sheets to exclude from the execution
var exclude =["Sheet1","Sheet2","Sheet3","Article","Frontpage","Logos","Sheet4","Sheet5","Sheet6","Sheet10"];

for(var i=10;i<allsheets.length;i++) {
var sheet = allsheets[i];
var oldName = sheet.getName();
var newName = sheet.getRange(1,1).getValue();
if (newName.toString().length>0 && newName !== oldName) {
sheet.setName(newName);

// Stop iteration execution if the condition is met.
if(exclude.indexOf(sheet.getName())==-1) continue;
}

} // end of loop

} // end of function

How can I implement batching on the above script using the example below as a reference to reduce the number of service calls?

for (var i = 1; i <= 100; i++) { SpreadsheetApp.getActiveSheet().deleteRow(i); }

Do this:

SpreadsheetApp.getActiveSheet().deleteRows(i, 100);

1 Answers1

0

Try this:

function onEdit(e) {
  var sh=e.range.getSheet();
  var name=sh.getName();
  var a1=sh.getRange('A1').getValue();
  if(name!=a1) {
    sh.setName(a1);
  }
}

Note: you cannot run this function without the event object. So you cannot run it from the script editor unless you supply the event object.

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks but the script is throwing an error which I want to solve. `Exceeded maximum execution time`. Could you please check the link in question for a possible solution? Looks like it will work but I don't understand batching. Any input is welcome. –  May 28 '19 at 20:40
  • Check the execution log. What is the execution time? – Cooper May 28 '19 at 20:43
  • `[19-05-28 13:37:50:588 PDT] Sheet.getRange([1, 1]) [0 seconds] [19-05-28 13:38:02:684 PDT] Range.getValue() [12.095 seconds] [19-05-28 13:38:02:688 PDT] Execution failed: Exceeded maximum execution time [372.171 seconds total runtime]` The Range.getValue() is showing high values. –  May 28 '19 at 20:45
  • When I edit it should check whether the value in A1 is equal to the Current Sheet Name. I am comfortable running the script manually from the editor though. Yes we can drop onEdit(). –  May 28 '19 at 20:48
  • Replaced onEdit() with myFunction() but still get a timeout. –  May 28 '19 at 21:01
  • Now this onEdit() will check each sheet as it is edited and if it's not the correct name then it will update only the current sheet and you may not get a timeout. – Cooper May 28 '19 at 21:02
  • What do you mean by Replaced on edit with myFunction()? What is the function that is timing out? – Cooper May 28 '19 at 21:03
  • Finally, is there a way I could replace it with onChange because value in A1 changes dynamically without having to edit all 150 sheets to reflect changes? –  May 28 '19 at 21:13
  • If I were you, I'd rethink the need for renaming 150 sheets. It's going to be time consuming and if it's avoidable I'd avoid it. – Cooper May 28 '19 at 22:50