Get the date with format on google sheets script

Question

I'm using this script to take some information from the below table (this table information is used to send the information by email) and I was getting a huge data format, so I tried to take the day, month and year using the .getDay, .getMonth() and .getFullYear(), but I'm receiving the error message:

"Exception: Service invoked too many times for one day: email. (line 108, file "Code")"

I imagine that is because I'm using get 3 times inside a loop multiple times, is there a way to take without having this error message? Because I'd like to take the format date as it is on the spreadsheet (Day-shortMonth-year)

I saw about the Utilities.formatDate() but I couldn't be able to think a on way to use it.

Here is a part of the script, I didn't put everything cause is a little bit big script, but let me know if you need and I edit and put everything.

function myFunction() {
  let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let rows = ss.getDataRange().getValues();
  var emailRecipients = rows[1][0]+"@gmail.com";
  var CCemail = rows[1][1]+"@gmail.com";
  var emailSubject = "Vacation Request";
for (i = 1; i < rows.length; i++) {
    let bg_color;
    switch(rows[i][5]) {
        case 'Reflected':
              bg_color = 'green';
              break;
        case 'Cancelled':
              bg_color = 'Darkred';
              break;
        case 'Pending':
              bg_color = 'orange';
              break;
        case 'Rejected':
              bg_color = 'red';
              break;
        default:
              bg_color = 'white';
    }
    htmlBody += `   
     <table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:10pt;font-family:Arial;width:0px;border-collapse:collapse;border:none">
       <colgroup>
              <col width="100">
              <col width="100">
              <col width="100">
              <col width="100">
       </colgroup>
       <tbody>  
          <tr style="height:21px">
            <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;text-align:center;border:1px solid rgb(204,204,204)">
            ${rows[i][2].getDate()+"-"+rows[i][2].getMonth()+"-"+rows[i][2].getFullYear()}
          <\/td>
            <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;text-align:center;border:1px solid rgb(204,204,204)">
            ${rows[i][3]}
          <\/td>
            <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;text-align:center;border:1px solid rgb(204,204,204)">
            ${rows[i][4]}
          <\/td>
          <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;background-color:${bg_color};font-weight:bold;color:rgb(255,255,255);text-align:center;border:1px solid rgb(204,204,204)">  
            ${rows[i][5]}
          <\/td>
        <\/tr>
     <\/table>`;

The line 108 is:

"MailApp.sendEmail({to: emailRecipients,subject: emailSubject,htmlBody: htmlBody,cc: CCemail})"

When you figure what line is causing the problem let us know perhaps we can help. — Cooper
Yes, an edit is definitely needed - the part of the script that causes the issue is not the part you published. As the error clearly states, you exceeded the quota, most likely for sending emails in one day — Oleg Valter
Yep, quota issue alright - you will have to wait until it replenishes (24 hours appx, but the window in which it happens is unknown) — Oleg Valter

Cooper Cooper · Accepted Answer · 2020-06-13T23:48:16

Try adding something like this into your loop so you wont get this problem any more.

if(MailApp.getRemainingDailyQuota()>0){
   MailApp.sendEmail({to: emailRecipients,subject: emailSubject,htmlBody: htmlBody,cc: CCemail})
}

Get the date with format on google sheets script

1 Answers