Sanitize a Sheet Tab Name
setName and insertSheet reject characters Sheets reserves (: \ / ? * [ ]), and long client names blow past the 100-character tab limit…
22 posts tagged with “SpreadsheetApp”
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…