Trying to create a script that emails if specific cells/ranges within a sheet are blank

Question

I'm looking to send an email at the end of the day (I can use a trigger for this) on a daily basis if certain cells are left blank.

In other words, certain cells are required to be filled out at the end of each day, and if not done, I would like to send an email (to multiple addresses, if possible) to alert the manager.

For example, the cells I am interested in checking for ANY VALUE are: B18:B19, C24 (there are many more ranges & individual cells I am looking to check)

With what I have, I receive an error code

"Cannot find function isBlank in object"

I am ridiculously new to Scripts and this is a piecemeal chunk of code I have found on other forums

function myFunction() {
  var ui = SpreadsheetApp.getUi();
  var sheet = SpreadsheetApp.getActive().getSheetByName("Routes & Hours"); //this is the sheet that the data to be checked is in
  var sunCells = [
    [sheet.getRange("B18:B19").getValue(),
     sheet.getRange("C24").getValue()
    ]
                 ];
  if(sunCells.isBlank()){      
MailApp.sendEmail("XXXXX@gmail.com", "incomplete required cells", "message");
    } 
}

Also, is there any way of determining which cells are the blank ones and sending that in the email alert?

Any help is greatly appreciated

Answer 1

• You want to check whether "B18", "B19" and "C24" are empty, respectively.
• If the empty cell is included, you want to send an email including a1Notation of the empty cell.

If my understanding is correct, how about this modification? I think that there are several answers for your situation, so please think of this as one of them.

Modification points:

• In your script, sunCells is an array including the cell value. And sheet.getRange("B18:B19").getValue() retrieves only the value of "B18".
• isBlank() can be used for Range.

The script which reflected above points is as follows.

Modified script 1:

If you use "B18:B19" as "B18" and "B19", you can use the following script. In this modification, a1Notation is separated by only one cell like "B18:B19" to "B18" and "B19". By this, each cell can be checked. In this script, when "B18:B19" is used for isBlank(), if both "B18" and "B19" are empty, it becomes true.

From:

var sunCells = [
  [sheet.getRange("B18:B19").getValue(),
   sheet.getRange("C24").getValue()
  ]
];
if(sunCells.isBlank()){      
  MailApp.sendEmail("XXXXX@gmail.com", "incomplete required cells", "message");
}

To:

var sunCells = ["B18", "B19", "C24"]; // Modified
var ranges = sheet.getRangeList(sunCells).getRanges(); // Added
var res = sunCells.filter(function (_, i) {return ranges[i].isBlank()}); // Added
if (res.length > 0) { // Modified
  MailApp.sendEmail("XXXXX@gmail.com", "incomplete required cells " + res, "message"); // Modified
}

Modified script 2:

If you want to use the a1Notation of "B18:B19" as it is, you can use the following script. In this script, "B18:B19" is parsed to "B18" and "B19". By this, each cell can be checked. When you use this, please run myFunction().

// This method is from https://stackoverflow.com/a/21231012/7108653
function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function myFunction() {
  var ui = SpreadsheetApp.getUi();
  var sheet = SpreadsheetApp.getActive().getSheetByName("Routes & Hours");
  var sunCells = ["B18:B19", "C24"];

  // The following script was modified.
  var cells = sunCells.map(function(e) {
    var temp = [];
    var range = sheet.getRange(e);
    var r = {
      startRowIndex: range.getRow(),
      endRowIndex: range.getRow() + range.getNumRows(),
      startColumnIndex: range.getColumn(),
      endColumnIndex: range.getColumn() + range.getNumColumns(),
    }
    for (var i = r.startRowIndex; i < r.endRowIndex; i++) {
      for (var j = r.startColumnIndex; j < r.endColumnIndex; j++) {
        temp.push(columnToLetter(j) + i);
      }
    }
    return temp;
  })
  cells = Array.prototype.concat.apply([], cells);
  var ranges = sheet.getRangeList(cells).getRanges();
  var res = cells.filter(function (_, i) {return ranges[i].isBlank()});
  if (res.length > 0) {
    MailApp.sendEmail("XXXXX@gmail.com", "incomplete required cells " + res, "message");
  }
}

Note:

• This is a simple modification. So please modify it for your situation.
• If you want to add and modify the cell you want to check, please add them to sunCells.

References:

• Class RangeList
• isBlank()

If I misunderstand your question, please tell me. I would like to modify it.