AppsScriptPulse

Three pitfalls to avoid when using the onEdit trigger in Google Apps Script

Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198

  1. Making a Single Function Do Everything
  2. Expecting onEdit to Catch All Changes by Default

Source: Three Pitfalls to Avoid When Using the onEdit Trigger in Google Apps Script

Fix Google Apps Script file order problems with Exports 

How to fix Apps Script file loading order and defintion visibility problems with an Exports object.

It’s good practice to keep class and namespace definitions in separate files and avoid defining functions or variables in the global space. However, App Script doesn’t give you control over the order in which it loads files. If you reference a class or a namespace from one script file, it may not yet be defined. This is where an Exports object comes in.

As your script projects get larger and you start splitting out across script files you may find you need a little more structure. Class and namespace definitions are a good way to structure your code. Even when you do this you can still encounter problems with parts of your script trying to run before they are fully loaded.

This was a particular issue when the V8 runtime launched in 2020. This was fixed in June 2022, but it can still be an issue depending on how declarations are made in your code. To find out more about why this happens and how to fix it this post by Bruce Mcpherson shows how an Exports object can be used to structure your code.

Source: Fix Apps Script file order problems with Exports – Desktop Liberation

12 Years and 1000 pages in Office, Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

I’ve been running this site for about 12 years ago. with over 1000 pages of content, here’s some of the high (and low) lights. I came to Apps Script not long after it was available, my first foray into it was probably around 2010, and I started writing about it not long afterwards.

I’m sure many Google Workspace developers are familiar with the work of Bruce Mcpherson. Regardless of whether or not you have, this is a nice summary of the last 12+ years of work published by Bruce last year but well worth revisiting. It covers everything from his move from VBA to focus on Apps Script, useful script libraries and code as well as explorations into other Google Cloud products.

Source: 12 Years and 1000 pages in Office,Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

Alice Keeler’s top tips for getting your Google Workspace Add-on published | Google Cloud Blog

If this is your first attempt at submitting an add-on for the Google Marketplace, it—like all new experiences—can take longer than expected as you learn and get comfortable with all of the requirements. You should expect pushback from both the OAuth team and the Marketplace team, as they are on the frontline of ensuring that end users have a positive experience installing Add-ons. Taking the time to slowly go through and make sure you have each of the elements along with a willingness to update and improve your application will surely result in the successful publication of your Add-on published in the Google Marketplace.

Alice Keeler knows a thing or two about publishing Google Workspace Add-ons to the Marketplace with over 20 entries. In this post on the Google Cloud Blog Alice shares some of her top tips for surviving the publication process. This includes website essentials, tips on artwork as well as creating your verification video. Follow the source link for these tips and more.

Source: Google Cloud Workspace Add-On Customization | Google Cloud Blog

If you use clasp with Google Apps Script, you need this environment switching utility right now!

When working in a team and/or with a client, you want to have multiple environments. At minimum, you probably want a dev environment (or multiple ones) in which you are working, and a test environment in which the client or your team can run acceptance tests before production. Of course, they must both be separate from the production environment. To push your code to the correct environment, you need to either update the .clasp.json file manually or keep multiple copies of your script with different .clasp.json files. Fortunately, things have just become significantly easier, as I recently built an app for this purpose called clasp-env, which is available on NPM. See the source link for details.

Source: “If You Use Clasp With Google Apps Script, You Need This Utility Right Now”

Retrieving Smart Chip dropdown values from Google Docs using Google Apps Script

This is a sample script for retrieving the values of dropdown list of the smart chips on Google Document using Google Apps Script.

At August 23, 2021, 3 Classes for retrieving the smart chips have been added to Google Apps Script. But, in the current stage, unfortunately, all values of the smart chips cannot be retrieved by the Classes. For example, the dropdown list of the smart chips cannot be retrieved

Incredibly useful report and workaround from Kanshi Tanaike for Google Workspace Devs needing to get some ‘smart chips’ values from Google Docs. Hopefully classes/methods will be added to Apps Script and the Google Docs API (here is a related feature request you can star in the issue tracker), particularly as the current solution is to convert the Google Doc to .docx and then back to Google Doc.

Source: Retrieving Values of Dropdown List of Smart Chips on Google Document using Google Apps Script

Benchmark: Process cost for HTML Template using Google Apps Script

Image credit: Kanshi Tanaike

When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.

A great feature of Google Apps Script is the ability to create and serve custom HTML, often used to interface data you have in Google Workspace such as Google Sheets. Google highlight a coupe of different ways you can mix Apps Script code and HTML. Some of these ways are better in terms of process time and this report from Kanshi Tanaike highlights the cost of calling Apps Script functions as scriptlets in HTML templates. The good news is you can avoid delays in your web app rendering by making asynchronous calls with google.script.run, which you can read more about in Google’s best practices documentation.

Update: I’ve replicated this benchmark (smaller dataset) with google.script.run and it was only marginally slower (0.3s) than the ‘create HTML table with Google Apps Script’:

Source: Benchmark: Process cost for HTML Template using Google Apps Script

A beginners API authentication cheat sheet for Google Apps Script

See four different API Authentication methods presented in Apps Script, including authentication in query string, headers, and OAuth2.

I got fed up digging around in my Drive folder for old scripts to refresh my memory on the syntax, so I created this reference.

It’s not a comprehensive post on how to connect to APIs, instead, it’s a short summary of common protocols for easy reference.(If you’re new to APIs, start with my Apps Script API tutorial for beginners.)

We are currently spoilt for choice with Google Apps Script community contributions. This is a great post from Ben Collins for Google Apps Script beginners highlighting different patterns used to interact with third party websites with APIs.

An API is essentially an interface that can be used by a computer programme to retrieve or interact with another application.

What is an API?

If you would like to find out more about API Ben links to his API tutorial for beginners or I have shared workshop materials for ‘Machina a machina: An introduction to APIs with Google Sheets‘.

Check out the source link for Ben’s cheat sheet and other resources 👇.

Source: API Authentication Cheat Sheet for Apps Script

SuperFetch plugin – Firebase client for Google Apps Script – Desktop Liberation

Another in the SuperFetch (a proxy for Apps Script UrlFetchApp) plugins series, Frb is a plugin to access a Firebase Real time database.

If you want to take your use of APIs a little further Bruce Mcpherson is continuing his series exploring his recently published SuperFetch library showing how a client can be setup to interact with Firebase. As Bruce highlights: “Firebase is pretty fast, so there’s not a huge speed benefit from caching, but if you’re on a pay as go plan, SuperFertch caching can reduce your Firebase costs.”

The source post provided by Bruce provides everything to need to set up the SuperFetch client and Firebase project.

Source: SuperFetch plugin – Firebase client for Apps Script – Desktop Liberation

Dynamically resizing dialogs in Google Workspace documents using Google Apps Script and google.script.host

In this article, we’ll go over the different ways a GAS script can relate to the user using it, and show an easy way to dynamically resize dialogs built using the Apps Script HTML Service. To achieve this, we will introduce some basic concepts related to the object model of HTML documents and their manipulation using JavaScript. – [Google Translated]

A couple of community contributions for custom dialogs have landed in the Pulse inbox recently. The official documentation on Dialogs and Sidebars in Google Workspace Documents is an excellent starting point, but if you prefer learning from video tutorials Chanel Greco has recently published Google Apps Script Alert – Deep Dive on the saperis YouTube channel.

If you’d like to go a little further this post from Pablo Felip details how custom dialogs can be dynamically resized using the methods in google.script.host and client-side JavaScript (for non-Spanish speakers you’ll have to view this post via Google Translate).

Source: Cuadros de diálogo de tamaño dinámico en Apps Script