In This Article
- When Apps Script Is the Right Tool
- Programmatic Trigger Management
- Daily Metric Export
- Weekly Executive Summary
- CRM Sync with Business Hours Guard
- Timezone Handling
When Apps Script Is the Right Tool
Google Apps Script occupies a useful niche: it runs inside the Google ecosystem with no server provisioning, no deployment pipeline, and no infrastructure cost. For organizations whose operational data lives primarily in Google Sheets, Gmail, and Google Drive, it is the fastest path from "we need to automate this" to a running system.
The typical use cases where Apps Script outcompetes a Python ETL pipeline are: scheduled reporting that reads from Sheets and emails summaries, lightweight CRM sync between a Sheets-based pipeline and a downstream system, and triggered workflows that respond to form submissions or calendar events. For anything requiring complex data transformation at scale, a proper ETL pipeline is the right choice. But for operational automation where the data is already in Google Workspace, Apps Script often delivers in hours what a full pipeline takes days to deploy.
The Key Constraint
Apps Script triggers run in UTC by default. Any automation that should fire during business hours requires explicit timezone conversion — a detail that causes subtle failures in production when overlooked. Always set triggers using inTimezone('America/New_York') (or the appropriate zone) and validate with a test run at the edge of business hours.
Programmatic Trigger Management
Manually configured triggers break silently — they do not survive script republishing, and they accumulate duplicates when developers run setup functions multiple times. The production approach is a setupTriggers() function that deletes all existing triggers before creating new ones. Call it once after each deployment; it is idempotent by construction.
const CONFIG = {
DASHBOARD_TAB: 'Dashboard',
ARCHIVE_TAB: 'Archive',
THRESHOLDS: {
MIN_REVENUE: 10000,
MAX_VARIANCE: 0.15,
},
EXEC_EMAILS: ['cfo@company.com', 'ceo@company.com'],
};
function setupTriggers() {
// Delete all existing triggers to prevent duplicates
ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
// Daily metric export at 7 AM Eastern
ScriptApp.newTrigger('exportDailyMetrics')
.timeBased()
.atHour(7)
.everyDays(1)
.inTimezone('America/New_York')
.create();
// Weekly executive summary — Monday 6 AM Eastern
ScriptApp.newTrigger('sendWeeklyExecutiveSummary')
.timeBased()
.onWeekDay(ScriptApp.WeekDay.MONDAY)
.atHour(6)
.inTimezone('America/New_York')
.create();
// CRM sync every 2 hours
ScriptApp.newTrigger('syncCRMData')
.timeBased()
.everyHours(2)
.create();
}
Daily Metric Export
The daily export reads the current dashboard state, appends a timestamped row to the archive tab, and checks values against the defined thresholds. Threshold violations send an alert email before the function returns — so the operations team knows about out-of-range metrics by the time they open their inbox in the morning.
function exportDailyMetrics() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const dashboard = ss.getSheetByName(CONFIG.DASHBOARD_TAB);
const archive = ss.getSheetByName(CONFIG.ARCHIVE_TAB);
// Read current KPI snapshot from dashboard
const revenue = dashboard.getRange('B2').getValue();
const variance = dashboard.getRange('B3').getValue();
const timestamp = new Date();
archive.appendRow([timestamp, revenue, variance]);
if (revenue < CONFIG.THRESHOLDS.MIN_REVENUE) {
MailApp.sendEmail({
to: CONFIG.EXEC_EMAILS.join(','),
subject: `[Alert] Daily revenue below threshold:
{revenue.toFixed(0)} is below the minimum threshold of
{totalRevenue.toFixed(0)}</p>
<p><strong>Average Variance:</strong> ${(avgVariance * 100).toFixed(1)}%</p>`;
MailApp.sendEmail({
to: CONFIG.EXEC_EMAILS.join(','),
subject: `Weekly Summary — Week of ${weekData[0][0].toLocaleDateString()}`,
htmlBody: body
});
}
CRM Sync with Business Hours Guard
CRM sync triggers every two hours around the clock, but external CRM APIs often have rate limits, maintenance windows, or simply should not be hit during off-hours when the data they supply is stale. A business hours guard — checking that the current time in the business's timezone falls within weekday working hours before executing the sync — prevents unnecessary API calls and avoids waking anyone up with an off-hours failure alert.
function syncCRMData() {
// Business hours guard: weekdays 8 AM – 6 PM Eastern only
const now = new Date();
const eastern = Utilities.formatDate(now, 'America/New_York', 'HH');
const hour = parseInt(eastern);
const day = now.getDay(); // 0=Sun, 6=Sat
if (day === 0 || day === 6 || hour < 8 || hour >= 18) {
return; // outside business hours
}
// Perform CRM API call and update Sheets
const response = UrlFetchApp.fetch('https://api.your-crm.com/deals', {
method: 'GET',
headers: { 'Authorization': 'Bearer ' + PropertiesService.getScriptProperties().getProperty('CRM_TOKEN') }
});
const deals = JSON.parse(response.getContentText());
// ... write deals to CRM tab ...
}
Timezone Handling
Apps Script's built-in new Date() returns UTC. When business logic depends on local time — business hours guards, day-of-week checks, daily report timing — use Utilities.formatDate(date, timezone, format) to convert explicitly. Store the timezone string in the CONFIG object so it appears in exactly one place and changing it for a client in a different region is a one-line update.
This post was originally published on White Oak Intelligence. Read the full article there for formatted diagrams, code examples, and related content.
Top comments (0)