Data Validation - check for blank cells

The following Google Apps Script code was developed as a tool to check that all the necessary data existed in a spreadsheet before another function was triggered. The reason for this was that if any data was missing it would affect the student Group files I was creating (eg no email address or name) that would eventually prevent further functions from running at a later date. So I needed a way to go through a sheet and ultimately make sure there were no blank cells, at the same time however I wanted to produce a user-friendly message if there was a blank (rather than just preventing another function from running).

I developed the below validation tool to go through each column in turn and down the row looking for a blank (it checks about 1,700+ individual cells in the project I use it). If a blank cell is found the code identifies the column/row and calls a popup function to run, then returns a flag value to determine how the parent function should behave.

checkData
The function requires 4 input parameters:

1. sheetName - this helps identify to the user which sheet need attention - use 'getSheetName'.
2. sheet - this is the name of the sheet to point the tool at - use 'getSheetByName'.
3. lastRow - this is so the tool knows how far down the data to scan - use 'getLastRow'.
4. lastCol - this is the number of columns to scan across - use 'getLastColumn' (though in my code I just specify a value as I had a few columns which needed to be blank).

The code creates two loops (one for across the columns and the other for down the rows in each column). It sets the errorFlag as false before starting, then uses an if to check the value of each cell is not blank ('getValues'). Should a blank be found a 'switch' is used with an input parameter of i (the column number) to identify the letter of the column - to help produce the friendly popup later. Now the validationPopup (detailed below) can be called with the necessary parameters passed to it, along with the errorFlag now set to true.

validationPopup
When a blank cell is found by the above function we now need to trigger a popup message to alert the user, which we do here. 3 input parameters are required:

1. sheetName - this helps identify to the user which sheet need attention.
2. column - this has now been calculated as the relevant column letter (via the 'switch').
3. j - this is the row number from the sheet.

'getUi' allows us to access the spreadsheet's user-interface so that we can then create the 'alert' popup which displays the customised message identifying the exact cell reference for the user.

Data Validation - Check for blank cells.xlsx