AppsScriptPulse

Calculate contrasting font colors for Google Sheets with Google Apps Script

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.

Source: Calculate contrasting font colors for Sheets. – Desktop Liberation

Fix Google Apps Script file order problems with Exports 

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.

Source: Fix Apps Script file order problems with Exports – Desktop Liberation

12 Years and 1000 pages in Office, Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

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.

Source: 12 Years and 1000 pages in Office,Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

SuperFetch Plugins: Apps Script streaming with Tank and Drive (Copying very large files to/from Google Drive with Apps Script)

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).

Source: SuperFetch Plugins: Apps Script streaming with Tank and Drive – Desktop Liberation

Using Google Sheets via BigQuery from Google Apps Script with service accounts

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.

Source: Using Google sheets via Bigquery from Apps Script – Desktop Liberation

SuperFetch plugin – Firebase client for Google Apps Script – Desktop Liberation

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.

Source: SuperFetch plugin – Firebase client for Apps Script – Desktop Liberation

SuperFetch – a proxy enhancement to Apps Script UrlFetch – Desktop Liberation

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.

Source: SuperFetch – a proxy enhancement to Apps Script UrlFetch – Desktop Liberation

Simple but powerful Apps Script Unit Test library – Desktop Liberation

Image credit: the JavaScript Code CC-BY Dmitry Baranovskiy

Unit testing your code with this Apps Script Unit test library as you go along will makes it easy to immediately catch errors, and keeping a running test repertoire ensures that you don’t break anything.

When you start developing more complex Google Workspace solutions like add-ons and Chat apps it’s worth considering how you will test, debug and refactor your code. As noted in this post from Bruce Mcpherson the Apps Script community has published a number of different solutions/approaches to unit tests, a number of these appearing in Pulse.  This latest post from Bruce looks at the bmUnitTest library he has developed highlighting how it can be set up and used.

Source: Simple but powerful Apps Script Unit Test library – Desktop Liberation

Opportunities and workflows inlining Google Apps Script libraries – Desktop Liberation

This article will look at some of the opportunities you’ll have when you pull in your libraries inline rather than leaving them as references to external files, all without leaving the IDE

For the more advanced Google Apps Script developer this is a great article to get your teeth into which should also hopefully give you some tips to help manage and maintain complex Apps Script projects.

Source: Opportunities and workflows inlining Apps Script libraries – Desktop Liberation

Pull libraries inline to your Google Apps Script project – Desktop Liberation

Inline converter

This piece of work was both challenging and a lot of fun to produce, and it’s something I’ve wanted to get round to for a long time.  Apps Script libraries are a great way to reuse work you and others have done, but you have to be careful that they don’t get out of date. Libraries that refer to other libraries are complex to keep up to date, and worst of all they may disappear or their permissions change at any time.

Google recommend you don’t use them in Add-ons, and although they focus on ‘load efficiency’ (I did a study on this a few years back and found absolutely no evidence of a  measurable load penalty for libraries: see  Measuring library load speed), the above are probably better reasons to bundle all the code you use in your app or Add-on.

This builds on the work from Import, export and mix container bound and standalone Apps Script projects and I release it as a library (which you can of course use to inline it to your own project if you want!)

As noted by Bruce there may be a number of reasons you might want to inline libraries as part of your Apps Script projects, particularly if you are reusing third-party libraries where there is a risk the code could disappear.

Source: Pull libraries inline to your Apps Script project – Desktop Liberation