This is one of my favorite scripts I’ve ever built to “hack” Google Sheets. It allows you to reference the same cell on multiple sheets in a single call, rather than calling each one individually. IMPORTANT NOTES: • As written, this script only accepts a read more
Change all of a specific color text to another specific color.
In December 2020 Google released the new IDE for Google Apps Script. Here is an overview of some cool features as it relates to Sheets scripting. • You can always revert with the “Use legacy editor” button • Triggers and Executions are in the left read more
Here I show some basics of using onEdit() triggers: onEdit() triggers are not meant to be manually run; rather, they automatically run whenever a user changes a value on the spreadsheet. An event object is typically passed to the function, typically called e, which contains read more
I have 2 related tutorials here: how to create a dependent dropdown menu with a FILTER() formula and again how to do so with a script. Sheet (to copy) Formula: =FILTER(range_to_filter,range_to_compare=cell_to_compare) =FILTER(E2:F4,E1:F1=A2) Script: function onEdit(e){ var ss = e.source.getActiveSheet(); if (ss.getName() != “Script” || e.range.columnStart read more
How to find the last row of a specific column, rather than the entire sheet. Important notes: * Make sure the variable used to store the row is declared and instantiated outside and above the FOR() loop so that it can be accessed and used read more
The simplest tutorial yet, but one that still gets frequently asked about. This is simply how to hide a row with a checkbox. The script itself is a single active line long! Sheet (to copy) Script: function onEdit(e){ if (e.range.columnStart != 8 || e.value != read more
How to automatically send an email when a condition is met. This uses an installable On Edit trigger and must be authorized. Important Notes: * This uses both .toLocaleDateString() and .toLacaleString(). The former only returns the date “11/13/2020,” the latter returns the date and time read more
When a sheet is a calendar-based list, finding today’s date can be tedious. Here, I show how to open the sheet directly to today’s date.
This has less scripting than my other tutorials, but still shows a powerful use of math and one-time needs in the script. I’m creating a sales tracker intended for a call center where hours, calls, sales, and services would be tracked for each employee on read more