Everyone says use Google Apps Script libraries for easier maintenance — I found a better way. Here’s why NPM modules might change the game for you too.
Last time, we covered how to deploy our library as a native Google Apps Script (GAS) library using Vite, GitHub Actions, and clasp. This setup is efficient, but there’s a notable drawback to using Apps Script libraries.
According to the official guide: “Warning: A script that uses a library doesn’t run as quickly as it would if all the code were contained within a single script project. Although libraries can make development and maintenance more convenient, use them sparingly in projects where speed is critical. Because of this issue, library use should be limited in add-ons.”
Apps Script is not known for its speed, being a free runtime. Slowing it down further by using native GAS libraries is generally not advisable. Therefore, I prefer to avoid native GAS libraries whenever possible.
Instead, it’s much more efficient to bundle your dependencies directly into your source code, similar to how it’s done in Node.js. This is where NPM modules and Vite come into play.
In this post, we’ll cover:
Which NPM modules are compatible with Google Apps Script and which ones are not.
How to use NPM modules in your Apps Script projects.
A comparison of key-value store options in Google Apps Script, including PropertiesService, CacheService, Firestore, and Sheet Developer Metadata.
Justin Poehnelt has shared a comprehensive look at key-value store options in Google Apps Script. Key-value stores are useful for storing data like user preferences or frequently accessed data, which can also be shared between script executions.
As part of Justin’s post as well as looking at the built-in store options, PropertiesService and CacheService, there is also a look at scenarios where you might want to consider alternatives like Firestore.
The choice of key-value store depends on factors like expiration needs, number of items, value size, access control requirements, latency sensitivity, and cost considerations. Justin’s post provides a thorough analysis of each factor, including latency comparisons.
Head over to the source link to get all the details!
Use Google Apps Script to create a custom menu that will work inside Google Sheets, Google Docs, Slides and Google Forms.
Tired of writing separate code for custom menus in Docs, Sheets, Slides, and Forms? This nifty Google Apps Script snippet by Amit Agarwal helps you build a universal menu that works across all these apps!
The solution is a small snippet which detects the context to see which of DocumentApp, SpreadsheetApp, FormApp, SlidesApp are available which then lets you switch to get the appropriate .getUI() method.
Check out the source post for the full code and streamline your add-on development!
If your automations are not being triggered by Google Form submissions, Pablo Felip’s tip is it could be because your Google Workspace Admin has turned on AppSheet Core Security.
Forget everything you know about deploying Google Apps Script, because the typical ‘Deploy’ button is not part of our strategy.
TLDR;
In the previous posts in this series, we focused on preparing our tools and setting the stage. We reviewed our project’s functionality, explored the project structure, and delved into the source code. We also discussed the pros and cons of using JSDoc versus TypeScript and covered essential developer tooling.
Today, we’re finally getting to the fun part—deploying something! We’ll start by deploying our library as a native Google Apps Script library. But there’s a twist: we will do it the proper CI/CD way, avoiding the typical “Deploy” button in the online IDE.
Side note: I always avoid the online IDE as it feels like my hands are tied when I use it.
Instead, we’ll use Vite with a custom plugin to bundle our library and GitHub Actions to handle the deployment.
Learn how to move uploaded files from Google Forms to a specific folder in Google Drive. You can also rename the files based on the form responses
Amit Agarwal, the brains behind Digital Inspiration’s Document Studio must have one of the most feature full Google Workspace Marketplace Add-ons. I also like Amit’s approach of sharing how you can solve problems like moving Google Form file uploads using Document Studio as well as providing an Apps Script snippet if you would like to manually code it.
In this example some setup is required to specify a folder ID and create an onFormSubmit trigger. After this the script then retrieves uploaded files from the form response, creates a subfolder named after the unique Response ID, and moves the files into that subfolder within the specified parent folder. This provides a structured and organized way to manage file uploads from Google Forms.
Follow the source link to read more about this script solution and about Document Studio.
In this series about deploying Google Apps Script libraries, Part 2 dove deep into Test-Driven Development (TDD) and highlighted jest as my go-to tool for outstanding outcomes. Now, let’s broaden our toolkit with additional essentials that not only elevate our development standards but also make our codebase more welcoming to collaborators.
First up, we’ve got ESLint. Linting, for those unfamiliar, is the process of running a program that will check your code for potential errors.
Next, we introduce Prettier, which takes our code and formats it into a structured style that you define. Think of Prettier as the stylist of your codebase, making sure everything looks ‘pretty’ and readable by cleaning up those messy or inconsistent styles left behind.
We also employ Husky, a tool for managing git hooks. Git hooks are scripts you can set to run before or after events like commits, push, and receive. Husky ensures that tools like ESLint, Prettier and Jest run every time you commit changes, helping catch issues before they hit production.
These tools are indispensable not just for solo developers but also for teams looking to contribute to the library. By maintaining consistent, pre-defined high standards of code hygiene, we ensure that our library remains robust, clean, and maintainable.
Prerequisites: You should be familiar with git, an essential skill for any developer. If you’re not, I recommend checking out the beginner’s guide at FreeCodeCamp. Additionally, being able to code locally and understanding clasp is crucial. If you need a refresher on this, visit my earlier post on setting up your local environment. Once you’re set, we can dive into making our development process even smoother and more efficient!
Here are the links to the repo and the NPM page again.
About a year ago, a notable shift occurred in the developer community when SvelteKit made the decision to transition from TypeScript to JSDoc, specifically stating that TypeScript was not the ideal choice for developing libraries. This move definitely raised some eyebrows and stirred the pot among developers. Initially, I was skeptical, perceiving it as a regression rather than progress. TypeScript was even featured in one of my previous blog posts as a recommended tool. However, this decision began to resonate with me over time, leading me to adopt a similar approach in Google Apps Script. The reasons for this shift are manifold, with some being universally applicable and others specific to the peculiarities of Apps Script.
Here are the links to the repo and the NPM page again.
Let’s dive into why TypeScript was chosen in the first place and what has changed to endorse JSDoc over it.
In February 2024, Google made a change in Google Sheets to enhance data security. Now, users must provide explicit consent before Sheets can connect to external data sources. This change aims to prevent unintentional data leaks and improve overall data loss prevention within Google Sheets.
Impact on Users:
When manually adding or editing certain IMPORT functions (IMPORTHTML, IMPORTXML, IMPORTFEED, IMPORTDATA) and the IMAGE function in a Google Sheet, a warning banner will appear blocking sending a receiving data until the user gives consent. To enable this functionality, users need to click “Allow access.”
Impact on Developers:
As highlighted in the first source post by Justin Poehnelt, this change caused issues for developers who programmatically create Google Sheets and automate tasks, such as generating PDF documents as the IMPORT and IMAGE functions were blocked.
Solutions for Developers:
Fortunately, Google introduced an update to the Google Sheets API to address this challenge. Developers can now enable external data access programmatically by setting the importFunctionsExternalUrlAccessAllowed property in the Google Sheet metadata. This allows specific IMPORT and IMAGE functions to work without manual user consent.
For the IMPORTRANGE function, a similar API property is currently unavailable. However, an unofficial workaround exists that involves calling an undocumented endpoint using Google Apps Script as detailed in the second source post by Kanshi Tanaike.
Future Expectations:
As Google continues to prioritize data security, there will likely be further enhancements to data loss prevention controls within Google Workspace. Hopefully, future updates will also include official API controls for IMPORTRANGE and other functions to facilitate legitimate data access for developers while maintaining data security.
Following up from last week, we get into how I built the table() method in our ConsolAS class, using test-driven development (TDD). This is a cool way to make sure everything works perfectly by testing each part before we fully build it. We’re aiming to make it work just like the console.table() function in Google Chrome, which is pretty handy.
