Changing the value in place based on a leading operator. Important Notes: * Since ‘+’ is a legal operator to start a formula, Sheets automatically converts it to ‘=’ * Using .splice() on e.value essentially forces it to be considered a String. Multiply it by read more
Here I walk through how to copy a range that contains both values and formulas and keep both working. This includes showing issues along the way and why different approaches don’t work. Important notes: • .getFormulas() gets the entire range with only the formulas populated read more
How to move data from one spreadsheet to an entirely separate spreadsheet via Apps Script. Important Notes: * As with all on edit scripts, this script is not meant to be run manually but runs automatically from the trigger. * This must be done with read more
This can be super useful: How to create a PDF in your Google Drive of a specific sheet. Important notes: • The token is necessary ie var token = ScriptApp.getOAuthToken(); • All the parameters can be changed to suit your needs Sheet (to copy) https://docs.google.com/spreadsheets/… Script: read more
How to automatically place a static date, datetime, or timestamp with an edit. Important note: • I have included all 3 scripts in the editor. Uncomment out the one you want, as they cannot all work simultaneously. Sheet (to copy): https://docs.google.com/spreadsheets/d/1_9Q_bP79nGnETz25q-8J1NrIxxd2muqqz8ej3fCjDhw/edit#gid=0 Scripts: /* Sheet3 multiple read more
How to QUERY() with a column for the sheetname, or sheetnames from multiple sheets. Important note: * In the custom array {}, use commas to separate columns and semicolons to separate rows Sheet (to copy): https://docs.google.com/spreadsheets/d/1Q0kAwUQkj9i3M3qJMUX9_Fwgkaeo7cc26TRhW0irnIo/edit#gid=1970274019 Formula: =QUERY({ ARRAYFORMULA(IF(LEN(‘Location 1′!A2:A),”Location 1″,)),’Location 1’!A2:B; ARRAYFORMULA(IF(LEN(‘Location 2′!A2:A),”Location 2″,)),’Location read more
This is moving rows between several sets of sheets, rather than just 1 sheet to another. For a larger set of sheets, whether that be more elements in the set or more nodes on a path, I would use an array approach, rather than just read more
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