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
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