I have this code that extracts calendars from a list of Google calendars. I have a problem with the maximum execution time.
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);
let formulas = []
for(let i = 0; i < 7; i ){formulas.push('=(HOUR(RIGHT(b' lastRow ';5)) (MINUTE(RIGHT(b' lastRow ';5))/60))-(HOUR(LEFT(b' lastRow ';5)) (MINUTE(LEFT(b' lastRow ';5))/60))')}}
sheet.getRange(lastRow,6).setFormulas(formulas)
//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)')
}
}
}
One of the problems is that I'm 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 I 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.
I have recived this advice:
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))')
Should move to something like that:
let formulas = [] for(let i = 0; i < 7; i ){formulas.push(FORMULAS)} sheet.getRange(x,y,nx,ny).setFormulas(formulas)
I have try to change my code using this but without success. Can someone please give me an example using my code on how I can do this change ? Thanks a lot !!
CodePudding user response：
You create arrays and push the data in there. Then like you said, you can bulk update it all. Would be something like below. I did not test it and maybe the range is off. But this will get you in the right direction.
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
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);
// 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)
}
}
}
sheet.getRange(7,1,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');
}
CodePudding user response：
Thank you again to RemcoE33 for his code. I fixe two mistakes, lastrow was missing and the getRange had an error. Now it is working very well. Thanks a lot !!
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');
}```