Clean Cell Text
A lookup that should match "Ada Lovelace" fails because the cell is Ada\u00A0Lovelace with a non-breaking space, or has a zero-width…
136 posts tagged with “google-sheets”
Get Cell Links
getValues() gives you the visible text of a hyperlink, not the URL. getCellLinks(range) reads rich text (getRichTextValues, getRuns…
Next Round Robin
nextRoundRobin(key, people, opts) returns the next person in a rotation and remembers that choice in Script Properties. A script lock wraps…
Refresh Slides Charts
A Slides deck with charts linked to a sheet shows yesterday's numbers until something refreshes them. refreshSlidesCharts(presentation…
Only run the job on the rows you selected
Your nightly job handles everything. Then someone asks: “Can I just re-send these three?” The honest answer is usually “change Status back…
Copy Formatting From Row
New rows get values easily enough. Matching the fonts, number formats, and fill of your template row is the part people do by hand…
Rows Older Than
rowsOlderThan(rows, dateField, days, opts) returns the row objects whose date field is older than N days, measured in calendar days in the…
With Menu Cooldown
A menu item that archives rows or sends emails is dangerous when someone double-clicks it. withMenuCooldown(key, seconds, fn, opts) runs fn…
Pick Columns
You've turned rows into objects, and now you only need four of the twenty fields, in a set order, for an export, an email, or another tab…
Write Groups To Tabs
writeGroupsToTabs(ss, groups, headers) takes rows grouped by a key, like one group per owner, region, or client, and writes each group to…
One-click approve / reject for a row your ops team will actually use
Approvals in a spreadsheet usually look like this: someone types “ok” in column H, someone else types “approved!!” in column J, and nobody’s…
Batch Get Ranges
Reading ten ranges with ten getValues() calls means ten round trips. batchGetRanges(spreadsheetId, ranges) reads them all in one call to the…
Protect Row For Editors
protectRowForEditors(sheet, row, keepEmails) locks one row so only the people you list (and you) can edit it. If the row already has a…
Unmerge And Fill Down
Merged cells look tidy, but a script reading them only sees the value in the top-left cell. Every other cell in the block comes back empty…
CacheService vs Script Properties vs a Config sheet — pick the right shelf
Every Apps Script project eventually grows “settings.” The trouble isn’t storing them — it’s storing them on the wrong shelf. API tokens end…
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…
Generate a PDF from a sheet row and file it in Drive
Ops wants a PDF of each approved row, dropped in that client’s Drive folder, named so a human can find it six months later. The spreadsheet…
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…
Mirror a tab to another workbook on a schedule (without IMPORTRANGE)
The client wants “their” copy of the Orders tab. You reach for IMPORTRANGE, it works in the demo, and then: the client can’t see it because…
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…
Stamp “last successful run” where ops can actually see it
“Did the sync run last night?” is a question that shouldn’t need you. But if the only answer lives in the Apps Script Executions page, every…
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…
Keep formula columns when the script appends rows
Your import appends ten new orders. Column D used to say =C2*1.2. Now rows 42–51 say nothing — or worse, a hard-coded number from whatever…
onChange vs onEdit: catch inserts, deletes, and tab moves
onEdit is the hammer everyone reaches for. Then a teammate inserts ten rows, deletes a tab, or drags a sheet to reorder — and your sync…
Protect the sheet so people can’t wreck formulas (while the script still writes)
Someone tabs into a formula column, types over =VLOOKUP(…), and tonight’s digest is garbage. You don’t need to lock people out of the…
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…
Duplicate the spreadsheet for a throwaway dry-run of tonight’s job
Tonight’s job rewrites three tabs, refreshes pivot inputs, and emails a digest. A DRY_RUN = true flag skips the email — but it still mutates…
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…
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…
Validate Form answers before they hit your master sheet
Linked Forms feel convenient until one typo in “Email” breaks every nightly job that trusts the master tab. The fix isn’t a sterner form…
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…
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…
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…
Debounce onEdit so your sheet automation doesn’t thrash
Paste twenty status cells and watch your onEdit fan out twenty UrlFetch calls, twenty labeling passes, or twenty “send Slack” attempts. The…
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%, $…
Diff Objects By Key
“What changed since yesterday?” is a question every sheet eventually gets asked. diffObjectsByKey(before, after, key) compares two arrays of…
Import a Drive CSV into a sheet in safe chunks
Export guides get the glory; imports fail at 4am with "Exceeded maximum execution time" after writing 8,000 of 40,000 rows. Read the Drive…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
Sanitize a Sheet Tab Name
setName and insertSheet reject characters Sheets reserves (: \ / ? * [ ]), and long client names blow past the 100-character tab limit…
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…
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…
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…
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…
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…
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…
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…
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…
How to Send Drip Campaigns via Google Sheets
Drip campaigns (aka email sequences) are a powerful way to re-engage users using a linear fashion. Many email marketers use this technique…
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…
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…
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…
COMPLETE Guide to Event Triggers w/ 5 examples
In this video, we'll go over 5 examples of using Event Triggers to help you go from doing tedious work to being a SUPERCHARGED automation…
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…
Easiest Way To Upload Responses to Google Forms
We all wish that uploading responses to your Google Form was as easy as inserting new rows into the linked Google Sheet, but it's not... It…
Adding a Custom Sidebar w/ Tabs to Google Sheets
Adding custom sidebars into your Google Sheet gives you so much more flexibility with the user experience - but it only gives you 300px of…
Automate Data Pulls from the Search Console API into Sheets
Let yourself focus on other things (like sleeping in...) by replacing your manual data download from Google Search Console with this simple…
Use Google Apps Script To Add Dependent Drop Down Options
Adding Dependent/Dynamic Drop Downs to your spreadsheet can give it a HUGE boost in data quality and make it much easier to manage. In this…
How add a Calendar Picker with Data Validation in Google Sheets
Make sure to watch the video to follow along with the code: You're adding Dates WRONG If you don't DO THIS This "Simple Trigger" will fire…
I Automated Weekly Updates w/ Slack Bot
Save yourself time (and save your sanity) by automating weekly metric update messages to your co-workers. In this video, you'll learn how to…
How to Auto-send Slackbot DMs using Google Sheets
Save yourself time (and save your sanity) by automating weekly metric update messages to your co-workers. In this video, you'll learn how to…
COMPLETE Guide to Pagination for Airtable and Google Sheets
Airtable's API, by default, only allows you to pull 100 records at a time. In order to pull more than 100 records from your Airtable table…
Add Columns in BULK to Google Sheets using a Simple Script
Do you have 10+ tabs in your Google Sheet that you have to update? Instead of manually (and mind-numbingly) going through all of them and…
How to Send Messages to Slack using a Google Sheet
Google Apps Script is a powerful free Google product that lets you write scripts to customize and automate your workflows. In this video, we…
Programmatically Generate a Google Sheets Table of Contents
In this video, we'll show you how to programmatically generate a Table of Contents using Google Apps Script. We'll go over how to set up…
Getting started with the Monday.com API - Easy-Mode
Monday.com is a modern task management application that has become incredibly popular thanks to its intuitive UI and flexible integrations…
Get Your Airtable into a Google Data Studio Report in under 30 minutes
Airtable is a supercharged online spreadsheet editor that makes your spreadsheet infinitely more beautiful and usable. The only downfall is…
How to Auto Sort your Google Sheet
Welcome to Community Support - where you can get help with hurdles you're facing while bootstrapping your company or trying to find new ways…
How to Create Automated Snapshots of your Google Sheet
In this video, we'll show you how to automate snapshots of your Google Sheets and have those logs stored in a separate Google Drive folder…
How to Automate Emails Through your Google Sheet
Ever wish you could automatically notify your colleagues of important changes you've made to a Google Sheet? Or maybe notify a client when…
Easily Connect Google Sheets to Google Data Studio
Google Data Studio is a fantastic FREE Business Intelligence tool that can take your reports to the next level. It works with basically any…
How to Insert Checkboxes into Google Sheets
Sure you could just enter in "true" and "false" or "yes" and "no" into your cells, but that's not as easy to scan through like a checkbox is…
How to Get the Min or Max Value from a Dataset
Sometimes you have a huge dataset and you just want to know what the min/max values for certain range. In Excel or Google Sheets, you can…
A Guide to Creating a Basic Table of Contents in Google Sheets
In this guide, we're going to create a Table of Contents for your google sheet. This is particularly helpful when you have many different…
How to Calculate the Number Days Between Two Dates in Google Sheets
If you're trying to calculate the amount of time between two dates, there's an easy way to do that in Google Sheets via the =DATEDIF…
A Guide to Auto-Assignments in Google Sheets
In this guide, we'll look into using the `onFormSubmit(e)` installable trigger which will let us apply automation to any new tasks being…
A Guide to Checkbox Controls in Google Sheets
In this guide, we're going to create an automated way to update the tasks through the Todo list we created in our guide to creating custom…
How to Transpose Data in Google Sheets
Got some columns that you would rather be rows? Or maybe the other way around? If you've been copy & pasting each one to get it in the way…
How to Hide a Column in Google Sheets
Have some "work in progress" columns you want to hide or need to bring two columns closer together temporarily? The Hide feature in Google…
How to Combine Text Together in Google Sheets
Have a few text fields in your spreadsheet that you want to combine? Things like "First Name" and "Last Name", or maybe an address with its…
A Guide to Creating a Todo List in Google Sheets
Make sure to go through our guide to basic task management before going through this guide since there's a few things we set up in there…
A Guide to Creating Dynamic Links for Tasks in Google Sheets
After creating the Todo lists in our guide to creating todo lists, we realized that our team members still have to go back to the Tasks tab…
A Guide to Creating a Dynamic Alerts Column
The Conditions The "Time" Condition A common use case is "Time". People like to see which rows were after a certain date, before a certain…
A Guide to Generating a Table of Contents in Google Sheets
We've seen how helpful a Table of Content can be when your google sheet has many different tabs in it, but as we saw in the basic TOC guide…
A Guide to Advanced Dropdowns in Google Sheets
In this short guide, we're going to learn how to use "Ranges" to make dynamic dropdowns in Google Sheets. How will this make your…
How to use Named Ranges in Google Sheets
If you're working with lots of data, you're probably updating it regualrly. Named Ranges can help make sure that you "referenced cells" don…
A Guide to Basic Task Management with Google Sheets
In this guide, I'm going to show you how to use Google Sheets to create a task management tool that will serve you for a long time - at…
How to track time in Google Sheets
Trying to figure out how long it took to complete a task? Use =DATEDIF(start_date, end_date, time_unit) calculate the amount of days, months…
How to get the month from a date in google sheets
Have a bunch of rows that you want to group by month, but you only have the individual dates? Use =MONTH(date) to convert those dates into…
How to get the number of unique values in a dataset
Need a way to figure out how many unique values you have in a dataset? Use =COUNTUNIQUE(A:A) to get the count of unique values. Easy…