Appscript function for getting data from different sheets is slow, how can I optimise it?

Question

I'm new to JS and programming. I'm running a Project Spreadsheet with lots of sheets and trying to collect the information in one master sheet. The problem is, looping through the 100 sheets and getting the values is making the function too slow and needs about 40 seconds runtime.

I looked into the log and saw that .getValue was very demanding and took 0.2 seconds for every iteration. I have read about batching the function but know how to do that...

function onEdit(e) {

//declaring variables
var sheetnames = new Array();
var budgetSums = new Array();
var projectState = new Array();
var workingHours = new Array();
var projectLink = new Array();
var sheetLink = new Array();

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var masterSheet = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Mastersheet");

//get information and cell values from diffentent sheets
for (var i=0 ; i<sheets.length ; i++) {
sheetnames.push( [ sheets[i].getName() ] ); 
budgetSums.push( [ sheets[i].getRange('F1').getValue() ] );
projectState.push( [ sheets[i].getRange('K1').getValue() ] );
workingHours.push( [ sheets[i].getRange('K2').getValue() ] );
sheetLink.push( [ sheets[i].getSheetId() ] )
}


//set cell values in the master sheet
for (var i=0; i<sheetnames.length; i++){
masterSheet.getRange(i +1, 1).setValue(sheetnames[i]); 
masterSheet.getRange(i +1, 2).setValue(budgetSums[i]); 
masterSheet.getRange(i +1, 3).setValue(projectState[i]); 
masterSheet.getRange(i +1, 4).setValue(workingHours[i]); 

}
for (var i=0; i<sheetnames.length; i++){
masterSheet.getRange(i +1, 5).setValue('=HYPERLINK("#gid=' + sheetLink[i] 
+ '";"' + sheetnames[i] + '")' ); 
}
}

The code is working but I don't know how to optimize it. Maybe I need a different approach to this?

Answer 1

I believe you could take these two sections:

for (var i=0 ; i<sheets.length ; i++) {
  sheetnames.push( [ sheets[i].getName() ] ); 
  budgetSums.push( [ sheets[i].getRange('F1').getValue() ] );
  projectState.push( [ sheets[i].getRange('K1').getValue() ] );
  workingHours.push( [ sheets[i].getRange('K2').getValue() ] );
  sheetLink.push( [ sheets[i].getSheetId() ] )
}


//set cell values in the master sheet
for (var i=0; i<sheetnames.length; i++){
  masterSheet.getRange(i +1, 1).setValue(sheetnames[i]); 
  masterSheet.getRange(i +1, 2).setValue(budgetSums[i]); 
  masterSheet.getRange(i +1, 3).setValue(projectState[i]); 
  masterSheet.getRange(i +1, 4).setValue(workingHours[i]); 

}

And rewrite them like this:

for (var i=0 ; i<sheets.length ; i++) { 
  masterSheet.getRange(i+1,1,1,4).setValues([[sheets[i].getName(),sheets[i].getRange('F1').getValue(),sheets[i].getRange('K1').getValue(),getRange('K2').getValue()]]);
  sheetLink.push( [sheets[i].getSheetId()])
}

I haven't tested this code so it could probably use some tweaking to get it to work.

If you can restructure the User Interface so that you could get them all in one getValues() that would be great but you'll have to think about how that might affect your users. Of course one possibility is to use dialogs for data entry and then it doesn't matter how you store the data. But using spreadsheets for data entry is a much simpler approach than building custom dialogs for all data entry.

After looking at it a bit more I think you could probably go all the way with this:

for (var i=0 ; i<sheets.length ; i++) { 
  masterSheet.getRange(i+1,1,1,5).setValues([[sheets[i].getName(),sheets[i].getRange('F1').getValue(),sheets[i].getRange('K1').getValue(),getRange('K2').getValue(), '=HYPERLINK("#gid=' + sheets[i].getSheetId() + '";"' + sheetnames[i] + '")']]);
}

If you can set us up with a sample spreadsheet I'd be glad to try to get it working. Don't hook us up with something that has private information or you can't afford to lose.

I don't think I would want to run this in an onEdit(e) trigger. Remember, simple triggers need to finish is 30 seconds.