Apps Script Pattern. Stop Script Execution on conditions from Sheet
Jul 28, 2023
The common pattern for checking the business logic before executing automation:
You show the message with instructions on what the user needs to do before running the script. To accomplish this:
- Calculate the message in Google Sheet’s cell
- Create a named range of this cell
- Add this code to your script:
/**
* @param {String} rangeName
*
* @returns {Boolean} toStopExecution
*/
function getStopMessageBoxFromNamedRange_(rangeName) {
var ss = SpreadsheetApp.getActive();
var r = ss.getRangeByName(rangeName);
var v = r.getValue();
if (v === '') {
return false;
}
var stopHeader = 'The script was stopped';
Browser.msgBox(stopHeader, v, Browser.Buttons.OK);
return true;
}
The usage is simple:
function myMainAutomation_() {
var toStopExecution = getStopMessageBoxFromNamedRange_('exit_msg');
if (toStopExecution) return;
// other code...
doTheRestOfWork_();
}
That’s it. What patterns do you use?