144 posts tagged with “code-snippet”

Close Form At Limit

Google Forms can't stop at 50 sign-ups or at Friday 5pm on its own. closeFormAtLimit(form, {maxResponses, closeAt, message}) checks both and…

Get Sheet By Gid

A sheet URL ends in #gid=123456789. That number points at one tab and stays the same when someone renames it. getSheetByGid(ss, gid) finds…

Parse Loose Date

Imported sheets are full of dates that are really text: 03/04/2026, 4 Mar 2026, March 4th, 2026, 2026-03-04. parseLooseDate(value, {dayFirst…

Get Latest Csv Attachment

A vendor or report tool emails you a CSV every morning. getLatestCsvAttachment(query) searches Gmail, picks the newest .csv attachment from…

Notify Once

An hourly trigger checks a condition and emails you when it is true. Without a guard you get the same email every hour until someone fixes…

Sum By Key

Total quantity per SKU, spend per vendor, hours per client. sumByKey(values, keyIndex, valueIndex) takes a 2D array with a header row and…

Get Daily Rate

Invoices in EUR, reports in USD. getDailyRate(from, to, date) returns the exchange rate for a given day, or the latest one if you leave out…

Merge By Key Newest Wins

Two lists of the same people, both partly out of date. mergeByKeyNewestWins(listA, listB, keyField, dateField) merges row objects by a key…

Split Multi Select To Rows

Google Forms stores checkbox answers in one cell: Sheets, Gmail, Docs. Pivot tables and COUNTIF can't count that. splitMultiSelectToRows…

Geocode Cached

Geocoding a column of addresses one row at a time burns quota and time, and the same office address shows up forty times. geocodeCached…

List Calendar Event Rows

You want a week of calendar events on a sheet: one row per meeting, with the duration as a number you can sum. listCalendarEventRows…

Translate Cells

A column of product names or survey answers needs a Spanish copy next to it. translateCells(range, targetLang, opts) reads the range once…

Build Prefilled Form Url

A Google Form with fields you already know from a sheet row — name, email, client id — and a recipient who shouldn't have to retype them…

Business Hours Between

Calendar hours lie about SLAs. A ticket opened Friday at 4pm and closed Monday at 10am looks like 66 hours and feels like two…

Normalize Phone

Sheet phones arrive as (555) 123-4567, 555-123-4567, +1 555 123 4567, or 0071…. APIs and SMS gateways want E.164. normalizePhone(raw…

Add Business Days

“Due in three business days” is not date + 3. Weekends and company holidays get in the way. addBusinessDays(date, n, holidays) moves a date…

Create Drafts From Rows

A sheet of recipients and a template, and a human who still wants to read every email before it goes out. createDraftsFromRows(rows, buildFn…

Next Sequence Id

ROW() changes when someone inserts a row. A random id can collide. nextSequenceId(prefix) takes the script lock, bumps a counter in Script…

Fill Doc Template

A Doc full of {{Client}} and {{Amount}} placeholders, a sheet row with those headers, and a Drive folder for the finished copies…

Rename Files From Rows

A sheet with a file id column and a new-name column, and a pile of Drive files that still say IMG_4021 or Copy of Proposal…

Time Step

A 400-line main() that "just feels slow," and no idea whether the fetch, the sheet write, or the PDF export is the culprit. timeStep(name…

Combine Tabs With Source

Twelve rep tabs, one per person, and a manager who wants one list. combineTabsWithSource(ss, pattern, opts) reads every sheet whose name…

Formula Inventory For Prompt

A sheet with 4,000 formula cells usually has about six distinct formulas, copied down. formulaInventoryForPrompt(range) groups cells by…

Write As Plain Text

Write 00123 to a cell and Sheets stores 123. Write a 16-digit order ID and you get 1.23457E+15, and the last digits are gone for good…

Find Error Cells

A #REF! on tab 9 can sit there for weeks until someone presents the dashboard. findErrorCells(sheetOrSpreadsheet) scans one tab or every tab…

Set Highlight Rule

Run a “highlight overdue rows” script three times and you get three identical conditional-format rules, then thirty. setHighlightRule(range…

Stamp Edit Timestamps

“When was this row added, and when did someone last touch it?” stampEditTimestamps(e, opts) answers both from an edit trigger. It fills…

Redact Rows For Prompt

Co-Pilot writes much better code when it sees a real-looking sample row. Real-looking is the point: it shouldn't be a real customer…

Set Dependent Dropdown

Pick Category = Hardware in column B and column C should only offer Laptop, Monitor, Dock. Change B to Software and C should switch to…

Split Text Under Limit

Chat messages, SMS gateways, cell values and API fields all have a character limit, and a nightly summary will eventually blow past it…

Send Digest Per Owner

One big “open items” email to the whole team gets ignored. One short email per person, listing only their rows, gets read…

Snapshot Range To History

Formulas only ever show today's number. The MRR cell said 4,200 last month and 4,650 now, but nobody wrote 4,200 down…

Trigger Inventory For Prompt

Co-Pilot can't see which triggers a project has, so it happily adds a second nightly trigger next to the first, or edits a function without…

Append Status Audit Row

“Who moved this deal to Lost, and when?” Version history can answer that, slowly. appendStatusAuditRow(ss, entry) writes one line to an…

Archive Rows Before Date

The orders sheet has 40,000 rows and every script that reads it is slow. archiveRowsBeforeDate(sheet, dateHeader, cutoff, opts) moves every…

Script Property Keys For Prompt

Ask Co-Pilot for a script that calls your CRM and it will invent CRM_KEY when you already have HUBSPOT_TOKEN in Script Properties…

Get Display Row Objects

getValues() gives you what's stored in the cell: a Date object, 0.125, 4500. Sometimes you want what the cell shows: Dec 18, 2026, 12.5%, $…

Install Menu Safely

Someone renames exportReport to exportMonthlyReport, the menu still points at the old name, and every click says “Script function not found…

Snippet Catalog For Prompt

Your project already has rowsToObjects, withScriptLock and retryUrlFetch. Ask Co-Pilot for a new job and it writes its own versions of all…

Diff Objects By Key

“What changed since yesterday?” is a question every sheet eventually gets asked. diffObjectsByKey(before, after, key) compares two arrays of…

Fetch All Pages

Most APIs hand you records 100 at a time and a cursor for the next batch. fetchAllPages(fetchPage, opts) follows that cursor for you. You…

Tiny Test Runner

Apps Script has no built-in test framework, and you don't need one to catch the obvious stuff. runTests(tests) runs an object of named test…

Append Dead Letter Row

Some rows just won't go through: a bad email, an API that keeps saying 422, a customer ID that doesn't exist. Retrying them forever clogs…

Require Shared Secret

A public /exec URL is easy to leak, so your doPost shouldn't trust whoever calls it. requireSharedSecret(e, propertyKey) reads the expected…

Sheet Schema For Prompt

Ask any assistant to write a script for “the Orders sheet” and it will happily invent a Customer Email column you don't have…

Active User Email Or

Session.getActiveUser().getEmail() returns an empty string more often than you'd think: in installable triggers, in “execute as me” web apps…

Health Check Payload

When a client asks “is the thing still working?”, you want a URL you can open (or point an uptime monitor at) that answers in one line…

Send Email With Budget

A nightly job that emails 300 people works fine until the day it doesn't, and then it dies halfway through with “Service invoked too many…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

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…

Browse all tags