I've been using this function without any issues for over a year now but suddenly yesterday, execution time started to increase excessively and the function fails most of the time (not all the time).
I have 15 different variations of this function scanning my GMAIL every minute (each function only actually receives an e-mail every 15 minutes, not every minute).
Since it doesn't fail all the time, it makes me think that perhaps the GMAIL API changed and I'm now exceeding my request limit? However if that was the case, I would think it would specify that in the error. Error Exception: Not found at SSSalesImportFromGmail(SundanceSquare:77:11)
Line 77:
message.markRead();
message.unstar();
Gmail.Users.Messages.remove("me", message.getId()); // Added
}
}
Full function:
function SSSalesImportFromGmail() {
var ss = SpreadsheetApp.getActive(); // Get the spreadsheet file once
//gets first(latest) message with set label
var threads = GmailApp.getUserLabelByName('Sundance Square').getThreads(0,1);
if (threads && threads.length > 0) {
var message = threads[0].getMessages()[0];
// Get the first email message of a threads
var content = message.getPlainBody();
var tmp,
truncatedContent = content.match(/^([^\n]+?\n){5}/)[0];
// Get the plain text body of the email message
// You may also use getRawContent() for parsing HTML
// Implement Parsing rules using regular expressions
if (truncatedContent) {
tmp = truncatedContent.match(/Date Tripped:\s*([:\w\s]+)\r?\n/);
var tripped = (tmp && tmp[1]) ? tmp[1].trim() : 'N/A';
tmp = truncatedContent.match(/Business Date:\s([\w\s]+\(\w+\))/);
var businessdate = (tmp && tmp[1]) ? tmp[1].trim() : 'N/A';
// Get all of the attachments and loop through them.
var attachments = message.getAttachments();
for (var i = 0; i < attachments.length; i++) {
var attachment = attachments[i];
var title = attachment.getName();
// Is the attachment a CSV file
attachment.setContentTypeFromExtension();
var table = Utilities.parseCsv(attachment.getDataAsString());
if (attachment.getContentType() === "text/csv") {
switch (title) { // Update the specified sheets
case "Sales.csv":
/**
* Clears the sheet of values & formatting and inserts the new table
* using the Apps Script built-in CSV parser.
* @param {string} sheetName - The name of the sheet to update
* @returns {undefined}
*/
ss.getSheetByName("Sundance Square Sales").getRange("A:M").clear();
ss.getSheetByName("Sundance Square Sales").getRange(1, 1, table.length, table[0].length).setValues(table);
ss.getSheetByName("Sundance Square Sales").getRange("C2").setValue(tripped);
ss.getSheetByName("Sundance Square Sales").getRange("C1").setValue(businessdate);
break;
case "Labor.csv":
ss.getSheetByName("Sundance Square Labor").getRange("A:L").clear();
ss.getSheetByName("Sundance Square Labor").getRange(1, 1, table.length, table[0].length).setValues(table);
break;
case "ServerPerformance.csv":
ss.getSheetByName("Sundance Square Servers").getRange("C2:O").clear();
ss.getSheetByName("Sundance Square Servers").getRange(2, 3, table.length, table[0].length).setValues(table);
break;
case "ServerContest.csv":
ss.getSheetByName("Sundance Square Servers").getRange("R3:T").clear();
ss.getSheetByName("Sundance Square Servers").getRange(3,18,table.length, table[0].length).setValues(table);
break;
case "ServerNetSales.csv":
ss.getSheetByName("Sundance Square Servers").getRange("W3:Y").clear();
ss.getSheetByName("Sundance Square Servers").getRange(3,23,table.length, table[0].length).setValues(table);
break;
}
}
}
if( message.getSubject().indexOf('END OF DAY') !== -1) {
SSlogTodaysSales();
//SSlogTodaysServers();
}
if( message.getSubject().indexOf('END OF WEEK') !== -1) {
SSlogTodaysSales();
//SSlogTodaysServers();
SSlogWeeksLabor();
}
}
//Add Current Time-Clock Data
SSaddNote();
//marks the Gmail message as read, unstars it and deletes it using Gmail API (Filter sets a star)
message.markRead();
message.unstar();
Gmail.Users.Messages.remove("me", message.getId()); // Added
}
}