Apps Script has no “last business day of the month” trigger. The reliable pattern is a daily time trigger plus a guard at the top of the job. isLastBusinessDayOfMonth(date, tz, holidays?) returns true only when date, read in timezone tz via Utilities.formatDate, is the final Monday–Friday of its month that isn’t in the optional holidays list of 'YYYY-MM-DD' strings. It finds that day by walking back from the month end and skipping weekends and holidays, then compares it with today.
When to use
- Month-end invoices, payouts, close-the-books exports, “send the monthly report” jobs
- Any monthly job where “the 30th” breaks whenever the 30th lands on a Saturday
- Jobs that must skip company holidays (pass them in from a Config tab)
How to use this snippet
/**
* True if `date` (in timezone `tz`) is the last Mon–Fri of its month
* that is not listed in `holidays`.
*
* @param {Date} date usually new Date()
* @param {string} tz e.g. SpreadsheetApp.getActive().getSpreadsheetTimeZone()
* @param {string[]=} holidays optional ['2026-12-31', ...] in YYYY-MM-DD
* @return {boolean}
*/
function isLastBusinessDayOfMonth(date, tz, holidays) {
var parts = Utilities.formatDate(date || new Date(), tz, 'yyyy-MM-dd').split('-');
var y = Number(parts[0]), m = Number(parts[1]), today = Number(parts[2]);
var skip = {};
(holidays || []).forEach(function (h) { skip[String(h).trim()] = true; });
var pad = function (n) { return (n < 10 ? '0' : '') + n; };
// Pure calendar math in UTC from here on: no DST or server-timezone drift.
var lastDay = new Date(Date.UTC(y, m, 0)).getUTCDate();
for (var d = lastDay; d >= 1; d--) {
var dow = new Date(Date.UTC(y, m - 1, d)).getUTCDay(); // 0 Sun … 6 Sat
var key = y + '-' + pad(m) + '-' + pad(d);
if (dow !== 0 && dow !== 6 && !skip[key]) return d === today;
}
return false; // every day was a weekend/holiday (shouldn't happen)
}
Example
// Installed once as a daily time trigger (e.g. 7am). The guard decides if today is the day.
function monthEndExport() {
var ss = SpreadsheetApp.getActive();
var tz = ss.getSpreadsheetTimeZone();
var holidays = ss.getSheetByName('Config')
.getRange('B2:B20').getDisplayValues()
.map(function (r) { return r[0]; })
.filter(String); // '2026-12-31', ...
if (!isLastBusinessDayOfMonth(new Date(), tz, holidays)) return; // not today, exit quietly
runMonthEndExport_(ss);
}
Tips:
- Pass the spreadsheet’s timezone (
getSpreadsheetTimeZone()), not whatever the server happens to use. A 1am trigger can be “yesterday” in another zone. - Holiday strings must be exactly
YYYY-MM-DD. Read them withgetDisplayValues()and format the column as plain text (oryyyy-mm-dd) so Sheets doesn’t turn them into Date objects. - Run the daily trigger in the morning, not right after midnight, so a late-night timezone edge can’t land the run on the wrong date.
- Want “first business day” instead? Walk forward from day 1 using the same skip rules.
- Pair with a Script Property “last run month” stamp if the job must never run twice in one month (manual re-runs included).
Tip: NitroGAS Co-Pilot can drop the isLastBusinessDayOfMonth guard at the top of an existing monthly job and wire the holiday list to your Config tab. Tell it the tab and range.
Happy Coding!
