AppsScriptPulse

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 :

  • A 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

Bypassing the Maximum Script Runtime in Google Apps Script

Image: Dmitry Kostyuk

Google Apps Script is an amazing language that can automate a lot of your work. However, working with GAS also means that you have to learn to live with its built-in limitations and quotas. One such quota is the total script runtime. … I have experienced that the time required to complete tasks like copying or even simply listing files on a drive or in a directory can be quite long. Merging hundreds or thousands of documents can also take longer than both thresholds. Now let’s look into how we can build a solution

Another community contribution looking at handling script runtime and this one is also worth looking into for tips on structuring your code. The tutorial includes lots of useful information and an explanation of what is going on and might be a useful example to look if you are interested in moving your Apps Script coding abilities to the next level.

Source: Bypassing the Maximum Script Runtime in Google Apps Script

Tracking Google Sheet Opens with Google Apps Script

The basic principle behind tracking Google Sheet opens is the same as the one behind email tracking with a transparent pixel. In a Google Sheet, we will use the IMAGE()function, which will call a deployed Google Apps Script web app URL with some parameters in the query string. The web app will be able to do whatever you need among the following: record to a spreadsheet, create a message via a messenger, or simply send an email.

Source: Tracking Google Sheet Opens with Google Apps Script

Taking Away the Pain from Unit Testing in Google Apps Script

Unit testing is an extremely important practice. It helps developers avoid bugs easily and lets you know when something is broken immediately. … There are numerous libraries that exist for unit testing in JavaScript, including Jest and Mocha; however, they aren’t necessarily well-adapted to run under the Google Apps Script environment. … For that reason, I built my own library with Google Apps Script in mind.

We’ve featured a couple of community contributions around Google Apps Script Unit Testing and if this is a topic that interests you then the related posts section below will link to additional resources. This post from Dmitry Kostyuk is a great addition and the accompanying UnitTestingApp library that is shared in the post has some very useful Google Apps Script related features such as a dedicated method to check your data is a 2D array (particularly useful in the context of handling spreadsheet values). Follow the source link for more details…

Source: Taking Away the Pain from Unit Testing in Google Apps Script

Using Design Patterns in Google Apps Script – Introduction to façade and proxy patterns

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.

As noted by the post author Dmitry Kostyuk the Google Apps Script V8 runtime opens up many new coding opportunities previously not available to Apps Script developers. Using the common use case, fetching data and writing it to a Google Sheet, Dmitry explains how the façade and proxy code patterns can be used to write efficient easy-to-read code.

Source: Using Design Patterns in Google Apps Script