AppsScriptPulse

The Ultimate Google Apps Script Front-End Development Guide. Part 0: The Worst and the Best Practices

Welcome to the Ultimate Google Ultimate Google Apps Script Front-End Development Guide!

If you are reading this post, then you likely have experience with Google Apps Script. Essentially, there are three types of front-ends that you can create with Apps Script:

  • Web apps
  • Modal/modeless dialogues
  • Sidebars

In this blog post series, we will only discuss HTML front-ends, as this is where you can create the most powerful and sophisticated user experiences. Unfortunately, the Card Service is outside the scope of this series, and it is not nearly as powerful as an HTML front-end. With that in mind, Google, how about allowing HTML front-ends for Workspace add-ons?

Source: The Ultimate Google Apps Script Front-End Development Guide. Part 0: The Worst and the Best Practices

Using design patterns in Google Apps Script

Google Apps Script is a JavaScript-based language that has access to Google Workspace-specific libraries for Gmail, Google Sheets, Google Forms, Google Drive, etc., and allows you to quickly and efficiently automate your tasks and program business applications.

A lot of users try and quickly learn GAS and use it to make their lives easier. It’s all great, however the code we sometimes tend to come across on StackOverflow and other sites lacks best practices, hence I thought it was time to start bringing them up and I will start today with design patterns.

I’m all for copy/paste coding and it one of the things I love about the Google Apps Script developer community, there are lots of great snippets out there and in Pulse we’ve now over 800 posts and counting. When you start going beyond quick script solutions into more complex projects investing time planning how you’ll structure your code can save you headaches and frustrations further down the line.

Using design patterns are one way to produce better code that is more readable which in turn is more maintainable and can lead to faster development. This post from Dmitry Kostyuk a nice opportunity to learn about a design pattern for a very common use case of maintaining data in a Google Sheet from a third party API.

Source: Using Design Patterns in Google Apps Script

Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

ChatGPT is pretty much a universal API: one endpoint to get any data.

Fake data is useful for testing purposes. At times, real data is too sensitive, and sometimes you do not have access to real data before production, but you need to begin working with something. Here is where fake data comes in.

We used to have a great library for fake data called Faker.js; however, Marak, the author, became so frustrated that he could not monetize the solution that he pushed an update that broke everything, which led to him being blocked on GitHub and NPM. As a result, the library is no longer maintained. ChatGPT, however, is not going anywhere.

Source: Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

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

How I Programmed the Game of Life in a Google Sheet with Google Apps Script

According to Wikipedia, the Game of Life “is a cellular automaton devised by the British mathematician John Horton Conway in 1970.”

It begins on a two-dimensional grid of square cells. Each cell can be either alive or dead. Every cell interacts with its eight immediate neighbors. A live cell only remains alive if it has two or three living neighbors. If it has fewer than two living neighbors, it dies as if by underpopulation. Conversely, if it has more than three, it dies as if by overpopulation. A dead cell remains dead unless it has exactly three living neighbors; otherwise, it becomes a live cell, as if by reproduction.

There is no immediate practical use for the Game of Life in a spreadsheet; however, it is a fun algorithmic challenge. Moreover, Google Sheets natively provides us with the perfect data structure: a two-dimensional array. This is all the more reason to work on those array skills!

As usual, there is a GitHub repo with the full source code. Alternatively, you can just make a copy of this spreadsheet.

Source: “How I Programmed the Game of Life in a Google Sheet with Google Apps Script

How to Build an API Wrapper in Google Apps Script

No Google Apps Script SDK for your favorite API? No problem!

I’ve never seen a Google Apps Script SDK made available as part of an API, as, surprise surprise, it’s not the most popular choice for professional development. It’s time to start changing that, so we will learn to build our own!

[Editor note: Really impressive work from Dmitry providing a framework you can use to develop your own service for interacting with 3rd party APIs. The post includes lots of clear instructions and guidance to help you understand and learn about approaches for structuring your Google Apps Script code projects]

Source: How to Build an API Wrapper in Google Apps Script

The ULTIMATE Guide to NPM Modules in Google Apps Script

The What and Why of NPM Modules

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.

Source: The ULTIMATE Guide to NPM Modules in Google Apps Script.

How to Write Google Apps Script Code Locally in VS Code and Deploy It With clasp

Google Apps Script Power Up

Why Write GAS Code Locally?

When it came out, the new Google Apps Script IDE was, of course, a big deal. It’s way better than the legacy one. It brought syntax highlighting, keyboard shortcuts, command palette and just a much better UI experience.

In fact the reason it’s so good is that it’s built on top of VS Code Monaco editor. But it’s definitely still far away from what a real VS Code installation can do. You can get all the functionality of the online IDE and much more more: autocomplete, custom themes, installation of modules, linting, snippets, etc.

The bare minimum that you would need is :

  • VS Code installation (duh!)
  • Node.js + NPM : a JavaScript runtime with a built-in module installer;
  • Autocomplete;
  • clasp: a CLI utility to sync your GAS code

Let’s get into it!

Source: How to Write Google Apps Script Code Locally in VS Code and Deploy It With clasp

How to Use Service Accounts and OAuth2 in Google Apps Script

Image: Dmitry Kostyuk

Give your scripts privileges that your users don’t have … In 99% of all cases, authorizations in Google Apps Script are extremely straightforward. When a user executes their script, they run it as themselves with their respective authorization scopes. … However, what if you need to give more rights to your app beyond what your intended users will have? … This is where service accounts come in.

A useful post to find out more about service accounts and how to use them with Google Apps Script. The tutorial put together by Dmitry Kostyuk includes example code for using a service accounts with Firestore, BigQuery and the Admin Directory API.

Source: How to Use Service Accounts and OAuth2 in Google Apps Script

How to Build Custom Functions in Google Sheets with Google Apps Script

Build your own functions in Google Sheets that do anything you need them to in a few lines of code.

This is a great tutorial if you are interested in finding out more about developing custom functions in Google Sheets. The post includes everything you need to get started from handling single cell and range inputs, to documenting your function so that users see inline help.

Source: How to Build Custom Functions in Google Sheets with Google Apps Script