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…
85 posts tagged with “google-sheets”
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…