Category: Advanced Google Services

Multiple Google Sheets Async Requests in Google Apps Script

While we can efficiently read and update multiple ranges in Google Sheets with the Google Sheets API Advanced Service in Google Apps Script, there is often occasions where I want to run multiple batch updates on different Google Sheet files.

As you have probably discovered, updating multiple sheets consecutively can be time-consuming and frustrating.  I wanted a way to read and update my Google Sheets asynchronously.

Why?

Let’s say I have a bunch of Google Sheets, one each for my users. Perhaps on a daily cadence, I want to gather aggregate data for their manager. Perhaps my manager wants me to add some new spreadsheet features to the current Google Sheets tools. I need an efficient way of updating and reading these sheets.

So was I successful?

Well, mostly.

I discovered that I could run batch updates on up to around 60 Google Sheets somewhat asynchronously.

How did I do this?

I discovered that while “UrlFetchApp.fetchAll()” does not claim to be asynchronous, benchmark research by Kanshi TANAIKE suggests that “fetchAll()” behaves asynchronously.

In conjunction with the Google Sheets API’s “spreadsheets.values” batchGet and batchUpdate requests, we can do a pretty good job updating up to around 60 Google Sheets very quickly.

Let’s dive in!

Continue reading “Multiple Google Sheets Async Requests in Google Apps Script”

Get Google Sheet Tab Name and ID with Google Sheets API Advanced Service with Apps Script

The Google Sheets API Advanced Service has a strange habit of switching between using Google Sheet names like 'Sheet1' and the sheet ID that you would see in the URL, like 482103711.

This can be problematic when working with complex processes in the Sheets API.

One of the first things I do when running a complex process is to make an initial call to the Sheets.Spreadsheets.get() method to generate an array of all of the sheet tabs by name and sheet ID. Yes, it is an additional call to the API but if we do it initially in conjunction with any other core requests from the spreadsheet then we only need to do it one time.

Continue reading “Get Google Sheet Tab Name and ID with Google Sheets API Advanced Service with Apps Script”

List All Users in an Organisation’s Google Workspace Account with Google Apps Script

Note! This tutorial is for Google Workspace for organisations and not the free consumer account, unfortunately. 

While the Google Apps Script docs provide a great example of how to get a list of users in a Domain on a Google Workspace account, it is not in the scope of the documentation to go into the weeds and explain all the ways we can search for all users.

Weeds sound much more like the purview of a goat. A coding goat, perhaps 🐐. Me. I’m talking about me…yeesh!

In this tutorial, we will cover how to access your Google Workspace organisation’s user data, what data you can retrieve and how it looks, who can retrieve it and a couple of ways to display what you need.

This post is intended as a resource reference that compliments the Google Docs on the Admin SDK. Links to the Google documentation are provided throughout the post. It is worth a bookmark if you intend on using the Admin SDK a lot in Google Apps Script.

Use the contents page to navigate to what you need.

Continue reading “List All Users in an Organisation’s Google Workspace Account with Google Apps Script”

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet (Updated Feb 2024)

Google Apps Script: DriveApp, Advanced Drive Service, SpreadsheetApp,  DocumentApp Javascript: spread operator, map, regex

Ah, bureaucracy at its finest.  Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. A… p…d…f.

Gee, thanks.

After some prostrations to the great Google Apps Script gods, I had a though.

“Hey, can’t we convert a PDF to a Google Doc with just a click of the button? Surely the great Google Apps Script devs have made it so we can do it programmatically too.”

And you know what? They bloody well did. The big legends.

The Scenario

I’ve just received a bunch of PDFs. The PFDs are all labelled by the class number. Take a look at the files in my Google Drive:

PDFs of sections in Google Drive

Each PDF file contains a list of student IDs that I need to extract and put into a Google Sheet.

PDF file with student IDs

The aim is to have a list of student IDs in column A and their corresponding sections in column B.

As you can see, we have some pretty standard text in the PDF that should be easy for Google to recognise so that we can extract the IDs.

The list of names in the demo sheets were randomly generated by AI!

NOTE! As always, I have tried to create this tutorial for varying levels. Feel free to follow along, or just grab what you need and get stuck into your own project.

If you are playing along, you can find a copy of the PDF files below. Simply add them to your own Drive before you get started:

PDF files by Section.

Continue reading “Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet (Updated Feb 2024)”

Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 3                                         <<Intro>>

In Part 3 of our 2D array data transformation course in Google Apps Script, we worked out how to get the count of each choice of each question item from the survey results in a Google Sheet.

This time we are going to add a final element to our mix. Let’s say we have multiple questions and multiple groups. We want to find out the count for each choice for each question for each group.

Continue reading “Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets”