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

How to recover an old Google Apps Script version (a no-code solution)

Many of us undisciplined hacks (read: not professional developers) sometimes find ourselves wondering when we will buckle down and start using Github to store our Apps script source files and versions.

If you sometimes find yourself in the same boat: needing to restore or access an old script version, the first thing you probably do is revert back to the old Apps script editor (IDE), (filling out the form regarding the lack of version history as your reason), and then hoping the version queue goes back far enough for you to recover what you need.

Well, today my undisciplined friend, I will show you a way to recover your script files, all the way back to version 1!! Yes, no more switching back to the old code editor.

In Pulse we’ve previously highlighted Romain Vialard’s solution to Retrieve previous versions of Google Apps Script projects, which uses UrlFetchApp to make a call to the Script REST API and add recovered files to your Google Drive. In this new contribution from Clark Lind a clever ‘no-code’ solution using Google’s interactive API Explorer. The post includes all the steps you need to follow if you need to recover an old version of a script project.

Source: How to recover an old script version

“This app is blocked” error on Google Apps Script [solution]

In this post, we’ll be going through a quick workaround so that you can get back to running your scripts. Note that this issue is still not entirely resolved, but you can follow any developments in Google’s issue tracker.

If you are a Google Apps Script developer using a consumer @gmail.com account for development/testing or sharing script projects for other users to use with their gmail.com account you may have encountered the “This app is blocked” issue. This issue appears to prevent a Google account from completing the Apps Script authentication flow even when using limited scopes.

This post from Aiman Fikri provides a solution for getting around this issue by associating an Apps Script project to a Google Cloud Platform (GCP) project. Google also provide documentation on setting up Standard Cloud Platform projects, but if you are supporting novice users directing them to Aiman’s post might be less daunting for them.

There are some benefits of using Standard GCP project particularly when you are developing scripts as it gives access to Cloud logs and Error Reporting. If you encounter “This app is blocked” on all your script projects you can group multiple scripts with a single Cloud Platform project to save having to go through the full setup process.

Source: “This app is blocked” error on Google Apps Script [solution]

Report: Handling 10 million cells in Google Sheets using Google Apps Script

In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.

In March 2022 Google announced that the Google Sheets cell limit is doubled from 5 million to 10 million cells. The increased capacity has implications for Google Workspace developers as you now may encounter scenarios where you have users with lots of data.

Fortunately, Kanshi Tanaike has been exploring the impact the increased volume of data in Google Sheets has when using Google Apps Script and both SpreadsheetApp and Sheets API. The linked report contains a number of useful findings and strategies for handling large Google Sheets with Apps Script.

Source: Report: Handling 10,000,000 cells in Google Spreadsheet using Google Apps Script

Making of Webhooks for Sheets Workspace Add-on [and lessons learned using the Apps Script API]

Behind the scenes look at what went into creating an Apps Script-native Add-on to generate Webhooks for Google Sheets. ICYMI: You can access the add-on from this link and know more about what it does here

It’s worth checking out Sourabh Choraria’s latest Google Sheets add-on which enables users to quickly setup a Google Sheet to receive data from other services which support the creation of webhooks. As part of this solution the add-on makes extensive use of the Apps Script API, which can be used to programmatically manage Apps Script projects including deployments. This post from Sourabh highlights some of the key endpoints used in the Apps Script API as well as a number of lessons learned about deploying web apps for users.

Source: Making of Webhooks for Sheets Workspace Add-on

Retrieving Google Docs summaries using Google Apps Script (hint: it’s using the existing Google Drive description property)

This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful. In this post, I would like to introduce to retrieve the summary of Google Document using Google Apps Script.

There are a couple of pieces of interesting information highlighted by this post by Kanshi Tanaike that caught my eye . First, useful to have a reminder that Google announced automatically generated summaries in Docs in February 2022. This might be a premium feature for the paid Workspace accounts as like Kanshi I’m not seeing this yet in my own free Google Workspace domain. The bit that really caught my eye is Google Docs summaries are using the existing Google Drive description property, which means it is not currently available via DocumentApp or if using the Google Docs API as an Advanced Service Docs . I’m sure this will catch some people out and hopefully this post will point people in the right direction.

Source: Retrieving Summary of Google Document using Google Apps Script