Script time limit for Google sheet

1
votes

I need to change some formulas at the same cells at an specific sheet (LISTAFINAL) present in a great number of spreadsheets, these one located at the same folder. But it stops at Google script time limit of 6 minutes, making changes only in 9 spreadsheets, and comes the message: Erro - Exceeded maximum execution time.

  1. My goals:

I would like to know if there's any way to or speed up this process or make changes in a bigger number of spreadsheets or both. Here is the code:

function validacao(){
  var folder = DriveApp.getFolderById("FOLDER ID");
  var x = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (x.hasNext()) {
    SpreadsheetApp.open(x.next()).getSheets().forEach(sheet => {
      sheet.getRange('LISTAFINAL!F5:F15').activate();
      sheet.getRange('LISTAFINAL!F5').setValue('=ALUNO1!$F$167');
      sheet.getRange('LISTAFINAL!F6').setValue('=ALUNO2!$F$167');
      sheet.getRange('LISTAFINAL!F7').setValue('=ALUNO3!$F$167');
      sheet.getRange('LISTAFINAL!F8').setValue('=ALUNO4!$F$167');
      sheet.getRange('LISTAFINAL!F9').setValue('=ALUNO5!$F$167');
      sheet.getRange('LISTAFINAL!F10').setValue('=ALUNO6!$F$167');
      sheet.getRange('LISTAFINAL!F11').setValue('=ALUNO7!$F$167');
      sheet.getRange('LISTAFINAL!F12').setValue('=ALUNO8!$F$167');
      sheet.getRange('LISTAFINAL!F13').setValue('=ALUNO9!$F$167');
      sheet.getRange('LISTAFINAL!F14').setValue('=ALUNO10!$F$167');
      sheet.getRange('LISTAFINAL!F15').setValue('=ALUNO11!$F$167');
    });
  }
}
1
google-apps-scriptgoogle-sheetsgoogle-drive-api

1 Answers

3
votes

Explanation:

  • You iterate over all sheets for every spreadsheet file. Your goal is to just get a single sheet and put the formulas in specific cells. Therefore, you can get rid of the forEach loop.

  • It is a best practice to work with arrays instead of iteratively using multiple google apps script functions.

  • For example, in your code you are using getRange and setValue 11 times. If you store the formula values in an array you will be able to store them by using only a single getRange and setValues.

Solution:

function validacao(){
  const folder = DriveApp.getFolderById("FOLDER ID");
  const x = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  const formulas = Array(11).fill().map((_, i) => [`=ALUNO${i+1}!$F$167`]);
  while (x.hasNext()) {
      let ss_target = SpreadsheetApp.open(x.next());
      let sh = ss_target.getSheetByName("LISTAFINAL");
      sh.getRange('F5:F15').setValues(formulas);
  }
}