I have the following flow:
- Zendesk triggers a webhook that makes a POST request with JSON object of 7 keys to my Google Apps Script.
- Based on a value of a certain key, the script defines the Spreadsheet ID and simply appends a new row with other request body values.
It works almost always, except for random cases when Google turns back with 504 Gateway Error, after 12 seconds (it's a standard Zendesk window for 'retry'). Zendesk makes multiple retries for up to 5 times, and at last it always ends up with 200 OK. However, as I understand, the GAPPS stacks these calls and execute them all, which ends with up to 5 new similar rows being added to the doc.
My question is, what could cause such random delays in script execution, and how do I omit them?
I'm leaving my code for better understanding:
function doPost(request) {
var jsonString = request.postData.getDataAsString();
var jsonData = JSON.parse(jsonString);
var ticketId = jsonData.id;
var comment = jsonData.comment;
var game = jsonData.game;
var locale = jsonData.locale;
var assignee = jsonData.assignee;
var priority = jsonData.priority;
var ssId = getSpreadsheetId(locale);
var sheetName = getSheetName(comment, priority);
var updated = new Date();
var sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName);
sheet.appendRow([ticketId, comment, "", game, assignee, priority, "New", updated]);
}
function getSheetName(comment, priority) {
if ((comment.search("#to translate") >= 0) && priority == "Urgent") {
var sheetName = "SEND URG";
} else if (comment.search("#to translate") >= 0) {
var sheetName = "SEND";
} else if (comment.search("#to understand") >= 0) {
var sheetName = "UNDERSTAND";
}
return sheetName;
}
function getSpreadsheetId(locale) {
switch (locale) {
case "Deutsch":
var spreadSheetId = "german_doc_id";
break;
case "Italiano":
var spreadSheetId = "italian_doc_id";
break;
}
return spreadSheetId;
}
My assumption was that it was a matter of the size of the document that's being opened with SpreadsheetApp.openById(id).openByName(name). However, after clearing all the documents completely, the issue still persists. The documents sheets are approximately ~200 rows at the moment, not more than 5 sheets per doc.