I've created my first Google Workspace Addon which I have deployed to the associated domain successfully although still working on it due to this error.
Without errors, functionally it's (was) working well, however, I now keep getting "Exceeded maximum execution time" errors.
This error is frustratingly intermittent, but it recently started occuring around 90% of the time despite not occurring previously for weeks. Reverting to previous versions of my code hasn't fixed the issue.
Most if not all existing answers regarding this error assume the script is genuinely exceeding the time limit for scripts (either 6 minutes or 30 minutes).
To be clear - this is not what's happening here. The script doesn't run for anywhere close to the maximum time allowed (as far as I'm aware). It actually runs for around 45 seconds before giving the error.
It may somehow be running in the background and causing a legitimate Timeout error but I need to figure out if this is the case and the cause of the error and then fix it.
I've contacted Google but their relevant support who are knowledgeable about this and who could help are unfortunately very difficult to get hold of.
In Cloud Logging for this error, I'm seeing (obfuscated id's):
{
"insertId": "28z8v2anf1xq6",
"jsonPayload": {
"context": {
"reportLocation": {
"filePath": "[unknown file]",
"functionName": "[unknown function]"
}
},
"message": "Exceeded maximum execution time",
"serviceContext": {
"service": "AKfycbx-SO5mXBNoe2leEH4wrj02t9fZZwkq5BQlJPuBaNM"
}
},
"resource": {
"type": "app_script_function",
"labels": {
"function_name": "fileMakerPro",
"invocation_type": "unknown",
"project_id": "order-management-713317"
}
},
"timestamp": "2022-03-10T10:09:58.827Z",
"severity": "ERROR",
"labels": {
"script.googleapis.com/process_id": "fIxbX5X5IjrIqLLK-XQgJiJV0U9vQMEAEA1GOxTqw-XzqsaVlKTNK2UcymM7feuXp-qZLshvBUg61TS6u28l_v6szoAq8QBBOvGudISVj0yceQXPKpIHO6HJ2G-uxuqy4xcnv-NzDfBTMbJH7VmK_AjZd6a5KVA-LnhtOE_28mCn_zTpI5AC3-BhX_lcCC1p-3QsMX6blhMZSYgVTTo1T_Z9SovufUWinpJieIbio-L8wzQPkjLYM2l9s5RHGuKMcT3LbvUO7fFS4DV1z_xfaR_nU1LqeayAk1aouGSXc",
"script.googleapis.com/user_key": "wPo7ZL4DSIqpPrZfeqo7E7yAArA430YCNnzbVYhyuVlR6nKybMkISeaDZoigRDx0gAJXIjF49EoL",
"script.googleapis.com/deployment_id": "wkq5BQlJPcbx-SO5mEHAKfywrj02t9fZZ4uBXBNoe2leaNM",
"script.googleapis.com/project_key": "Xf9t90MIwJJbMnu2Z9hQ48TM6DPTcW9w3"
},
"logName": "projects/order-management-317713/logs/script.googleapis.com/console_logs",
"receiveTimestamp": "2022-03-10T10:09:59.313440894Z"
}
Which is obscure and isn't helpful at all with the "unknowns".
The function it references, for reference:
async function fileMakerPro(e) {
console.time("fileMakerPro");
//Order Management Named Ranges
let OM_NR = await getNamedRangesAsObject_(ss);
let orderNumber, orderFolder, orderFolderName;
let loadingDate, loadingDateFormatted, dueDate, dueDateFormatted;
let sheetGoodsTable = OM_NR.goodsTable.ranges.filter(function (x) { /* here, x is an array, not an object */
return !(x.every(element => element === (undefined || null || '')))
});
//Get order number if exists or show warning and return
if (orderNumber = OM_NR.OrderNo.range.toString()) {
} else {
ui.alert(`⚠️ ORDER NUMBER \(${OM_NR.OrderNo.rangeA1.toString()}\) appears missing, please correct and try again.`);
return;
}
//Get loading date if exists or show warning and return
if (loadingDate = OM_NR.transportLoadingDate.range) {
loadingDateFormatted = `${loadingDate.getDate()}-${loadingDate.getMonth() 1}-${loadingDate.getFullYear()}`;
monthName = monthNames[loadingDate.getMonth()];
//Set Due Date 30 days after Loading Date
dueDate = new Date(loadingDate.getFullYear(), loadingDate.getMonth(), loadingDate.getDate() 30);
dueDateFormatted = `${dueDate.getDate()}-${dueDate.getMonth() 1}-${dueDate.getFullYear()}`;
} else {
ui.alert(`⚠️ LOADING DATE \(${OM_NR.transportLoadingDate.rangeA1.toString()}\) appears to be missing, please correct and try again.`);
return;
}
let rootFolder = DriveApp.getFileById(sheetId).getParents().next();
let tempFolder = DriveApp.getFolderById("1f4Ll-KODzmvBIuunaCS_7anMCX-w86S6");
let monthFolders = rootFolder.getFolders();
let tickBoxes = e.formInputs.generateDocsCheckboxes;
let packingListTemplateId = '1cZUB4U59Z56456gdgdghhka2QQ9sUNIlQSHYy1M';
let tickCMR = false, tickInvoice = false;
//Find existing order folder
let orderSearch = await searchFolders(monthFolders, monthName, orderNumber);
//Set Order Folder Name if both values present
orderFolderName = `${loadingDate.getDate()}-${loadingDate.getMonth() 1}-${loadingDate.getFullYear()} \(${orderNumber}\)`;
if (tickBoxes !== undefined) {
if (tickBoxes.indexOf('tickBoxCMRnote') > -1) {
tickCMR = true;
}
if (tickBoxes.indexOf('tickBoxInvoice') > -1) {
tickInvoice = true;
}
} else {
let confirm = ui.alert(`⚠️ No documents selected. Update Order ${orderNumber} Snapshot and Packing List only?`, Browser.Buttons.YES_NO);
if (confirm === ui.Button.YES) {
fileMakerPro();
} else {
ss.toast(`⚠️ Update cancelled.`);
return;
}
}
if (!orderSearch.foundMonthFolder) {
ss.toast(`⚙️ Creating Month Folder...`);
monthFolder = rootFolder.createFolder(monthName);
} else {
monthFolder = orderSearch.foundMonthFolder;
}
if (!orderSearch.foundOrderFolder) {
ss.toast(`⚙️ Creating Order Folder...`);
orderFolder = monthFolder.createFolder(orderFolderName);
} else {
if (!orderSearch.foundOrderFolder.getName().includes(loadingDateFormatted)) {
ui.alert(`⚠️ Duplicate Order Number ${orderNumber} found: ${orderSearch.foundMonthFolder.getName()} / ${orderSearch.foundOrderFolder.getName()}.\n\n Please change Order Number or remove duplicate Order Folder.`);
return;
}
orderFolder = orderSearch.foundOrderFolder;
}
if (tickInvoice && tickCMR) {
assembleCMR(orderFolder, orderNumber, tempFolder, loadingDateFormatted, sheetGoodsTable, OM_NR);
assembleInvoice(orderFolder, orderNumber, tempFolder, loadingDateFormatted, dueDateFormatted, sheetGoodsTable, OM_NR);
ss.toast(`✅ Documents Saved`);
} else {
if (tickCMR) {
assembleCMR(orderFolder, orderNumber, tempFolder, loadingDateFormatted, sheetGoodsTable, OM_NR);
ss.toast(`✅ CMR Document Saved`);
}
if (tickInvoice) {
assembleInvoice(orderFolder, orderNumber, tempFolder, loadingDateFormatted, dueDateFormatted, sheetGoodsTable, OM_NR);
ss.toast(`✅ Invoice Document Saved`);
}
}
let existingOrderSnapshot = orderFolder.getFilesByName(`${orderNumber} - ORDER SNAPSHOT - DO NOT EDIT`);
let existingPackingList = orderFolder.getFilesByName(`${orderNumber} - PACKING LIST`);
let packingListId = await createPackingList(existingPackingList, orderFolder, orderNumber, packingListTemplateId, sheetId, OM_NR);
createOrderSnapshot(existingOrderSnapshot, orderFolder, orderNumber, sheetId, packingListId);
syncOrderNumbers(orderSearch.orderNumbers);
console.timeEnd("fileMakerPro");
return;
}
I suspect maybe the following function could be causing the Timeout since the loop may be malfunctioning but not sure how to check or fix:
async function writePackingList(OM_NR, packingList, packingListId){
let PL_NR = await getNamedRangesAsObject_(packingList);
for (let nr in PL_NR) {
Logger.log(nr);
if (nr == "loadingAddress") {
packingList.getRangeByName(`${nr}`).setValue(OM_NR[`${nr}`].range);
} else if (nr) {
packingList.getRangeByName(`${nr}`).setValue(OM_NR[`${nr}`].range);
} else {
break;
}
}
ss.getRangeByName("transportTotalNetWeight").setFormula(`=IMPORTRANGE("${packingListId}", "PL!I15")`)
ss.getRangeByName("transportTotalGrossWeight").setFormula(`=IMPORTRANGE("${packingListId}", "PL!H15")`)
}
Any help would be appreciated as I've hit a brick wall with this. I need to figure out how to debug this error, understand why it's happening and fix it so it doesn't happen when fully deployed.
Thanks
EDIT to include additional functions:
//Make copy of Invoice template doc, get body, replace all placeholders, return
async function assembleInvoice(orderFolder, orderNumber, tempFolder, loadingDateFormatted, dueDateFormatted, sheetGoodsTable, OM_NR) {
console.time("assembleInvoice");
ss.toast(`⚙️ Assembling Invoice Document...`);
let prefix = orderNumber " - ";
let fileName = "INVOICE";
let templateTempCopy;
//Blue Invoice Template
let invoiceTemplate1 = DriveApp.getFileById("C2j_q3nUP4UTUR1KiCt07r1lATPKSjzEm-EeY109T8B4");
//Red Invoice Template
let invoiceTemplate2 = DriveApp.getFileById("1uczJT-F-JzKzaBwh_CdhJNcFkgDHfGjypJYom4vqGIo");
//Choose Invoice template based on Movement Type
let staraMovementType = OM_NR.staraMovementType.range;
if (staraMovementType.toString().includes('GB SHIPPING TO')) {
templateTempCopy = invoiceTemplate1.makeCopy(tempFolder);
} else {
templateTempCopy = invoiceTemplate1.makeCopy(tempFolder);
}
//Open working copy
let workingCopy = DocumentApp.openById(templateTempCopy.getId());
//Get body from working copy
let documentBody = workingCopy.getBody();
//Populate table
let allTables = documentBody.getTables();
let invoiceGoodsTable = allTables[3];
let itemRowTemplate = invoiceGoodsTable.getRow(invoiceGoodsTable.getNumRows() - 2);
let totalsRowTemplate = invoiceGoodsTable.getRow(invoiceGoodsTable.getNumRows() - 1);
for (let n in sheetGoodsTable) {
let tableRow = invoiceGoodsTable.appendTableRow(itemRowTemplate.copy());
tableRow.getCell(0).replaceText("{itemNo}", sheetGoodsTable[n][1]);
tableRow.getCell(1).replaceText("{itemDesc}", sheetGoodsTable[n][0]);
tableRow.getCell(2).replaceText("{commodityCode}", sheetGoodsTable[n][2]);
tableRow.getCell(3).replaceText("{cartons}", sheetGoodsTable[n][4]);
tableRow.getCell(4).replaceText("{qtyKgs}", sheetGoodsTable[n][5]);
tableRow.getCell(5).replaceText("{price}", sheetGoodsTable[n][3]);
tableRow.getCell(6).replaceText("{total}", sheetGoodsTable[n][5] * sheetGoodsTable[n][3]);
}
let totalsRow = invoiceGoodsTable.appendTableRow(totalsRowTemplate.copy());
totalsRow.getCell(3).replaceText("{cartons}", OM_NR.transportCasesCartons.range);
totalsRow.getCell(4).replaceText("{qtyKgs}", OM_NR.transportTotalNetWeight.range);
totalsRow.getCell(6).replaceText("{total}", OM_NR.transportInvoiceTotal.range);
itemRowTemplate.removeFromParent();
totalsRowTemplate.removeFromParent();
let importerAddress = `${OM_NR.importerConsigneeIntoEU.range}, ${OM_NR.importerAddress.range}`;
documentBody.replaceText("{currency}", OM_NR.staraInvoiceCurrency.range);
documentBody.replaceText("{shipToAddress}", importerAddress);
documentBody.replaceText("{billToAddress}", importerAddress);
documentBody.replaceText("{exporterCustomsInvoiceNo}", OM_NR.exporterCustomsInvoiceNo.range);
documentBody.replaceText("{exporterVAT}", OM_NR.exporterVAT.range);
documentBody.replaceText("{exporterEORI}", OM_NR.exporterEORI.range);
documentBody.replaceText("{staraOrderNo}", OM_NR.staraOrderNo.range);
documentBody.replaceText("{staraCustomerOrderNo}", OM_NR.staraCustomerOrderNo.range);
documentBody.replaceText("{transportLoadingDate}", loadingDateFormatted);
documentBody.replaceText("{dueDate}", dueDateFormatted);
documentBody.replaceText("{paymentTerms}", "30 DAYS");
documentBody.replaceText("{staraInvoiceNo}", OM_NR.staraInvoiceNo.range);
documentBody.replaceText("{incoTerms}", `${OM_NR.orderIncoterm.range} ${OM_NR.orderCountryOfDestination.range}`);
documentBody.replaceText("{transportTotalGrossWeight}", OM_NR.transportTotalGrossWeight.range);
documentBody.replaceText("{transportTotalNetWeight}", OM_NR.transportTotalNetWeight.range);
documentBody.replaceText("{transportTruckRef}", OM_NR.transportTruckRef.range);
documentBody.replaceText("{transportSeal1}", OM_NR.transportSeal1.range);
documentBody.replaceText("{transportSeal2}", OM_NR.transportSeal2.range);
documentBody.replaceText("{transportInvoiceTotal}", OM_NR.transportInvoiceTotal.range);
documentBody.replaceText("{staraInvoiceCurrency}", OM_NR.staraInvoiceCurrency.range);
workingCopy.saveAndClose();
//Gets a 'blob' of the completed document
let completedDoc = templateTempCopy.getAs('application/pdf');
//Check for existing PDF and move to trash (not fully delete)
let foundFile = orderFolder.getFilesByName(prefix fileName);
if (foundFile.hasNext()) {
foundFile.next().setTrashed(true);
}
try {
//Create PDF
ss.toast(`