Google apps script Exceeded maximum execution time-CodePudding

I try to adapt my code to avoid the time limitation but it looks like it is not working. Can someone help me with that? what am I doing wrong? It is running but not avoiding the time limitation problem. The main function is a function that extracts many calendars from google calendar and does some easy calculation. I receive a time limit restriction, for now, my solution was to create 8 codes and trigger them every hour, but I feel it is not super clean and we could do better. Thanks a lot, j.

function update_main_master() {
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Id Calendriers - Dates Debut et Fin"); //Sheet name where you will have the loop
  sheet2.getRange('d1').setValue(0);
  // Set filters
 
  var begin = new Date(); // Time when execution begins
  // Your code before loop
  export_gcal_to_gsheetLast1()
   // Your code before loop
  
  
  
  var x = sheet2.getRange('D1').getValue(); // Retrieve x stored in previous execution (from PropertiesService? Spreadsheet?) (should be 0 if first execution)
  var timeLimit = 1000 * 60 * 1; // 4 minutes (in milliseconds)
  while (new Date() - begin < timeLimit) { // Check if 5 minutes passed since execution start
    for (var x = 0; x < xngData.length && xngData[x][3] != undefined; x  ) {
      
      // Your code inside loop
  export_gcal_to_gsheetLast1()
    // Your code inside loop
    }
  }
  // Store current x index (using PropertiesService? Or write to the spreadsheet itself?
  var ContorlSS = SpreadsheetApp.openById('IDssdfsadfsafd');
  var ContorlSh = ContorlSS.getSheetByName('Id Calendriers - Dates Debut et Fin');
  ContorlSh.getRange('e1').setValue('Updated');
  ContorlSh.getRange('f1').setValue(new Date());
  ContorlSh.getRange('d1').setValue(x);
  if (x < xngData.length) { // Create trigger if x hasn't reach the total number of iteration
    ScriptApp.newTrigger("update_main_master")
    .timeBased()
    .after(1000 * 20) // This fires the function 1 minute after the current execution ends. Change this time according to your preferences
    .create();  
  }
}
///////////////////////////////////////////export calendar////////////////
function export_gcal_to_gsheetLast1(){
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Extraction 1 - Calendrier");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Id Calendriers - Dates Debut et Fin"); //Sheet name where you will have the list of calendar ID's, startDate and endDate

  sheet.clear() // If you'll be runnning the script several times, the data will be duplicated so I added this line to clear the sheet before adding the data
  // other option would be to create a script to check if data already exists before adding it to the sheet

  // Set filters
  var startDate = sheet2.getRange('k1').getValue(); //Range for startDate
  var endDate = sheet2.getRange('k2').getValue(); //Range for endDate
  var users = sheet2.getRange('b3:B').getValues(); //Range where you have the calendar ID's

  // Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
  // of the getRange entry below
  var header = [["Titre", "Description", "Location", "Début", "Fin", "Heures effectives","Extraction 2","Extraction 3","Heures Planifiées", "Vacances", "Maladie","Congé légal", "Absence"]]
  var range = sheet.getRange(7,1,1,13);
  range.setValues(header);
  
  for (var j = 0; j< users.length; j  ){
    //here we do the things we do once per calander
    if (users[j] == ""){
      break;
    }
    
    else{
      var cal = CalendarApp.getCalendarById(users[j]);
      var events = cal.getEvents(startDate, endDate);

      // Loop through all calendar events found and write them out starting on the next empty row
      for (var i=0;i<events.length;i  ) {
        var myformula_placeholder = '';
        var details=[
          [events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), 
          myformula_placeholder,myformula_placeholder,myformula_placeholder,myformula_placeholder,myformula_placeholder,myformula_placeholder,myformula_placeholder,myformula_placeholder]
        ];

        var lastRow = sheet.getLastRow() 1;
        var range=sheet.getRange(lastRow,1,1,13);
        range.setValues(details);
          
        var cell=sheet.getRange(lastRow,6);
        cell.setFormula('=(HOUR(RIGHT(b'  lastRow ';5)) (MINUTE(RIGHT(b'  lastRow  ';5))/60))-(HOUR(LEFT(b'  lastRow  ';5)) (MINUTE(LEFT(b'  lastRow  ';5))/60))')
        cell.setNumberFormat('.00');
        
        var cell=sheet.getRange(lastRow,7);
        cell.setFormula('=IFERROR(TEXT(INDEX(SPLIT(A' lastRow ';" ");2);"hh:mm");"")')
        
        
        var cell=sheet.getRange(lastRow,8);
        cell.setFormula('=IFERROR(TEXT(INDEX(SPLIT(A' lastRow ';" ");3);"hh:mm");"")')

        var cell=sheet.getRange(lastRow,9);
        cell.setFormula('=IF(OR(G' lastRow '="Maladie";G' lastRow '="Congé";G' lastRow '="Absence";G' lastRow '="00:00";G' lastRow '="Vacances");0;(HOUR(H' lastRow ') (MINUTE(H' lastRow ')/60))-(HOUR(G' lastRow ') (MINUTE(G' lastRow ')/60)))')

        var cell=sheet.getRange(lastRow,10);
        cell.setFormula('=IF(IFNA(VLOOKUP(D' lastRow '; feries;1;FALSE);1)<>1;0;IF(AND(G' lastRow '="00:00";H' lastRow '="Vacances");0,5;IF(G' lastRow '="Vacances";1;0)))')

        var cell=sheet.getRange(lastRow,11);
        cell.setFormula('=IF(G' lastRow '="Maladie";1;0)')

        var cell=sheet.getRange(lastRow,12);
        cell.setFormula('=IF(G' lastRow '="Congé";1;0)')
        
        var cell=sheet.getRange(lastRow,13);
        cell.setFormula('=IF(G' lastRow '="Absence";1;0)')

      }
    }
  }
}

CodePudding user response：

The main problem with your script is that you are using methods like setFormula or getRange inside of a loop, this causes a massive call to Spreadsheet service.

This is not recommended by the best practices guides Minimize Call to other service.

So, in order to solve this problem you have to use another recommendation from Best Practices guide, using batch operation.

Instead of using setFormula use setFormulas and delegate to Javascript all the tasks to build the formulas.

So all the lines that start with:

var cell=sheet.getRange(lastRow,6);
cell.setFormula('=(HOUR(RIGHT(b'  lastRow ';5)) (MINUTE(RIGHT(b'  lastRow  ';5))/60))-(HOUR(LEFT(b'  lastRow  ';5)) (MINUTE(LEFT(b'  lastRow  ';5))/60))')

Move to something like this:

let formulas = []
for(let i = 0; i < 7; i  ){formulas.push(FORMULAS)}
sheet.getRange(x,y,nx,ny).setFormulas(formulas)

This will be a starting point.

Documentation

Best Practices Google Apps Script

CodePudding user response：

Thanks to the help of this amazing community. I was able to have a code that works so much faster. Thank you @Emel for your advices. Now it is working so much faster!!

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Extraction 8 - Calendrier");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Id Calendriers - Dates Debut et Fin"); //Sheet name where you will have the list of calendar ID's, startDate and endDate

  sheet.clear() // If you'll be runnning the script several times, the data will be duplicated so I added this line to clear the sheet before adding the data
  // other option would be to create a script to check if data already exists before adding it to the sheet

  // Set filters
  var startDate = sheet2.getRange('k15').getValue(); //Range for startDate
  var endDate = sheet2.getRange('k16').getValue(); //Range for endDate
  var users = sheet2.getRange('b3:B').getValues(); //Range where you have the calendar ID's

  // Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
  // of the getRange entry below
  const data = []
  const formulas = [];
  const headers = [["Titre", "Description", "Location", "Début", "Fin", "Heures effectives","Extraction 2","Extraction 3","Heures Planifiées", "Vacances", "Maladie","Congé légal", "Absence"]]

  
  for (var j = 0; j< users.length; j  ){
    //here we do the things we do once per calander
    if (users[j] == ""){
      break;
    }
    
    else{
      var cal = CalendarApp.getCalendarById(users[j]);
      var events = cal.getEvents(startDate, endDate);
      var lastRow = sheet.getLastRow() 8;

      // Loop through all calendar events found and write them out starting on the next empty row
      for (var i = 0; i < events.length; i  ) {
        var details=
        [
          events[i].getTitle(), 
          events[i].getDescription(), 
          events[i].getLocation(), 
          events[i].getStartTime(), 
          events[i].getEndTime()
        ];

        data.push(details);
        const rowFormulas = 
        [
          '=(HOUR(RIGHT(b'  lastRow ';5)) (MINUTE(RIGHT(b'  lastRow  ';5))/60))-(HOUR(LEFT(b'  lastRow  ';5)) (MINUTE(LEFT(b'  lastRow  ';5))/60))',
          '=IFERROR(TEXT(INDEX(SPLIT(A' lastRow ';" ");2);"hh:mm");"")',
          '=IFERROR(TEXT(INDEX(SPLIT(A' lastRow ';" ");3);"hh:mm");"")',
          '=IF(OR(G' lastRow '="Maladie";G' lastRow '="Congé";G' lastRow '="Absence";G' lastRow '="00:00";G' lastRow '="Vacances");0;(HOUR(H' lastRow ') (MINUTE(H' lastRow ')/60))-(HOUR(G' lastRow ') (MINUTE(G' lastRow ')/60)))',
          '=IF(IFNA(VLOOKUP(D' lastRow '; feries;1;FALSE);1)<>1;0;IF(AND(G' lastRow '="00:00";H' lastRow '="Vacances");0.5;IF(G' lastRow '="Vacances";1;0)))',
          '=IF(G' lastRow '="Maladie";1;0)',
          '=IF(G' lastRow '="Congé";1;0)',
          '=IF(G' lastRow '="Absence";1;0)'
        ]
        
        formulas.push(rowFormulas)
        lastRow=lastRow 1
      }
    }
  }

  sheet.getRange(7,1,headers.length, headers[0].length).setValues(headers)
  sheet.getRange(8,1,data.length,data[0].length).setValues(data);
  sheet.getRange(8,data[0].length   1,formulas.length, formulas[0].length).setFormulas(formulas);
  sheet.getRange(8,6,sheet.getLastRow()).setNumberFormat('.00');

}```