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)')
}
}
}
}