Google Apps Script is great and all (and it’s a great starting point for writing code to control spreadsheets) but it doesn’t do all of the things I want it to. Namely, it doesn’t run Python, only Javascript (actually App Script but it’s a small distinction).
We are going to write some App Script in this series, but it won’t be the powerhouse of our logic. Instead, we will use it to capture user input and proxy requests to our own API.
If you’ve spent a bit of time with Apps Script it’s very likely that you’ve used SpreadsheetApp or the Sheets advanced service to interact with Google Sheets. If you are interested in expanding your horizons and using different tooling for this job here’s a starter tutorial showing you how you can spin up a Google Cloud Run service using Terraform which will let you deploy a Python API application.
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.
Learn how to search and find all values in an entire Google Sheet spreadsheet, in a specific sheet tab or in a range with Apps Script.
Handy little snippet from Scott Donald on using the Google Apps Script TextFinder class to find all the occurrences of matching text in a Google Sheet. The bit I particularly liked in this solution was how Scott used a .map method to return an object array of cell hits including A1 notation:
Array containing an object for each sheet containing the sheet name and the cell location (Image credit: Scott Donald)
Follow the source link for more information including a video tutorial.
These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet [sic] using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this.
In SpreadsheetApp there is the .deleteRow() and .deleteRows(), but when you have a lot of data or non-consecutive rows it can really eat into your Apps Script runtime limit. In searching for a better way of doing this I found this great solution from Kanshi Tanaike on Stack Overflow which uses the Google Sheets Advanced Service to delete multiple rows using one API call!
Using Sheets.Spreadsheets.batchUpdate isn’t just limited to deleting rows and for more examples see the source post which includes examples for hiding and deleting both rows and columns.
Take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.
Bulk add files to existing Google Drive folders
The following Google Apps Script tool is designed to take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.You can decide if you want the subfolder name appending/prepending/ignoring in the file name of the new copy to help make it unique.
The tool will iterate through a single level of folders (not sub-subfolders) and for each one place a copy of the files into it.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
ChatGPT is pretty much a universal API: one endpoint to get any data.
ChatGPT has recently taken the world by storm. Unless you have been living under a rock, then you have certainly seen a variety of content on this topic. One of its greatest advantages is its ease of use, and it is perfectly usable in Google Apps Script. With that in mind, here is one use case that we can implement: generating fake 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.
Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198
The onEdit trigger is likely the most used trigger in Google Apps Script. It runs automatically with an event object whenever you change a value in a spreadsheet — programmatic changes excluded — thus allowing you to execute a script based on context. When done properly, it can be extremely powerful. When done wrong, however, it can feel unreliable and messy.
In this article, we will learn to avoid three common pitfalls:
Not Exiting Early
Making a Single Function Do Everything
Expecting onEdit to Catch All Changes by Default
For this purpose, we will build a simple script to handle a task list in Google Sheets. It will do two things: add a checkbox next to new tasks and add a completion date when each task is checked as done.
Bulk combine multiple CSV files into a single Google Sheet, putting the data in specific locations and creating multiple tabs.
Bulk combine CSV files into a single Google Sheet
The following Google Apps Script is designed to take a number of CSV files (structured in an identical way) stored in a Google Drive folder and combine them all into a single Google Sheet file. It maps the data within the CSV file to specific cells in the Google Sheet, along with creating a tab for each of the CSV files to separate them out.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
In this article Jahswill Essien describes how to use Google Sheets with Flutter via Google Apps Script.
This article assumes the following:
You have experience using the basics of Flutter and can call APIs and read an existing codebase
You have little to no understanding of Google Apps Script
You have some experience using Google Sheets or spreadsheets in general
I’m sure many of you reading this post will already be familiar with Google Apps Script and have little/no experience deploying Flutter apps but I also know a lot of Google Apps Script developers are keen to try new stuff.
If deploying mobile apps is a thing that interests you I’d highly recommend also looking at Google no/low code development platform, AppSheet, which enables you to quickly create/deploy apps often using Google Sheets as a backend. If, however, you need custom graphics or pixel perfect UI, solutions like Flutter would be my personal starting point. Enjoy!
Learn how to list hidden Google Sheets tabs with Google Apps Script – CODE and VIDEO tutorial with script explainers.
This post from Scott Donald is an opportunity to learn about how you can work with hidden tabs in Google Sheets with Apps Script. For beginners this post is also an opportunity to learn about the JavaScript reduce method as a way to iterate through data. As well as the code and supporting video the author Scott Donald has some related projects where you can see this solution in action.
