Export last night’s filtered rows to a dated CSV in Drive
Ops ask we hear a lot: after the nightly sync, dump “status = done” (or last 24h) rows into Exports/2026-09-18.csv without opening Sheets…
104 posts tagged with “google-apps-script”
Get Unprocessed Threads
Your nightly job pulls vendor receipts out of Gmail into a sheet. Run it twice and you get every receipt twice. getUnprocessedThreads(query…
Is Last Business Day Of Month
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…
Post To Chat Webhook
You want the nightly job to say “done, 42 rows” (or “failed”) in the team’s Google Chat space instead of somebody’s inbox. Incoming webhooks…
Point your script at staging so you never write prod by accident
Dry Run previews writes. This guide is the other half of safety: which spreadsheet those writes would hit. Keep a Script Property pointer…
Cancel Flag
A teammate kicked off “Process all rows” from the menu. Two minutes in they realize the filter was wrong. There is no Stop button — only…
Escape Leading Formula
You imported a CSV of support notes. One row started with =HYPERLINK(...) (or just =1+1, or a phone number typed as +15551212). Sheets…
Under Time Budget
Apps Script kills a execution around the 6-minute wall. If your loop is still mid-UrlFetch when that happens, you lose the chance to write a…
“Execute as me” vs “as the user” — pick the web app identity that won’t haunt you
You deploy a doGet / doPost web app, share the URL, and suddenly writes land under the wrong account — or the script can’t see the sheet at…
Assert Required Keys
The API call failed because row 37 had no email. Worse, it succeeded with an empty amount. assertRequiredKeys(obj, requiredKeys, opts…
Script Write Guard
Your nightly job rewrites 400 rows by row number. Meanwhile someone edits a cell, your installable onEdit auto-sorts the sheet, and the job…
Set Dropdown From List
Status columns full of “approved”, “Approved ”, and “APPROVED!!” break every script that reads them. setDropdownFromList(targetRange, source…
Build a tiny HtmlService sidebar your teammates will actually click
Non-technical teammates will not open Apps Script. They will click a menu item that opens a sidebar with one obvious button and a status…
Create Calendar Event If Missing
“Create a calendar event for each booking row” is easy. Running it twice and getting two events per booking is just as easy…
Flush Then Get Values
You write inputs, then read a formula cell that depends on them, and get the old answer. Apps Script batches writes, so the read can run…
Share File With Email
Sharing from a script is one line, until the job runs twice and the client gets two “shared with you” emails. shareFileWithEmail…
Find and flag duplicate rows before you push upstream
Inbound imports often get an idempotency story ("re-run without duplicating"). Outbound pushes are the opposite footgun: two sheet rows with…
Create One Shot Trigger At
Some jobs should run once: send the reminder Thursday at 9, close the form at midnight, re-check the invoice in two hours…
Get Visible Row Objects
Ops filter the sheet to “Status = Ready, Region = East” and click your menu item. They expect it to act on what they can see. getValues…
List Drive Files Modified Since
“What landed in the Inbox folder since last run?” is the start of half the Drive automations we write. listDriveFilesModifiedSince…
Pause a nightly job without deleting the trigger
Deleting a time-driven trigger to "turn off" a nightly job feels clean until Monday when nobody remembers the exact hour, timezone, or…
Filter Objects By Date Window
“Rows from the last 7 days” sounds trivial until a row stamped at 11:30pm in New York shows up as tomorrow because something compared…
Get Selected Row Objects
getActiveRowAsObject is perfect for one row. Ops rarely stop at one. getSelectedRowObjects(sheet, headerRow) returns a header-keyed object…
Move File To Folder
Form uploads land in one big “(File responses)” folder. Exports land in My Drive root. moveFileToFolder(fileId, folderOrId) puts a Drive…
Give non-technical teammates errors they can act on
Your script threw TypeError: Cannot read properties of undefined (reading '0') and the account manager Slack'd "is it broken?" That's on the…
Fill Html Template
Building approval emails and HtmlService notices by concatenating strings is how < in a client name becomes a broken page — or worse…
Group Objects By Key
You have 200 rows and need one email (or one Drive folder, or one export) per client. Nested loops and “remember the last name” logic get…
Is Within Business Hours
A time-driven trigger that fires at 3am and emails a client is a support ticket waiting to happen. isWithinBusinessHours(startHHmm, endHHmm…
Simple triggers vs installable triggers (when onEdit lies to you)
You wrote a perfect onEdit that emails the team when Status flips to Done — and nothing happens. Or worse: it works when you edit, then…
Copy Values To External Sheet
IMPORTRANGE is great until the client workbook needs a static copy, the source sheet has to stay private, or the formula starts showing #REF…
Stamp Last Successful Run
“Did the nightly job run?” shouldn’t mean opening Executions. stampLastSuccessfulRun(target, status, opts) writes a spreadsheet-timezone…
Write Block Leaving Formula Cols
appendRow and one wide setValues write every column — including the ones holding =VLOOKUP(…) or =IF(…). One run and your formula columns are…
Treat a sheet like a job queue (claim → work → done)
Spreadsheets make terrible message buses — until you need a visible, editable backlog that non-engineers can triage. A Jobs tab with pending…
Mask Secret
Debugging “is the API key loaded?” should not paste Script Property values into a shared Log sheet. maskSecret(secret, keepStart, keepEnd…
Truncate For Cell
Sheets cells top out around 50k characters. Dump a full err.stack, UrlFetch body, or JSON blob with setValue and the run throws mid-flight…
Open By URL Or Id
Paste a whole browser URL into SpreadsheetApp.openById and the run dies. openByUrlOrId(urlOrId) accepts a Spreadsheet URL or raw id…
Brief NitroGAS Co-Pilot so you get working Apps Script
Co-Pilot is optional. The NitroGAS extension stays free forever for snippets and the editor workflow. When you do use Co-Pilot, the quality…
HTML Table From Objects
Digest emails beat screenshots of a sheet. htmlTableFromObjects(rows, columns) turns an array of objects into a simple escaped <table> you…
Hash String SHA-256
Cache keys and dedupe fingerprints should not log raw emails or IDs. hashStringSha256(text) runs Utilities.computeDigest(SHA_256, …, UTF_…
Format in Spreadsheet Timezone
Log stamps and export filenames that disagree with the sheet’s timezone are a classic bootstrapper footgun. formatInSpreadsheetTimezone(date…
Clone a template sheet for every new client
Freelancer / ops pain: you keep copying last month's tab and inheriting junk — stale sample rows, half-edited formulas, a filter that still…
Safe JSON Parse
JSON.parse throws on garbage — and webhooks, Script Properties, and CacheService strings go garbage more often than we’d like. safeJsonParse…
Debounce Or Skip
onEdit fires for every cell change — paste 40 rows and your automation thrashes. debounceOrSkip uses CacheService as a quiet window: if a…
Read / Write a Job Checkpoint
Long jobs hit the ~6-minute wall mid-batch. A checkpoint in Script Properties — { key, cursor, updatedAt } — lets the next timed trigger…
Email yourself when the nightly job fails
You've got a time-driven sync that "mostly works" until it doesn't — and you find out three days later from a confused teammate. Triggers…
Get UI Safe
SpreadsheetApp.getUi() only exists when a user has the spreadsheet open. Time-driven and many installable triggers are headless — calling…
Parse CSV Text
Utilities.parseCsv is fine for tidy files. Real Drive drops include quoted commas, embedded newlines, and "" escapes. parseCsvText turns a…
Try Lock Or Skip
Time-driven triggers and overlapping edits collide. withScriptLock waits then throws if it can't acquire — great when failure must be loud…
Map columns by header, not by letter
Column C is a rumor. The moment a client inserts "Middle Name" between A and B, every hard-coded letter in your script writes into the wrong…
JSON OK / ERR helpers for Web Apps
doGet / doPost handlers stay readable when every success and failure returns the same JSON shape. jsonOk / jsonErr wrap ContentService so…
Write Values as a CSV File in Drive
After a nightly sync you often want a dated CSV in Drive without opening Sheets. Build the 2D array, turn it into CSV (reuse valuesToCsv…
Require a Script Property (fail loud)
Silent null from Script Properties is how staging pointers and API bases "work" until they write nowhere. requireScriptProperty throws with…
Add a Dry Run switch before you write
The scariest line in a bootstrapper is the first setValues against a live client tab. A Dry Run switch lets you rehearse the import: same…
Escape HTML for HtmlService
Stuffing sheet values straight into HtmlService HTML is how < in a client name breaks the sidebar — or worse, opens an XSS footgun…
Build a Query String for UrlFetch
Hand-building ?a=1&b=2 is how spaces and ampersands sneak into broken UrlFetch GETs. buildQueryString walks a plain object, skips null/empty…
Normalize an Email Address
Upserts and sheet lookups miss rows when one person typed Ada@Example.com and another stored ada@example.com. normalizeEmail trims…
Setup your triggers once (no doppelgängers)
Re-running "Install triggers" should be safe. In too many client workbooks it isn't — every Setup click stacks another time-driven job, and…
Sanitize a Sheet Tab Name
setName and insertSheet reject characters Sheets reserves (: \ / ? * [ ]), and long client names blow past the 100-character tab limit…
Extract a Drive File ID from a URL
Operators paste Share links, "Open in new tab" URLs, and bare ids into the same column. extractDriveFileId normalizes that mess into a Drive…
Claim the Next Queue Row
A sheet full of pending jobs will double-process the moment two triggers overlap. claimNextQueueRow finds the first matching status under a…
Archive rows, don’t delete them
Delete feels decisive until a client asks where order 1842 went. Soft-delete flags help; a dedicated Archive tab is clearer for operators…
Get the Active Row as an Object
Menu actions and sidebar handoffs usually care about the row you're on, not the whole grid. getActiveRowAsObject reads the header row plus…
Copy a Template Sheet Tab
Copying last month's client tab inherits junk formulas, stale sample rows, and mystery filters. Keep a clean template tab, then…
Email Yourself an Error Alert
Nightly jobs that "mostly work" fail quietly until a teammate notices bad data. emailErrorAlert sends you a plain Gmail with the error…
Budget Apps Script quotas like a grown-up
Apps Script quotas aren't a surprise if you treat them like a budget. UrlFetch calls, email sends, spreadsheet reads, and total runtime all…
Get or Create a Nested Drive Folder Path
One folder name isn't enough when exports land under Exports/2026/09 or Clients/Acme/Incoming. Walk a slash-separated path from a parent…
Confirm a Dangerous Action (Yes/No)
Custom menus that Clear, Re-import, or Archive should not fire on a misclick. This thin wrapper around SpreadsheetApp.getUi().alert returns…
Build a Header → Column Index Map
Hard-coding column letters (C, D) breaks the moment a client inserts a column. Build a { headerName: columnIndex } map once from row 1, then…
Re-run your import without duplicating rows
Imports fail halfway. APIs page slowly. Someone clicks the menu twice. If "run again" means double every row, you don't have an importer…
Move a Row to Another Sheet
"Delete" is usually the wrong word. Archive the row to another tab (or workbook tab), then remove it from the working sheet — so audits…
Ensure Header Row Exists
Importers and upserts assume row 1 is headers. Empty tabs and "someone deleted column C" both break that. This helper creates the header row…
withScriptLock Helper
Standalone projects and library code don't get a Document Lock. When the critical section is the script project itself — watermark updates…
A Log sheet you’ll actually read later
Logger.log is fine until you need yesterday's failure and the Executions page shrugged. A tiny Log sheet — timestamp, level, message…
Catch a webhook in Apps Script without losing the payload
Webhooks don't retry politely when your script throws mid-flight. A solid Apps Script receiver does four boring things well: accept doPost…
Send an HTML Email with GmailApp
GmailApp.sendEmail can send HTML — you just need htmlBody (and a plain-text fallback for picky clients). This helper wraps the options bag…
Upsert a Row by Key Column
Find a row by a key column (email, order ID, SKU) and update it — or append if it's new. Header-aware, so you pass a plain object instead of…
Get the Last Data Row in a Column
getLastRow() happily counts formatting, old formulas, and phantom blanks. When you need the real last cell with data in a column — for…
Custom functions that don’t thrash your sheet
Custom functions look like spreadsheet superpowers until one volatile formula starts refetching an API on every edit. Use them for pure…
withUserLock Helper
Document locks serialize everyone on a spreadsheet. User locks only serialize the same user's overlapping runs — handy for personal quotas…
Convert Sheet Values to CSV Text
Need a CSV string for email attachments, Drive dumps, or API uploads? Escape commas/quotes/newlines correctly, then join rows. These two…
HtmlService Template Include Helper
Splitting HTML dialogs into Dialog.html + Styles.html + Client.js.html keeps IFRAME UIs maintainable. The classic pattern is a server-side…
Show a Sheet Toast for Progress
Long-running menu actions feel broken without feedback. SpreadsheetApp.toast pops a small notice in the lower-right — perfect for "started…
Get or Create a Drive Folder by Name
Exports, backups, and client drop folders all need a stable place in Drive. This returns the first child folder with that name under a…
Delete Triggers by Handler Name
Re-running an install function without cleanup creates duplicate triggers — and suddenly your sync fires five times. This deletes every…
Create a Daily Time-Driven Trigger
Time-driven triggers are how most overnight syncs stay hands-off. This helper creates a once-per-day trigger for a named function around the…
Cache JSON with a TTL
Hitting the same API or expensive sheet scan every trigger run burns quota. CacheService gives you a short-lived store (max ~6 hours…
Store and Load JSON in Script Properties
Script Properties are great for small config blobs — tokens, last-run watermarks, feature flags. These helpers wrap JSON.stringify / JSON…
Append an Object as a Sheet Row
When your data is already a plain object (API payload, form values), aligning it to row-1 headers beats hand-building arrays. Missing keys…
Clear a Sheet but Keep Header Row
Wiping a sheet before a fresh import is common — but you usually want the header row to survive. This clears content below the header(s…
Get or Create a Sheet by Name
Need a tab that might not exist yet? This helper returns the sheet if it's there, or inserts it and returns the new one — no null checks…
rowsToObjects and objectsToRows
Sheet data is easiest as a 2D array. Business logic is easier as objects keyed by header. These two helpers convert back and forth without…
withDocumentLock Helper
When a time-driven trigger and a manual run can overlap — or two editors kick the same sync — you need a lock. This helper wraps LockService…
Retry UrlFetch with Exponential Backoff
Flaky APIs and 429s are part of bootstrapper life. This helper wraps UrlFetchApp.fetch with exponential backoff so a single blip doesn't…
Timezone-safe dates in Sheets and Apps Script
new Date() in Apps Script, a date cell in Sheets, and a teammate in another city will happily disagree by a day — and you'll spend an…
Script Properties for bootstrapper config
Hardcoding an API key in the script editor feels fine… until you share the project, clone it for another client, or paste the same file into…
Batch writes without timeouts
When a script dies halfway through writing thousands of rows, you don't need a fancier API — you need a boring pattern: lock, chunk…
For Loop Using Dynamic Set
What Does This Code Snippet Do? Unlike the incremental For Loop where you specify the number of iterations you want the loop to execute…
For Loop Using Increments
What Does This Code Snippet Do? This code snippet is a basic For Loop that takes in a set iteration length and also lets you specify where…
Find the Max Length of Nested Arrays
What Does This Code Snippet Do? This findMaxLengthOfNestedArrays() code snippet takes an array of arrays (aka nested arrays) and calculates…
Find Column Number By Column Header Name
What Does This Code Snippet Do? This getColumnByName() code snippet is a fast and efficient way to quickly identify the Column Index of a…
Lookup The Matching Row Index Using a Search Term
This snippet is very helpful when you need to make updates to a specific row in a Google sheet and have an identifier that you can use to…
Converting Columns between a Number or a Letter
When you're scripting in Google Apps Script to automate data in a Google Sheet, you'll be working with Columns a lot. Sometimes you'll get…
Making a Word Game w/ Google Sheets and Apps Script
There's a Word Game that's gone viral and tons of clones are coming out for it - even ones using Google Sheets! But... they're only using…
How to Auto-Send Emails on a Google Form Submission
Are you using Google Forms and wish you could send auto-replies to your form responders? Better yet, how about personalized auto-reply…