Google Sheets Script Error: Exception: Ranges must have at least one range

Question

I asked this question and thought I had gotten the resolution, however, after it ran properly the first time, the next day I ended up with a bunch of trigger errors stating: Exception: Ranges must have at least one range.

The new rows of data being added are still being marked "Y" in Column I, as if the email got sent out for it, but I'm either not receiving an email, only receiving one email when more than one row was added at one time, or receiving an email containing information from the wrong row of data (the last/oldest row rather than the new row).

This is the script I'm using to send an email notification whenever new row(s) of data are added - it should be sending one email per new row of data, and then mark "Y" in Column I of the corresponding row.

  function email() {
  var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("First Time Users");
  var StartRow = 2;
  var LastRow = ActiveSheet.getLastRow();
  var RowRange = LastRow - StartRow + 1;
  var WholeRange = ActiveSheet.getRange(StartRow, 1, RowRange, 9);
  var AllValues = WholeRange.getValues();
  var ranges = [];
  for (var i = 0; i < AllValues.length; i++) {
    var CurrentRow = AllValues[i];
    var EmailSent = CurrentRow[8];
    if (CurrentRow[7] == "Y" && EmailSent != "Y") {
      var message =
        "<p><b>Request: </b>" + CurrentRow[0] + "</p>" +
        "<p><b>Account: </b>" + CurrentRow[1] + "</p>" +
        "<p><b>Appointment Created Date: </b>" + CurrentRow[4] + "</p>" +
        "<p><b>User: </b>" + CurrentRow[5] + "</p>";
      var setRow = i + StartRow;
      var SendTo = "testemail@gmail.com";
      var Subject = "First Time User Submitted Ask:  " + CurrentRow[1];
      MailApp.sendEmail
        ({
          to: SendTo,
          cc: "",
          subject: Subject,
          htmlBody: message,
        });
      ranges.push("I" + setRow);
    }
  }
  ActiveSheet.getRangeList(ranges).setValue("Y");
}

This is the trigger I have which is currently set to run every 24 hours...previously I had it run every 5 minutes (before this, I had used an On Change trigger, as shown in my previous question linked above):

Any guidance would be greatly appreciated. Thanks!

Answer 1

From your code you are running the ActiveSheet.getRangeList(ranges).setValue("Y"); every time, so it will still run even if all range from column I is filled with Y which is why the ranges array is empty.

From the logic you've stated

it should be sending one email per new row of data, and then mark "Y" in Column I of the corresponding row.

I think it makes more sense if you put the line ActiveSheet.getRangeList(ranges).setValue("Y"); inside the condition if (CurrentRow[7] == "Y" && EmailSent != "Y") after the MailApp.sendEmail. Since you are only setting the value "Y" in column I if EmailSent (which is also column I) is not Y and after sending the email.

See final code below:

  function email() {
  var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("First Time Users");
  var StartRow = 2;
  var LastRow = ActiveSheet.getLastRow();
  var RowRange = LastRow - StartRow + 1;
  var WholeRange = ActiveSheet.getRange(StartRow, 1, RowRange, 9);
  var AllValues = WholeRange.getValues();
  var ranges = [];
  for (var i = 0; i < AllValues.length; i++) {
    var CurrentRow = AllValues[i];
    var EmailSent = CurrentRow[8];
    if (CurrentRow[7] == "Y" && EmailSent != "Y") {
      var message =
        "<p><b>Request: </b>" + CurrentRow[0] + "</p>" +
        "<p><b>Account: </b>" + CurrentRow[1] + "</p>" +
        "<p><b>Appointment Created Date: </b>" + CurrentRow[4] + "</p>" +
        "<p><b>User: </b>" + CurrentRow[5] + "</p>";
      var setRow = i + StartRow;
      var SendTo = "da@devopstraining.club";
      var Subject = "First Time User Submitted Ask:  " + CurrentRow[1];
      MailApp.sendEmail
        ({
          to: SendTo,
          cc: "",
          subject: Subject,
          htmlBody: message,
        });
      ranges.push("I" + setRow);
      ActiveSheet.getRangeList(ranges).setValue("Y"); //moved inside the condition
    } 
  }
}

Answer 2

From your error message of Exception: Ranges must have at least one range., I guessed that in your script, an array of ranges is empty. When ranges is an empty array, such an error occurs. So, how about the following modification?

From:

ActiveSheet.getRangeList(ranges).setValue("Y");

To:

if (ranges.length == 0) return;
ActiveSheet.getRangeList(ranges).setValue("Y");

• By this, when ranges is empty array, ActiveSheet.getRangeList(ranges).setValue("Y") is not run. Your current error can be avoided.

Answer 3

Try this:

ActiveSheet.getRangeList(ranges).getRanges().forEach(r => r.setValue("Y"));

or this:

function email() {
  const ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("First Time Users");
  var sr = 2;
  var lr = sh.getLastRow();
  var numrows = lr - sr + 1;
  var rg = sh.getRange(sr, 1, numrows, 9);
  var vs = rg.getValues();
  var ranges = [];
  for (var i = 0; i < vs.length; i++) {
    var r = vs[i];
    if (r[7] == "Y" && r[8] != "Y") {
      var message =
        "<p><b>Request: </b>" + r[0] + "</p>" +
        "<p><b>Account: </b>" + r[1] + "</p>" +
        "<p><b>Appointment Created Date: </b>" + r[4] + "</p>" +
        "<p><b>User: </b>" + r[5] + "</p>";
      var setRow = i + sr;
      var SendTo = "testemail@gmail.com";
      var Subject = "First Time User Submitted Ask:  " + r[1];
      MailApp.sendEmail({to: SendTo,cc: "",subject: Subject,htmlBody: message});
      sh.getRange(setRow,9).setValue("Y")
    }
  }
}