How to Add Error Alerts to Your Google Sheets Automation (Step‑by‑Step Guide)

Problem: You built an Apps Script that runs every night to pull sales data into a Google Sheet, but when the source API is down the script stops working and you never notice. Solution: Add explicit error handling that catches failures, logs details, and sends you an alert email.

Prerequisites

• A Google account with edit access to the target spreadsheet.
• Basic familiarity with the Apps Script editor (found under Extensions → Apps Script).
• Permission to send email from the script (the first run will ask for authorization).
• Optional: a secondary email address or Slack webhook for team notifications.

1. Map the Manual Workflow First

Before you automate, write down the exact steps you currently perform:

1. Open the sales dashboard in the external system.
2. Export a CSV of yesterday’s orders.
3. Copy the rows into the Google Sheet.
4. Summarise totals and send a summary email to the manager.

Identifying each step helps you know where a failure could happen – network request, file parsing, or the email send.

2. Build a Small Pilot Automation

Start with a minimal script that performs just one part of the workflow, for example fetching the CSV and writing it to the sheet. Keep the code short so you can test it quickly.

Sample code (fetch & write)

function importSales() { const url = 'https://example.com/daily-sales.csv'; const response = UrlFetchApp.fetch(url); const csv = response.getContentText(); const rows = Utilities.parseCsv(csv); const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RawData'); sheet.clearContents(); sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows); }

Run the function manually from the Apps Script UI to confirm it works. Once it does, set a time‑based trigger (e.g., every night at 02:00) via Edit → Current project's triggers.

3. Add Robust Error Handling and Alerts

Wrap the core logic in a try / catch block. In the catch section, log the error and email you (or the team). This prevents silent failures.

Enhanced script with alerts

function importSales() { const ADMIN_EMAIL = '[email protected]'; // change to your alert address try { const url = 'https://example.com/daily-sales.csv'; const response = UrlFetchApp.fetch(url, {muteHttpExceptions: true}); if (response.getResponseCode() !== 200) { throw new Error('HTTP error: ' + response.getResponseCode()); } const csv = response.getContentText(); const rows = Utilities.parseCsv(csv); const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RawData'); sheet.clearContents(); sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows); // optional: send success summary MailApp.sendEmail(ADMIN_EMAIL, 'Sales import succeeded', 'Rows imported: ' + rows.length); } catch (e) { // Log to the script's execution log console.error('Import failed: ' + e.message); // Send a concise alert email const subject = '⚠️ Sales import failed'; const body = 'The automated import encountered an error:\n' + e.stack; MailApp.sendEmail(ADMIN_EMAIL, subject, body); // Re‑throw if you want the trigger to be marked as failed throw e; } }

Key points:

• muteHttpExceptions: lets you inspect non‑200 responses instead of the script stopping silently.
• Throwing an error: forces the catch block to run, guaranteeing you get an alert.
• Success email (optional): gives you a quick health check without opening the sheet.

4. Test, Monitor, and Maintain

After deploying the alert‑enabled script, run a few manual tests:

1. Force a failure by changing the URL to an invalid domain – you should receive an alert email.
2. Restore the correct URL and run again – you should get the success email.
3. Check the Executions dashboard in Apps Script for any hidden errors.

Monitoring is cheap: keep the alert email address monitored daily for the first two weeks. Once you’re comfortable, you can switch the alert to a Slack webhook or a Google Chat space using UrlFetchApp.fetch to post a JSON payload.

Realistic Example: Daily Sales Summary for a Small Apparel Store

Imagine a boutique in Karachi that sells T‑shirts on Shopify. The owner wants a daily sheet that shows total units sold, revenue, and top‑selling SKU, then emails the summary to the manager.

Step‑by‑step:

1. Create a sheet with three tabs: RawData, Summary, and Log.
2. Write an Apps Script that pulls the Shopify orders API (requires API key). The script parses JSON, writes rows to RawData, calculates totals, writes them to Summary, and finally sends an email.
3. Wrap the whole process in the try / catch pattern shown above. Include separate alerts for API authentication errors, quota limits, and data‑parsing errors.
4. Set a nightly trigger at 01:00 (when traffic is low). The manager receives an email like: "🗓️ Daily Sales – 120 units, $3,450 revenue, Top SKU: BLUE‑TSHIRT‑M".

If Shopify’s API is down for maintenance, the script catches the 503 response, logs it, and sends a red‑flag email. The owner can then decide whether to use yesterday’s data or wait for the next successful run.

Common Mistakes & How to Fix Them

• Missing trigger authorization: The first time you add a trigger, Apps Script asks for permission. If you skip it, the script never runs. Open Triggers and click the warning icon to re‑authorize.
• Hard‑coding email addresses: Change the address in one place (use a constant at the top) so future updates don’t miss a spot.
• Ignoring quota limits: UrlFetchApp has daily limits. If you exceed them, the script throws an error. Add a back‑off retry or schedule the automation at off‑peak hours.
• Not logging enough context: Include the URL, timestamp, and response code in the error email. This makes troubleshooting faster.
• Assuming the script is always up‑to‑date: APIs change. Schedule a quarterly review of the script and update authentication tokens.

Checklist – Ready to Deploy Error‑Aware Automation

1. Document the manual steps you are automating.
2. Write a minimal script that performs one step successfully.
3. Add try / catch with detailed error messages.
4. Configure an alert email (or webhook) inside the catch block.
5. Test failure scenarios manually (wrong URL, bad credentials, quota exceeded).
6. Set a time‑based trigger and verify it runs on schedule.
7. Monitor alert inbox for the first 7‑10 days.
8. Document the script location, trigger details, and alert recipient for future team members.

Conclusion

Adding error alerts turns a fragile “fire‑and‑forget” script into a reliable assistant that tells you when something goes wrong. By following the steps above you can launch a small‑scale automation pilot, keep it under watch, and scale it confidently when the alerts prove trustworthy.

Ready to try a more complex workflow – for example, automating order fulfillment updates across Shopify and an accounting system? Discuss a practical automation pilot. Tell us the repetitive task, the input data, the expected result, and any exception cases you anticipate, and we’ll help you design a safe, alert‑enabled solution.