Learn business growth with Google Analytics 4 Forums Google Analytics 4 Troubleshooting email sending issues in Google Apps Script

  • Troubleshooting email sending issues in Google Apps Script

    Posted by Abigail on 25 April 2022 at 10:49 am

    Hey, we’re working on a cool feature: getting email alerts whenever our revenue data takes a nosedive. However, it seems we aren’t receiving the emails as expected. Could any codewarriors out there lend a hand and show us where we may be going wrong in the code?

    function sendEmail() {
      const ss = SpreadsheetApp.getActive();
      const sh = ss.getSheetByName("Sheet1"); 
      const data = sh.getRange("B2:L80" + sh.getLastRow()).getValues();
      data.forEach(r => {
         let overdueValue = r[9];  
         if (overdueValue === "TRUE"){
             let name = r[10];
             let message = "Reach out to " + name;
             let subject = "Reach out to this person.";
             ////MailApp.sendEmail("xxxxx@gmail.com", subject, message);
             GmailApp.sendEmail("xxxx@gmail.com", subject, message);  
         }
      });
    }
    Isabella replied 10 months, 3 weeks ago 3 Members · 2 Replies
  • 2 Replies
  • Jacob

    Member
    1 December 2022 at 3:12 am

    Hey, I peeped your code and noticed in your conditional statement you write ‘if (overdueValue === “TRUE”)’. The thing is, ‘overdueValue’ is a boolean (true or false), not a string (‘TRUE’ or ‘FALSE’). So, your condition will always deliver ‘false’.

    Here’s an updated version of your code using boolean instead:

    `
    function sendEmail() {
    const ss = SpreadsheetApp.getActive();
    const sh = ss.getSheetByName(“Sheet1”);
    const data = sh.getRange(“B2:L80” + sh.getLastRow()).getValues();
    data.forEach(r => {
    let overdueValue = r[9];
    if (overdueValue === true){
    let name = r[10];
    let message = “Reach out to ” + name;
    let subject = “Reach out to this person.”;
    GmailApp.sendEmail(“xxxx@gmail.com”, subject, message);
    }
    });
    }
    `

    Alternatively, you can keep using string but remember to write ‘true’ in lowercase, like this: if (overdueValue.toString() === "true"). Remember to turn ‘overdueValue’ into a string first! Happy coding!

  • Isabella

    Member
    8 April 2023 at 4:43 pm

    The issue could reside in a couple of potential areas in your code.

    One problem could be in how you determine the data range in your Google Sheets. The code “B2: L80” + sh.getLastRow() is meant to get data from B2 to the last row of the L column. However, it may not be working as you expect because “B2: L80” + sh.getLastRow() translates to “B2: L80[Ending Row Number]”, which is not a valid range in Google Sheets. Instead, you’d want it to translate to “B2:L[Ending Row Number]”.

    Another potential issue could be with the comparison operator. You are checking if overdueValue is a string “TRUE”, instead of a Boolean true. Depending on how your sheet is set up, this could be the cause of the problem. If the overdueValue is recorded as a Boolean value in your sheet (i.e., true without quotes), then your if condition will never be truthy.

    Lastly, ensure that you are using the correct column index. The r[9] notation is zero-based, r[0] represents the ‘B’ column, therefore r[9] represents the ‘K’ column and not ‘J’ column. Ensure that the overdueValue represents the correct column on your sheet. Similarly, check the name variable, r[10] represents the ‘L’ column.

    Also, make sure the email value “xxxx@gmail.com” is replaced with the actual email address you want the function to send notifications to.

    You should also check if the function has the necessary permissions to access the GmailApp, and that any script triggers that are supposed to call this function are set up correctly.

Log in to reply.