I asked

Any guidance would be greatly appreciated. Thanks!

CodePudding user response：

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 = "[email protected]";
      var Subject = "First Time User Submitted Ask:  "   r[1];
      MailApp.sendEmail({to: SendTo,cc: "",subject: Subject,htmlBody: message});
      sh.getRange(setRow,9).setValue("Y")
    }
  }
}

CodePudding user response：

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.

CodePudding user response：

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 = "[email protected]";
      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
    } 
  }
}