Help with execution time
1 view
Skip to first unread message
C. Omar Kebbeh
Jul 4, 2024, 10:19:54 PM (3 hours ago) Jul 4
to Google Apps Script Community
Hi all,
I have the script below which sends daily reminders to teams at 7am. I worked very well at the begining, but now that the source file have over 300 rows, it always times out.
Any advice on how to address the executions' time out? Should I reduce number of rows?
thanks
function dailyReminder() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Form responses 1');
var sheet2=ss.getSheetByName('Pending Requests');
var subject = sheet2.getRange(2,7).getValue();
var n=sheet1.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = sheet1.getRange(i,2).getValue();
var name=sheet1.getRange(i,3).getValue();
var details=sheet1.getRange(i,5).getValue();
var title=sheet1.getRange(i,6).getValue();
var link=sheet1.getRange(i,7).getValue();
var urgent=sheet1.getRange(i,9).getValue();
var clearance_date=sheet2.getRange(i,47).getValue();
var status=sheet2.getRange(i,46).getValue();
var casenumber=sheet2.getRange(i,36).getValue();
var teamclearancedate=sheet2.getRange(i,11).getValue();
var poclearancedate=sheet2.getRange(i,12).getValue();
var datereceived=sheet2.getRange(i,10).getValue();
var team=sheet2.getRange(i,43).getValue();
var clearancedirector = sheet2.getRange(i,8).getValue()
var clearance = sheet2.getRange(i,7).getValue();
var emailSentPo=sheet1.getRange(i,15).getValue();
var tracker="https://docs.google.com/spreadsheets/d/1GGAvcrc-O-C5PSaYdv3iVhdUcool_yG-pFbvr9M/edit?resourcekey#gid=7827099";
if ( !(clearance == 'Cleared') && !(clearance == 'Returned') && (status == "Case") && (team == "Budget")){
var subject="Automated Clearance System (ACS): Pending Clearance/Approval Request - Case Number " + casenumber;
var message = "Dear Budget Team," + "<br><br>"+
"You have a pending clearance/approval request with the following details:" + "<br><br>"+
"Date Received in PO: " + datereceived + "<br><br>"+
"Case Number: " + casenumber + "<br><br>"+
"Requested clearance date: " + clearance_date + "<br><br>"+
"Type of Clearance/Approval Requested: " + details + "<br><br>"+
"Title of the document/correspondence: " + title + "<br><br>"+
"Link to document or folder: " + link + "<br><br>"+
"Urgent: " + urgent + "<br><br>"+
"Name of requestor: " + name + "<br><br>"+
"Email address of the requestor: " + emailAddress + "<br><br>"+
"Please note that your team must clear the request before COB: " + teamclearancedate + "." + "<br><br>"+
"Please note that PO must clear before COB: " + poclearancedate + "." + "<br><br>"+
"Please update the Automated Clearance System (ACS) after your clearance/approval: " + tracker + "<br><br>";
GmailApp.sendEmail(budget, subject, "",{htmlBody:message,
cc: emailAddress,
});
}
else if ( !(clearance == 'Cleared') && !(clearance == 'Returned') && (status == "Case") && (team == "MEL")){
var subject="Automated Clearance System (ACS): Pending Clearance/Approval Request - Case Number " + casenumber;
var message = "Dear MEL Team," + "<br><br>"+
"You have a pending clearance/approval request with the following details:" + "<br><br>"+
"Date Received in PO: " + datereceived + "<br><br>"+
"Case Number: " + casenumber + "<br><br>"+
"Requested clearance date: " + clearance_date + "<br><br>"+
"Type of Clearance/Approval Requested: " + details + "<br><br>"+
"Title of the document/correspondence: " + title + "<br><br>"+
"Link to document or folder: " + link + "<br><br>"+
"Urgent: " + urgent + "<br><br>"+
"Name of requestor: " + name + "<br><br>"+
"Email address of the requestor: " + emailAddress + "<br><br>"+
"Please note that your team must clear the request before COB: " + teamclearancedate + "." + "<br><br>"+
"Please note that PO must clear before COB: " + poclearancedate + "." + "<br><br>"+
"Please update the Automated Clearance System (ACS) after your clearance/approval: " + tracker + "<br><br>";
GmailApp.sendEmail(mel, subject, "",{htmlBody:message,
cc: emailAddress,
});
}
else if ( !(clearance == 'Cleared') && !(clearance == 'Returned') && (status == "Case") && (team == "FO")){
var subject="Automated Clearance System (ACS): Pending Clearance/Approval Request - Case Number " + casenumber;
var message = "Dear Front Office," + "<br><br>"+
"You have a pending clearance/approval request with the following details:" + "<br><br>"+
"Date Received in PO: " + datereceived + "<br><br>"+
"Case Number: " + casenumber + "<br><br>"+
"Requested clearance date: " + clearance_date + "<br><br>"+
"Type of Clearance/Approval Requested: " + details + "<br><br>"+
"Title of the document/correspondence: " + title + "<br><br>"+
"Link to document or folder: " + link + "<br><br>"+
"Urgent: " + urgent + "<br><br>"+
"Name of requestor: " + name + "<br><br>"+
"Email address of the requestor: " + emailAddress + "<br><br>"+
"Please note that your team must clear the request before COB: " + teamclearancedate + "." + "<br><br>"+
"Please note that PO must clear before COB: " + poclearancedate + "." + "<br><br>"+
"Please update the Automated Clearance System (ACS) after your clearance/approval: " + tracker + "<br><br>";
GmailApp.sendEmail(fo, subject, "",{htmlBody:message,
cc: emailAddress,
});
}
else if ( !(clearance == 'Cleared') && !(clearance == 'Returned') && (status == "Case") && (team == "PD")){
var subject="Automated Clearance System (ACS): Pending Clearance/Approval Request - Case Number " + casenumber;
var message = "Dear Project Design Team," + "<br><br>"+
"You have a pending clearance/approval request with the following details:" + "<br><br>"+
"Date Received in PO: " + datereceived + "<br><br>"+
"Case Number: " + casenumber + "<br><br>"+
"Requested clearance date: " + clearance_date + "<br><br>"+
"Type of Clearance/Approval Requested: " + details + "<br><br>"+
"Title of the document/correspondence: " + title + "<br><br>"+
"Link to document or folder: " + link + "<br><br>"+
"Urgent: " + urgent + "<br><br>"+
"Name of requestor: " + name + "<br><br>"+
"Email address of the requestor: " + emailAddress + "<br><br>"+
"Please note that your team must clear the request before COB: " + teamclearancedate + "." + "<br><br>"+
"Please note that PO must clear before COB: " + poclearancedate + "." + "<br><br>"+
"Please update the Automated Clearance System (ACS) after your clearance/approval: " + tracker + "<br><br>";
GmailApp.sendEmail(deisign, subject, "",{htmlBody:message,
cc: emailAddress,
});
}
else if ( !(clearance == 'Cleared') && !(clearance == 'Returned') && (status == "Case") && (team == "DOC")){
var subject="Automated Clearance System (ACS): Pending Clearance/Approval Request - Case Number " + casenumber;
var message = "Dear DOC Team," + "<br><br>"+
"You have a pending clearance/approval request with the following details:" + "<br><br>"+
"Date Received in PO: " + datereceived + "<br><br>"+
"Case Number: " + casenumber + "<br><br>"+
"Requested clearance date: " + clearance_date + "<br><br>"+
"Type of Clearance/Approval Requested: " + details + "<br><br>"+
"Title of the document/correspondence: " + title + "<br><br>"+
"Link to document or folder: " + link + "<br><br>"+
"Urgent: " + urgent + "<br><br>"+
"Name of requestor: " + name + "<br><br>"+
"Email address of the requestor: " + emailAddress + "<br><br>"+
"Please note that your team must clear the request before COB: " + teamclearancedate + "." + "<br><br>"+
"Please note that PO must clear before COB: " + poclearancedate + "." + "<br><br>"+
"Please update the Automated Clearance System (ACS) after your clearance/approval: " + tracker + "<br><br>";
GmailApp.sendEmail(doc, subject, "",{htmlBody:message,
cc: emailAddress,
});
}
}}
Keith Andersen
Jul 4, 2024, 10:22:22 PM (3 hours ago) Jul 4
to google-apps-sc...@googlegroups.com
Instead of one function - split the range in half And assign one range to one function and the other range to another function and have them fire (trigger) at 7:00 and 7:30.
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7b9acc31-852a-4273-8aa9-4e0e6bb6a5fbn%40googlegroups.com.
C. Omar Kebbeh
Jul 4, 2024, 10:25:04 PM (3 hours ago) Jul 4
to google-apps-sc...@googlegroups.com
Keith thanks. Google Apps script novice.. Do you mind explaining further or showing me how to do that?
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4yLJ1FcY97z0NDkMbJhmxUN3etmc%3DXvAh61ENwDojyFuA%40mail.gmail.com.
Keith Andersen
Jul 4, 2024, 10:31:27 PM (3 hours ago) Jul 4
to google-apps-sc...@googlegroups.com
I can jump on my computer tomorrow and take a better look than on my phone now and I'll get back to you.
👍
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKxLt_k6XXyMFK7xdWcJN8fV-oD5gj4imEwYc_JTw58ot_wuyA%40mail.gmail.com.
C. Omar Kebbeh
Jul 4, 2024, 10:44:25 PM (3 hours ago) Jul 4
to google-apps-sc...@googlegroups.com
I appreciate it.
Sent from my iPhone
On Jul 4, 2024, at 10:31 PM, Keith Andersen <keith.a...@gmail.com> wrote:
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4za0p-X_T8W6U4d9GFc8O73dn-49E0%3Dv2%3DdUz-RVuRkWQ%40mail.gmail.com.
Reply all
Reply to author
Forward
0 new messages