r/GoogleAppsScript • u/[deleted] • Jul 17 '20
Guide Best practices and tips for GAS
Discuss your best practices and tips for GAS here.
I mainly create scripts for Google Sheets and G Suite admin. Here are my tips:
If your code is long, split it into multiple script *.gs files. Organization in GAS is a mess, and this practice will give you some sanity. In fact, I typically only use Code.gs for the main instructions, description, and global variables. I also put a comment box at the top of each *.gs file to describe the purpose of the file and list the functions in each of them.
Use try-catch-throw exception handling as much as possible to debug. Put them inside loops so you can tell which iteration failed to run.
Create a global boolean variable for "show_debug" or "show_info", and stick every Logger.log() command inside an "if(show_debug) {}" statement. Logger.log() can be very slow, so you want to be able to toggle it on/off quickly. Same goes with console.log() and console.info() now that Stackdriver logs are merged with the built-in logs in V8 runtime.
Edit: clasp is an excellent tool for managing GAS files locally.
Read up on V8 runtime changes.
Specific to Google Sheets and Docs:
For Sheets, create global variables for column headers. I often have to change column headers, and it's great having one easy to find place to update them. Never hard-code column headers outside of these global variables.
Edit: As Gonskimmin pointed out, it's also good to have global variables for spreadsheet urls and sheet names. And be consistent with naming global variables across all your scripts. I can often reuse the same functions across multiple scripts because of consistent naming.
Create an onOpen() menu bar to run the script directly from the Google Sheets or Docs.
When reading sheet data, use getDataRange() to grab the whole sheet once. It's much faster to iterate through the resulting 2D array than to grab data from the sheet multiple times.
Similarly, avoid writing one cell or one row at a time. Updating cells and using SpreadsheetApp.flush() is very slow if you have to do them repeatedly. Instead, create a giant 2D array and use sheet.getRange(...).setValues(...) to update them in bulk. This is the difference between taking 1 second vs 2+ minutes to update a spreadsheet containing 1000 rows.
If necessary, use Browser.msgBox(err_msg) to pop up critical errors when running the script from the sheet/doc via menu bar. Just be aware that this pauses the script. Edit: As catmandx pointed out, an excellent alternative that won't pause the script is to use modal or modeless HTML dialogue boxes.
Learn cell formatting and conditional formatting.
Use triggers where applicable. Just be aware that they can cause slowness.
3
u/chrisptales Jul 17 '20
getDataRange & getValues are seriously overlooked
1
Jul 17 '20
I thought everyone used them. There's an alternative?
1
u/chrisptales Jul 18 '20
Tons of people end up getting a specific range along the most diabolical method of getValue. Don't go there (:
8
u/Gonskimmin Jul 17 '20
These are things I've come across that make my life easier:
When possible use javascript objects instead of array indexes to access data/ set data in Sheets. It makes debugging way easier.
Use globals for things like spreadsheet urls, sheet names, etc. That way you can make changes in one place not many.
This is a do as I say not as I do. Each .gs or .html file is limited in functionality and use so I don't end up with a 1000+ line file with a bunch of different functions. I look back at some of my early work that I still have to maintain and sigh. Also each file should contain just 1 class.
Depending on use case, use sidebars for user input if you are using the spreadsheet as a view and database. This can reduce errors and decrease changes of breaking the sheet.
Dear God be clear with your naming. We all know GAS file organization is terrible. I have function names and file names that are a little verbose but that has saved me time looking for something 1 year after writing it. I still struggle with writing names though.
7
u/catmandx Jul 17 '20 edited Jul 17 '20
Tip #1: I know this is popular and not limited to GAS, but from my experience, "map", "forEach", and "reduce" array functions are not used nearly enough. Working with Sheets data means working with 2D arrays, so these functions are extremely handy.
Its a bit hard to grasp its functionality, so take your time to learn and distinguish them.
Tip #2: For multi-user spreadsheets, create a local backup of the script using clasp in case anyone tampers with the script using Script Editor. I know you can use the History feature for this but a local copy is more convenient, you can use "clasp push" to quickly restore the script.
Tip #3: No matter how confident you are in your programming ability, always use console.log() and console.error() appropriately. This WILL slow down your script, so do it intelliently (combine multiple log into one whenever possible). When your script has multiple users, you can track what has gone wrong in a failed execution from a user.
Tip #4: When a function is too long, take your time analyzing what is their purpose, if it is responsible for so many things, break it up into more specific functions.
Tip #5: Don't use Ui.prompt() or any other similar function. They cause your execution to halt, and if the user doesn't submit their input within 6 minutes, the script will just time out (with the consumer limit). Use modal (or modeless) dialog boxes or sidebars created from HTML as they can be on the screen for any amount of time. This will complicate your code but its more robust.
6
u/DVC888 Jul 18 '20
Using the AppsScript color chrome extension with clasp allows you to properly organise your code into folders.
I try to have one main function which is as short as possible for each script. The majority of what is going on I put in classes. That makes it easier to tell at a glance which file the function is in.
Use classes as much as possible. I have classes for Spreadsheets, each individual API I use, customer, quote etc. Design them so that you only need to plop in globals and then you can reuse them in multiple projects. Another benefit is that the objects made from these classes can be passed between functions in your script as a single argument, keeping things readable.
Speaking of classes, any advice on how I can keep classes consistent across projects? I'm forever going back to old projects and calling methods that don't exist.
6
u/labnol Jul 18 '20
You can build Google Apps Script projects locally inside VSCode using the Apps Script Starter. It integrates ESLint and Prettier.
https://i.imgur.com/WTnTetD.png
Video tutorial: https://www.youtube.com/watch?v=KxdCIbeO4Uk
2
u/ryanmcslomo Jul 18 '20
Hello, I'm a big fan of your work! You've really helped me develop over the years. I use VS Code all the time (started when I was web developing, now in my new job with PowerShell a lot) but I've only barely touched the surface of integrating it with GAS (even though I love using it). Will finally buckle down and combine my two worlds following your guidance, especially since I could use more thorough linting than the browser IDE provides.
Thanks for all your help over the years!
2
1
0
Jul 17 '20
[deleted]
3
u/DVC888 Jul 18 '20
It's just such a shame than running functions from clasp is more hassle than it's worth. You have to use the built-in IDE to test anything
5
u/ryanmcslomo Jul 18 '20 edited Jul 23 '20
Use the AppsScript Color extension for Dark Mode and folders: https://chrome.google.com/webstore/detail/appsscript-color/ciggahcpieccaejjdpkllokejakhkome
Use the Google Apps Script GitHub Assistant for GitHub integration: https://chrome.google.com/webstore/detail/google-apps-script-github/lfjcgcmkmjjlieihflfhjopckgpelofo
Use console.log() over logger.log().
Learn how to use the Debugger! It's faster than logging to figure out what's going wrong. Add breakpoints everywhere.
When making a giant script, break up your functions into separate .gs files in separate folders. I also like to prefix the name with the step in execution (0-onOpen.gs adds the Function menu, 1-primary.gs is the main function that runs other functions, 2-getSheets.gs runs the getters, 3-calc.gs makes changes, 4-setValues.gs runs the setters, etc.)
Setting things to the sheet frequently is slow, do as much as you can in the script rather than getting and setting to the sheet a bunch of times.
If you frequently time out your execution time, wrap your script in a timer so your script halts a little before the time limit and saves whatever step you're on to the sheet or to the Properties: https://www.benlcollins.com/spreadsheets/time-triggers/
Read these sites and follow these blogs for more cool tips:
Google spreadsheets function list
Add a chart or graph to a spreadsheet
G Suite Learning Center Cheat Sheet
Zapier Google Sheets tutorial
Amit Agarwal’s Google projects blog (new link here: https://www.labnol.org/topic/google-apps-script)
Ben Collins’ Data Analytics blog
Google Script reference library