While working on Sheets Workbook functions – converted to Apps Script I realized that I’d need to tackle the query language at some point, so I figured I may as well go the whole hog and implement a comprehensive SQL variant for Apps Script. Luckily though, I found alasql so with a few tweaks it was ready to go!
A couple Google Apps Script community contributors have shared solutions for using the AlaSQL.js library. Latest come from Bruce Mcpherson, which is included as the source link. If you use Bruce’s fiddler library his post is worth checking out as he provides examples showing how both libraries can be used together. Another version of AlaSQL.js you should look at is Alex Ivanov’s AlaSQLGS which also includes some data and code samples.
If you are handling data from sheets it might be useful to use the same logic as is available in the worksheet for common tasks rather than reinventing them. Dates and Times are especially complicated as JavaScript dates are not good with timezones and date arithmetic, especially since spreadsheets and scripts can exist in different timezones from each other. I’m using Temporal – dates, times and timezones and the proposed new Date system for ECMAScript to emulate what Sheets functions do, and I’ll go through the implementation as a learning aid for Temporal as much as a documentation of the functions.
How google apps script has changed over the past 5 years. Episode 2 in a series of post covers the Properties Service and the Spreadsheet service
Bruce Mcpherson continues his look at changes in Google Apps Script over the last 5 years, this time looking at PropertiesService and SpreadsheetApp. This post is worth a read just to check you’ve not missed any new methods for working with Google Sheets, because over the last 5 years there have been many!
Sometimes you need to generate some fake data for a spreadsheet. In this post I’ll cover a few utilities in 2 separate libraries that can help with this.
Bruce Mcpherson has put together a Google Apps Script library which is a port of Faker.js and elements of d3.js to make it easier to generate random data in your projects. You can see the full list of Faker.js methods which includes a wide range from names/addresses to dates to just lorem text. The library can also generate various fake datasets, strings and selectors.
It’s been over 5 years since my book ‘Going Gas‘ was published, and Apps Script evolution means that a number of sections of it is now pretty out of date. I don’t think I have the energy to do a rewrite – so I’m going to do a critique, chapter by chapter, of where it’s now wrong as a way to show how much Apps Script has improved (or otherewise) over the years.
We recently highlighted how you could preview excerpts from Bruce Mcpherson’s ‘Going GAS’ book. Bruce has now followed up by using the book contents from 2016 to highlight differences in the current version of Google Apps Script.
Whether you’re moving from Microsoft Office to Google Docs or simply want to learn how to automate Docs with Google Apps Script, this practical guide shows you by example how to work with each of the major Apps Script services.
Bruce Mcpherson the author of Going Gas has recently highlighted in the Google Apps Script Community that a chuck of his book is currently available for free in the Google Books preview (133 of the 456 pages are available). Bruce highlights that since publication in 2016 there have been a number of changes in Google Apps Script most notably the move to modern JavaScript syntax in the V8 engine, but much of the content may still be of interest.
You’ve written a great Apps Script library and you want to know how many people are using it, and perhaps even which parts of the library they are using, and how often. Perhaps you have a new version out, and need to know whether anybody is still using the old version. You may also have a set of libraries and you want to know whether users have flipped to the new version. All these things are important to know, but impossible to find out using the Apps script platform as is.
Bruce Mcpherson provides a detailed solution for tracking Google Apps Script library usage using the Properties Service. The source post contains everything you need to integrate this solution into your existing libraries as well as a quick way to chart usage.
Use Github as a back end for caching large objects across platforms. It’s possible to use a github repo as the back end for a caching service, and by taking this approach we can share data between Apps script and multiple platforms. It also means you can retrieve the data with the git CLI in addition. It works in exactly the same way as all the other backends. … Using a regular git Repo means you can make it private and share it using the tools already built into Github.
I’m a great fan of both Redis and GraphQL. You’ll find plenty of articles about them around on this site. Although I’ve showed many examples of GraphQL and Apps Script, it was never possible to connect up Apps Script to Redis, because redis doesn’t use HTTP to communicate between Client and Server. I’ve come across upstash.com (with a free tier), that fronts a redis database with a GraphQL API.
The upstash.com service looks like a nice find from Bruce Mcpherson and his post details how you can use this for cross platform caching.
This library used to be part of my cUseful collection, but I’ve decided to pull out into a library in it’s own right. The idea is not only to be able to squeeze more into cache by compression, but also to spread across multiple cache entries. In addition, through the use of plugins, it also allows multiple backend cache stores, all accessed the same way, with the option of creating additional ones. This abstraction allows you to switch platforms as you outgrow them without any main code changes.
