CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Tayzer Damasceno
Tayzer Damasceno

Reputation: 302

Get the date with format on google sheets script

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.

screenshot of relevant range

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})"

Upvotes: 0

Views: 87

Answers (1)

Cooper
Cooper

Reputation: 64100

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})
}

Upvotes: 2

Related Questions