Apps Script Pattern. Stop Script Execution on conditions from Sheet

Max Makhrov
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:

  1. Calculate the message in Google Sheet’s cell
  2. Create a named range of this cell
  3. 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?

--

--

Max Makhrov
Max Makhrov

Written by Max Makhrov

Google Sheets Developer, master of Online Accounting

No responses yet