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…
60 posts tagged with “code-snippet”
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…
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…
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…
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…
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…
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…
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…
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…
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…
Filter Out Rows Using Regex
Using Regex Using Regular Expressions allows you to analyze a value and find a match according to some logic. It's a pretty vast topic, so I…
Filter Out Blank Rows
What you'll need Before you can filter anything, you'll need a data set to filter by. More specifically, you'll need a data Array. An array…
Find The Difference Between Two Arrays
Why would you need a find the difference between two Arrays? When you're working with multiple data sets, you might run into a situation…
Get Unique Values from Array
What is a Unique Array? Just as straight forward as it sounds, it's an Array where all of the values within it are unique - meaning, there…
Converting JSON into a flat Array
This code snippet helps you convert an object into a flat array that you can use to import into a Google Sheet. Important Note: This snippet…
Converting an Object Array into a flat Array
This code snippet helps you convert an array of objects into a flat array that you can use to import into a Google Sheet. This is pretty…
Converting an Array into an Object Array
This snippet helps you convert an array into an object array. Converting an array into an object array is useful when you need to export…
Basic REST API Request Call Using XML
This is a basic REST API call that parses out XML data and returns it back to you. To do a POST request using XML data, check out the Basic…
Basic REST API POST Request Using XML
This how-to guide will cover making a POST request to an API using XML data. For retrieving XML data from an API and parsing it out, check…
Basic REST API POST Request Using JSON
This how-to guide will cover making a POST request to an API using JSON data. For retrieving JSON data from an API and parsing it out, check…
Auto-Sorting a Specific Google Sheet Range
Sorting your Google Sheet using Apps Script is very simple. Firstly, you'll need to decide how you want to sort your data - either by the…
How To Get All Sheets From A Google Sheet Using Apps Script
The first thing you'll need to do is set your spreadsheet as a variable. In the below example, we use the openByUrl() method to do this, but…
Basic REST API GET Call Using JSON
This is a basic REST API call that parses out JSON data and returns it back to you. To do a POST request using JSON data, check out the…
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…
Get Data from a Sheet
There are a few different ways to get all of the data from a specific sheet. The fastest way is to use the .getDataRange() method. The only…
Sending Emails Using Apps Script
The built-in libraries in Google Apps Script makes sending emails extremely easy. By default, you have two libraries to choose from: MailApp…
Creating Backup Files of a Google Sheet
There's a few different reasons for creating data snapshots of your Google Sheet. One of them is just to make sure you have a copy of the…
Create a Custom Menu Option
The onOpen trigger is mainly used to add in additional menu options that can help you execute scripts without having to open up your Script…
Auto-Sorting Your Google Sheet
Sorting your Google Sheet using Apps Script is very simple. Firstly, you'll need to decide how you want to sort your data - either by the…