We’re all used to dealing with iterables such as Arrays, Maps and Sets. Creating a collection of input items, transforming then outputting is what we do all the time. However, sometimes that’s not possible – for example, where we have a potentially large input data set of unknown size that would be too big to fit in memory, or perhaps an output method with rate limits. Dealing with items 1 by 1, despite being by far the simplest to code, is often the least efficient to execute – so we use a paging method to get an input chunk at a time, and a bulk method to flush out the transformed results.
All of that can get a bit tricky, especially if you add asynchronicity into the mix.The use of iterators and generators can make coding as simple as 1 in -> 1 out, while handling chunking and bulking automagically. In this article, we’ll look at how to implement these.
In this blog post, Bruce Mcpherson explores the use of iterators and generators in JavaScript and how these can be used in Google Apps Script. A generator is a special type of function that can be paused and resumed at any point, yielding values one at a time. This is unlike regular functions that return all their results at once. Generators can be useful for handling large datasets efficiently by processing items in chunks and outputting results in bulk, even when dealing with asynchronous operations.
While Apps Script doesn’t natively support generators, Bruce has found a handy converter published by Facebook to make them compatible. As part of the post Bruce includes some Apps Script samples you can test and modify, demonstrating how to apply these techniques in real-world scenarios, even within the limitations of the platform. Bruce provides clear explanations of these concepts, illustrated with practical examples like finding breweries near specific locations and well worth spending the time to digest.
Time for an occasional update on the shared BigQuery dataset all about the Apps Script ecosystem on Github, with reports available on Looker Studio.
Bruce Mcpherson has created an October 2024 update report on Gassypedia, a shared dataset that tracks the Google Apps Script ecosystem on Github. The data is publicly available on BigQuery and Looker Studio if you want to explore yourself, but Bruce has also provided this report on trends in Apps Script usage.
Key findings from the October 2024 update include:
Growth in shared code and developers: There has been a 7% increase in the number of developers detected and a 6% increase in the number of repos and manifests shared.
Rise of Sheets Add-ons: A 10% increase in total Add-ons shared, with Sheets overtaking Gmail as the most popular type.
Increased advanced service usage: Drive remains the most popular advanced service, with an 11% increase in usage.
Library usage insights: The most popular library is oauth2, with 235 distinct users. Identifying library creators is challenging and current methods are not 100% reliable.
The report also includes detailed breakdowns of developer profiles, geographic locations, repo contents, manifest profiles, OAuth scope usage, and library usage. Overall, the data suggests that Apps Script usage on Github is growing, with more developers sharing their code and creating add-ons, web apps, and data studio connectors.
Click through to read more and see who, where and what some of the Apps Script community have been up to…
Gassypedia is a bigquery datatset and Google Looker report for analyzing on the Google Apps Script developer ecosystem on Github
The Apps Script developer ecosystem is an exciting and growing space. Bruce McPherson recently published a blog post that analyzes trends in this ecosystem using publicly available GitHub data which has been added to a BigQuery dataset.
Key findings of the post include that the number of Apps Script repositories and developers on GitHub is on the rise. There is a wide variety of Apps Script projects, including add-ons, web apps, and LookerStudio connectors. The report highlights popular OAuth scopes, advanced services, and libraries utilised by developers. It also offers insights into the geographical distribution of developers and the prevalence of tools like clasp.
Bruce acknowledges that the data is limited to public GitHub repositories, which may not be fully representative of the entire Apps Script ecosystem, but it’s nevertheless a great jumping off point if you are interested in exploring Apps Script solutions. You can access the full report via the source link.
If you are playing around with Sheet colors with Apps Script, you sometimes find yourself with font colors that don’t go well with the background colors you’ve chosen. However, we can use Yiq values to decide whether the luminance of the background color would be best with a light or a dark foreground font color. Here’s a small Apps Script library to figure it out for you.
Nice little helper library from Bruce Mcpherson if you’ve like to automatically apply contrasting colours to Google Sheets ranges. The source post from Bruce provides the background to the YIQ colour system as well as how to use the library. Follow the source link for more details.
How to fix Apps Script file loading order and defintion visibility problems with an Exports object.
It’s good practice to keep class and namespace definitions in separate files and avoid defining functions or variables in the global space. However, App Script doesn’t give you control over the order in which it loads files. If you reference a class or a namespace from one script file, it may not yet be defined. This is where an Exports object comes in.
As your script projects get larger and you start splitting out across script files you may find you need a little more structure. Class and namespace definitions are a good way to structure your code. Even when you do this you can still encounter problems with parts of your script trying to run before they are fully loaded.
This was a particular issue when the V8 runtime launched in 2020. This was fixed in June 2022, but it can still be an issue depending on how declarations are made in your code. To find out more about why this happens and how to fix it this post by Bruce Mcpherson shows how an Exports object can be used to structure your code.
I’ve been running this site for about 12 years ago. with over 1000 pages of content, here’s some of the high (and low) lights. I came to Apps Script not long after it was available, my first foray into it was probably around 2010, and I started writing about it not long afterwards.
I’m sure many Google Workspace developers are familiar with the work of Bruce Mcpherson. Regardless of whether or not you have, this is a nice summary of the last 12+ years of work published by Bruce last year but well worth revisiting. It covers everything from his move from VBA to focus on Apps Script, useful script libraries and code as well as explorations into other Google Cloud products.
Tank and Drv are SuperFetch plugins to emulate streaming and use the Drive REST API with Apps Script … This article will cover how to copy very large files using Tank to stream and Drv to upload and download partial content. The Apps Script Drive services have a limit on the size of files you can write, and very large memory usage can potentially cause Apps Script to fall over mysteriously
Clever stuff as always from Bruce Mcpherson, this time looking at how you can handle large files with Google Apps Script. We’ve featured some of Bruce’s other SuperFetch posts on Pulse, but developers can benefit from exploring the entire series so far on Bruce’s website. As well as SuperFetch plugins for Firebase and Twitter I’m personally interested in the evolution of the Google Drive client (Drv).
I’m using the JSON API for BigQuery rather than the Apps Script advanced service, since I have centralized all my BigQuery datasets in one project, and want to use a Service Account for authorization
Google Apps Script includes an Advanced service to interact with BigQuery. A limitation with both the Workspace services and Advanced Services don’t play nice if you need to use a service account. This post from Bruce Mcpherson provides details of how you can setup your Google Apps Script project to use a service account with BigQuery.
Another in the SuperFetch (a proxy for Apps Script UrlFetchApp) plugins series, Frb is a plugin to access a Firebase Real time database.
If you want to take your use of APIs a little further Bruce Mcpherson is continuing his series exploring his recently published SuperFetch library showing how a client can be setup to interact with Firebase. As Bruce highlights: “Firebase is pretty fast, so there’s not a huge speed benefit from caching, but if you’re on a pay as go plan, SuperFertch caching can reduce your Firebase costs.”
The source post provided by Bruce provides everything to need to set up the SuperFetch client and Firebase project.
I’ve written a few articles about JavaScript proxying on here, and I’m a big fan. I also use a lot of APIs, and it can be time consuming to keep on checking the REST documentation for how to call them and deal with the UrlFetch responses. SuperFetch is a proxy for UrlFetchApp to help.
Bruce Mcpherson has been busy again and this latest post introduces ‘SuperFetch’ the new Google Apps Script library which works as a proxy for UrlFetchApp. SuperFetch has some useful additional functionality including: built in caching and compression, standard response format and error handling, and built-in JSON parsing.
SuperFetch also has some useful features often required when using third party APIs including delaying between requests and rate limiting. Bruce has promised some additional posts detailing more complex API configuration options including authentication.
