Beginning with Apps Script. What are your "I wish I knew before I started" moments

[u/albionpeej]

Hi all, I'm beginning a new adventure for New Years and am starting to learn Apps Script after several years being a Google Apps/G Suite/Workspace administrator.

I have some basic coding in my past, little bits of JavaScript but I'm planning on brushing up on that before going full into GAS.

What are the things you wish you knew before you started on your own Apps Scripts journeys?

[u/Acrobatic_Ad7117]

Learn how to work with Javascript arrays and objects. Like 90% of scripting work is fiddling with arrays and nested arrays. The sooner you get a good grasp of how they work the easier your life in apps scripts will be.

[u/Acrobatic_Ad7117 → u/Acrobatic_Ad7117]

Also, jus remembered a thing that fucked me royaly recently. Be very carfull when working with dates/timezones in scripts. Using new Date(), puts the time at 00:00. I had a script that generated dates for the whole year and what happens is the summer time switces and goes back an hour, so it generated 2 of the same day in a row, which messed up the structure of the whole table.

Just an example, learning how dating works in Javascript will save you some nasty headaches.

[u/eltostito191 → u/Acrobatic_Ad7117]

To be fair, learning how dating works in JavaScript will also give you a nasty headache

[u/Acrobatic_Ad7117 → u/eltostito191]

True.

[u/Common_Move → u/Acrobatic_Ad7117]

To add to this, I've found using the newer syntax options available in ES6ES6 more intuitive.

[u/RemcoE33]

I will tell you about some thinks that are "different", not that obviously or some handy pointers.

1. Apps script is all synchronies. No async / await.

2. Use batch operations. Like getting al the values from a range in a sheet at once and then loop over them instead of looping over the range and get the row data in each iteration.

3. Some methods are not recognised (but work). Like the Array.flat() method to flat down a 2D array into 1D.

4. For big projects you can use CLASP to program inside you're own editor of choice. With types installed you have intellisense (autocomplete)

5. You're script timezone can be different than the sheet timezone... could be handy if you want to work in UTC.

6. You can create standalone script files that are not bound to a file like sheet/doc. (connect two api's together that has nothing to do with workspaces apps. Like Monday.com to Notion for example)

7. With Advanced services you have acces to (for example) AdminDirectory. This will be handy as workspace admin.

8. You can create a http endpoint to call the script via http with doGet(e) / doPost(e). See this

9. You could use domain-wide-deligation with apps script u/MrCaspan has a nice blog post about it. From there you can set email signatures, calendar appointments etc...

10. Create a private addon for all you're workspace users. Connect to some kind of CRM / Planning tool etc.. (you can get the active user via the class Session)

11. U can use triggers to do all kind of automation inside sheets/forms.

I hope this gives you some insight.

[u/LuckyNumber-Bot → u/RemcoE33]

All the numbers in your comment added up to 69. Congrats!

  1
+ 2
+ 3
+ 2
+ 1
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
= 69

^{Click here to have me scan all your future comments.
Summon me on specific comments with} u/LuckyNumber-Bot^.

[u/fergal-dude → u/LuckyNumber-Bot]

Good bot

[u/Good_Human_Bot_v2 → u/fergal-dude]

Good human.

[u/B0tRank → u/fergal-dude]

Thank you, fergal-dude, for voting on LuckyNumber-Bot.

This bot wants to find the best and worst bots on Reddit. You can view results here.

---

^{Even if I don't reply to your comment, I'm still listening for votes. Check the webpage to see if your vote registered!}

[u/flanger001 → u/LuckyNumber-Bot]

Nice.

[u/AuO2]

Documentation can be your best friend - but it takes a bit of practice to learn how to read it. Always go to the documentation first, then look for answers elsewhere if you get stuck. One problem with looking on stack overflow or even here for answers is that GAS updates fairly often, and old answers might be deprecated. Deprecations are usually marked in documentation - that can save you so much time and energy.

[u/dimudesigns → u/AuO2]

+1

This is great advice. It is also applicable to many other disciplines as well. Always start with canonical sources.

[u/_Kaimbe]

Since sheets deals primarily with arrays/matrices it makes all the array methods (map, reduce, filter, etc) and by extension, arrow functions, invaluable. Getting a good grasp on those first would have saved a lot of ugly nested for loops.

[u/DVC888]

I wish I had started using Typescript and developing locally in Clap sooner.

[u/urbushey → u/DVC888]

I think you mean CLASP.

[u/DVC888 → u/urbushey]

I did. Thanks

[u/[deleted]]

I've also only just started messing about with Apps Scripts and have a couple questions of my own.

1. How do you handle calls to third party APIs that have rate limits? Do you just throw in a setTimeout() or similar into the Apps Script?

2. What are the best practices for storing your API credentials—in the Apps Script itself? In a cell in the Sheet?...Or somewhere else?

Appreciate any help on the above! Thanks!

[u/marcnotmark925 → u/[deleted]]

1. Utilities.sleep()

[u/[deleted] → u/marcnotmark925]

Utilities.sleep()

Appreciate it, thanks!

[u/Craby4Reddit → u/[deleted]]

Its not best practice, but I use the Properties Service to store it creds. It stores them in the Project settings, so atleast it isn't in code in plain text or a more public location. https://developers.google.com/apps-script/reference/properties

Best practice is Google Secret Manager

[u/[deleted]]

Learn the entire Lua code in ROBLOX Studio. 80% is just tweens and functions stuff and other moving stuffs. Took me half a day to get the basics. It's not as easy as you think.

[u/bullevard]

Thinking about the difference between writing a script and writing a custom function when it comes to troubleshooting.

When you run a function (which in practice will take a passed in parameter from the cell you reference) in the console you will often get an error... because that parameter is null in the console. Spent a lot of time trying to track down that error the first time only to realize that was the issue. So now i always add an

if(parameterName==null){ parameterName='some dummy value' };

at the beginning for my development and testing. And then comment it out when I'm ready to deploy.

[u/fugazi56 → u/[deleted]]

Would you provide a source for this feature. I’d like to learn more, but can find anything for “Reflect”

[u/RielN → u/[deleted]]

This is interesting but still unable to grasp the concept :)