This piece of work was both challenging and a lot of fun to produce, and it’s something I’ve wanted to get round to for a long time. Apps Script libraries are a great way to reuse work you and others have done, but you have to be careful that they don’t get out of date. Libraries that refer to other libraries are complex to keep up to date, and worst of all they may disappear or their permissions change at any time.
Google recommend you don’t use them in Add-ons, and although they focus on ‘load efficiency’ (I did a study on this a few years back and found absolutely no evidence of a measurable load penalty for libraries: see Measuring library load speed), the above are probably better reasons to bundle all the code you use in your app or Add-on.
As noted by Bruce there may be a number of reasons you might want to inline libraries as part of your Apps Script projects, particularly if you are reusing third-party libraries where there is a risk the code could disappear.
In ‘Merging sheets from multiple sources and encrypting selected columns’ I published some code for selectively copying columns from multiple input spreadsheets/sheets to create summary sheets, and optionally encrypting columns. The idea was to distribute the same sheet to multiple people, along with private key(s) to decrypt columns to which they should have access. This seems a pretty handy thing to make into an Editor Add-on. I’m too impatient and life’s too short to bother getting into the Add-on publishing process, so I haven’t officially released this one personally, but here is a fully functional decryption Add-on that any of you are welcome to fiddle with and publish yourself.
Having previously featured some of Bruce Mcphersons previous posts on encrypting/decrypting Google Sheets data it seems fitting to share the post containing the final add-on. Even if you are not interested in the add-on’s functionality this post can still be worth a look to see how Bruce structures an add-on and uses Vue and Vuex to create the UI rendered with HTMLService.
here’s how you can generate a cryptographically random code verifier, hash it using sha256 & derive it’s base64 encoded challenge in google apps script.
Continuing the crypto theme Sourabh Choraria has been geeking out with Proof Key for Code Exchange (PKCE) OAuth 2.0 authentication flows which be used in the new Twitter API when making requests on behalf of users.
If you are unfamiliar with PKCE, you can find out more in Okta’s description on how to use PKCE to Make Your Apps More Secure. You can follow Sourabh Choraria post to find out more about the Apps Script implementations of this and the good news for Twitter/Apps Script users is the sample is now committed to the Google Workspace OAuth2 Apps Script library samples as Twitter.gs
This post introduces a library that can summarize selected columns from multiple sheets across multiple spreadsheets into 1 spreadsheet, as well as to optionally apply a public/private key data encryption scheme to selected columns, all via a simple JSON definition file.
Bruce Mcpherson continues to be one of my favourite Google Apps Script authors and often you get to gain an insight into his development process. This latest post is the latest in a series that follows a post on Super simple cipher library for Apps Script encryption and decryption. The source post shared here continues this journey, in particular, highlighting the use of Google Sheets Developer Metadata to store data bound to the spreadsheet.
Google Apps Script has some amazing built-in stuff. It gives us native access to all Google apps like Sheets and Gmail, seamlessly integrates with GCP services like BigQuery, allows for the building of interfaces with HTML and CardService, facilitates the creation of simple webhooks/APIs and web apps with simple and efficient client-server communication, can make use of any API through UrlFetchApp, and can be bundled into add-ons for efficient distribution. In my experience, it’s enough for 99% of all Google Apps Script developers.
However, one thing that Google Apps Script is missing is modules. NPM has an extremely impressive database of JavaScript modules that don’t automatically integrate with Apps Script. Of course, in Apps Script we have libraries, but the selection is extremely limited and there is no marketplace for those. By the way, who wants to participate in creating one? Let me know in the comments! However, the very first warning on the libraries documentation page notes that libraries make Apps Script slow. Well, Apps Script is already far from being the fastest programming language on Earth, so slowing it down further is not an idea that I’m a fan of!
But what if I told you that there actually is a way to use some NPM modules in Apps Script? You just need to bundle them with Webpack. Not sure what I mean? Keep reading.
This repository is an example of how to setup an automatic CI/CD process for Google Apps Script using GitHub Actions.
This solution come via Dimitri S on Twitter and provides a way to setup a continuous development workflow for Google Apps Script using clasp and Github. The basic recipe appears to be is you develop your code locally using your preferred IDE. You then use clasp to push your code to GitHub, which triggers a GitHub Action to deploy your code to script.google.com.
This article discusses row-level security in Google Data Studio and shows how community connectors can be used to overcome the limitations of the native feature when accessing data stored in Google Sheets.
I don’t think one of our summaries could ever do justice to this contribution from Pablo Felip. The post has a very thorough summary of row-level security and how Community Connectors coded in Apps Script can be used for additional levels of functionality.
Google Apps Script is handy, and it will help you a lot in your work. However, as you use it, you may hit a big wall. That is the six-minute limit on execution time. As the official documentation states, the maximum allowed time per execution of Google Apps Script is 6 minutes.
If the script execution time reaches 6 minutes, the script will stop suddenly, and an error message “Exceeded maximum execution time” will be displayed.
For as long as Google Apps Script has been around there have been various solutions published for handling the 6 minute execution limit. This post from Inclu Cat presents a nice overview and solution for the execution limit.
More technically, this library can be used to send http requests via UrlFetchApp.fetch or UrlFetchApp.fetchAll, interacting with api endpoints in raw form. By bringing it down to a lowest layer on this platform, you get the following benefits:
All of the options, features, and abilities that are available. No compromises.
Ability to batch the requests in bulk. Performance can be significantly improved and run times lowered.
If you’re looking for a way to duck under the 6 minute limit to your scripts, the last bullet point should be particularly interesting.
Este artículo presenta eMayordomo, un desarrollo GAS capaz de vigilar un buzón de Gmail y responder automáticamente y de manera condicionada a los correos electrónicos procedentes de diversos formularios web, enviando mensajes preparados de antemano. Estas respuestas preparadas soportan HTML, imágenes en línea, archivos adjuntos y emojis.
Google Translate: This article presents eMayordomo , a GAS development capable of monitoring a Gmail mailbox and responding automatically and in a conditional manner to emails from various web forms, sending messages prepared in advance. These prepared responses support HTML, inline images, attachments, and emojis
For non-Spanish speakers you’ll have to hit Google Translate but it is well worth it for the very thorough documentation Pablo Felip has prepared, which includes the source blog post and GitHub repository. The repository is particularly useful to look at as Pablo has taken the time to document and explain the various Google Apps Script functions he has developed, methods used and potential issues/limitations when interacting with a Gmail inbox.
