Google apps脚本超过最大执行时间

Question

我试图调整我的代码以避免时间限制，但它看起来不工作。有人能帮我一下吗?我做错了什么?它正在运行，但没有避免时间限制的问题。主要功能是从google日历中提取许多日历并进行一些简单的计算。我收到一个时间限制，目前，我的解决方案是创建8个代码，并每小时触发它们，但我觉得这不是超级干净，我们可以做得更好。非常感谢，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)')
}
}
}
}

Answer 1

你的脚本的主要问题是你在循环中使用setFormula或getRange这样的方法，这会导致对电子表格服务的大量调用。

最佳实践指南不建议这样做。

所以，为了解决这个问题，你必须使用最佳实践指南中的另一个建议，使用批处理操作。

代替使用setFormula使用setFormulas和委托给Javascript所有的任务来构建公式。

所有以:

开头的行

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

移动到这样的位置:

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

这将是一个起点。

• 最佳实践Google Apps Script

Answer 2

感谢这个了不起的社区的帮助。我有了一个运行速度快得多的代码。谢谢你@Emel的建议。现在它工作得快多了!!

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