1

I have a script that sends emails to a list of recipients per row. For every row it checks certain conditions and sends an email with different information taken from different columns of that row. The file is the following:

https://docs.google.com/spreadsheets/d/1n5nRET7M6NnI1gZg_WRA4xiEA5ZDKsfUhTnAoP_xpF4/edit?usp=sharing

This question is divided in two parts:

Part 1: I want to stop a script when it reaches the last row, of a specific column (I believe this is the easier way), with content. I used the following code and it used to work. However, now it's not wokring and I don't understand why.

     var sheet = SpreadsheetApp.getActiveSpreadsheet();
     var limit = sheet.getLastRow(); //number of rows in the sheet
     var Avals = sheet.getRange("E:E").getValues();
     var Alast = Avals.filter(String).length;
     for (var row = 3; row <= Alast+1; row = row + 1) {

Does anyone have an idea of what could be the issue and have any alternatives?

Part 2: The current code used to stop working after line 105 (from what Iw as able to estimate). Basically, if the last row with content was 85, it would stop the script on that row. However, if the last row with content was 106, the script only worked until 105 and would not sned the email on line 106 (despite meeting the required conditions). Does anyone know how to fix this?

Thank you in advance.

UPDATE:

Part 1 of the question is solved. I found the problem I was having with the files. Part 2 of the questions is still a problem, though. Thank you

1

1 Answer 1

0

There is a logical problem here:

  var Alast = Avals.filter(String).length;
  for (var row = 0; row < Alast; row = row + 1) {

Say you have values in rows 1–10, then a blank row 11, and again values in rows 12–20. Alast will not be 20 but 19 because there are only 19 values in the column. Your for loop will only reach row 19 and will not look at row 20.

To make it work, get the values on all rows until sheet.getLastRow(), and check inside the loop whether the column E is blank or not, like this:

function sendEmail() {
  const homeBook = SpreadsheetApp.getActive();
  const sheet = homeBook.getSheets()[0];
  const limit = sheet.getLastRow();
  const values = sheet.getRange(`A3:E${limit}`).getValues();
  values.forEach(row => {
    if (!row[4] // column E is blank
      || row[0] === 'Skip' // column A equals 'Skip'
      || !String(row[1]).includes('@') // column B does not contain an email address
      || !row[2]) {
      return;
    }
    // ...otherwise, do stuff
    // MailApp.sendEmail(row[1], 'Hello', row[4]);
  });
}
4
  • Hello, Thank you for your comment. I'm trying to get a file ready to share, so you can get an idea of how the file is. However I'm having some trouble because the script (exactly the same one) is behaving differently between two files that are the same: a file in my private company drive and the original file stored in a shared folder. Commented Dec 30, 2022 at 12:21
  • Hello, I put a link in the post. It's a copy I saved on my personal drive. However I'm having some trouble because the script (exactly the same one) is behaving differently between the files. Both the file I shared and a file in my private company drive, the script stops on the row with the first empty cell, so part 1 of the question is OK. The original file stored in a shared folder, the same script runs until the last row. So part 1 of the question isn't working properly. Could it be because the original file has more than one sheet? Commented Dec 30, 2022 at 12:45
  • There is a logical problem in your code. Try fixing the problem as outlined in the answer. Please ask only one question per post. Commented Dec 30, 2022 at 12:49
  • Hello, I was able to fix the issue with part 1 of the question. part 2 isn't causing the same problems now for some reason. I'll test your suggestion and see if it works, so it fixes the logical problem. Thank you for your help. Commented Dec 30, 2022 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.