AppsScriptPulse

SQL for Apps Script – it’s here – Desktop Liberation

alaa kaddour, CC BY-SA 4.0, via Wikimedia Commons

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.

Source: SQL for Apps Script – it’s here – Desktop Liberation

Sheets Workbook functions in Apps Script – Date and Time and Temporal primer – Desktop Liberation

Timezones

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.

Source: Sheets Workbook functions in Apps Script – Date and Time and Temporal primer – Desktop Liberation

Google Apps Script – What a difference 5 years make – Episode 2 [Property Service and Spreadsheet Service updates] – Desktop Liberation

Image credit: Bruce Mcpherson

Image credit: Bruce Mcpherson

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!

Source: Google Apps Script – What a difference 5 years make – Episode 2 – Desktop Liberation

Random and fake test data in Sheets with Google Apps Script – Desktop Liberation

Image credit: Bruce Mcpherson

Image credit: Bruce Mcpherson

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.

Source: Random and fake test data in Sheets with Google Apps Script – Desktop Liberation

Google Apps Script – What a difference 5 years make – Desktop Liberation

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.

Source: Google Apps Script – What a difference 5 years make – Desktop Liberation

Apps Script – How to track library and script usage – Desktop Liberation

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.

Source: Apps Script – How to track library and script usage – Desktop Liberation

Github as an Apps Script cache platform – Desktop Liberation

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.

Source: Github as an Apps Script cache platform – Desktop Liberation

3 Favourite things in one article – Apps Script, Redis and GraphQL – Desktop Liberation

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.

Source: 3 Favourite things in one article – redis, apps script and graphQl – Desktop Liberation

Apps script library with plugins for multiple backend cache platforms – Desktop Liberation

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.

Source: Apps script library with plugins for multiple backend cache platforms – Desktop Liberation

Every Google Apps Script project on Github visualized – Desktop Liberation

Visualization of apps script projects

There are so many Apps Script projects out there where the source code is published on Github, but it’s hard to find what you want. Whether it’s a library, an example of an add-on, how to use an advanced service, or just see who is working on what. I figured it would be nice if we had a searchable visualization of everything that’s public.

Source: Every Google Apps Script project on Github visualized – Desktop Liberation

Goa v8 changes and enhancements for OAuth2 and Google Apps Script

Goa tutorial

v8 and other htmlservice changes meant I had to make a few small changes to cGoa. The good news it’s easier to use than ever, and supports a few new services too. It’s best to look at the service list on github, as that’ll be kept up to date. Here’s a reminder of how to use it.

There are a couple of OAuth 2 Google Apps Script libraries out there but cGoa from Bruce Mcpherson is the easiest one I used, particularly, when it comes to setup. Bruce’s post has more details including various ways you can use the library.

Source: Goa v8 changes and enhancements

Apps Script V8: Keystore for global space – Desktop liberation

One of the challenges with V8 compared to Rhino is that you can’t be sure of the order of global variable initialization. Personally this is not an issue for me, as I avoid any such use of global space, but it is an issue for some. A nice way of dealing with global space is to use namespaces and IEF as I described in Apps Script V8: Multiple script files, classes and namespaces but another, perhaps less obvious way, is put all the variables (and even functions) you need to access from multiple functions in a keystore.

Interesting solution for storing/retrieving variables, particularly when you are using the new V8 runtime.

Source: Apps Script V8: Keystore for global space – Desktop liberation

Apps Script V8: Multiple script files and namespaces – Desktop liberation

Apps Script doesn’t have a module loader system. If you’re used to developing in NodeJs, you’ll also be familiar with importing and exporting to include required modules in your project. With Apps Script, you have no control over the order in which multiple script files are executed. In Legacy Apps Script, there seemed to be some kind of workaround going on so that global statements were executed in a sensible order (I don’t know the details), but in V8 this is not the case. …

My golden rules are

  • Nothing executable should be in global space
  • Don’t rely on the order that things are processed
  • Minimize the number of executable functions (1 is good)
  • Always assume your code will be reused somewhere else.

Source: Apps Script V8: Multiple script files and namespaces – Desktop liberation

Apps Script V8: spreading and destructuring – Desktop liberation

V8 adds destructuring from  JavaScript ES6. Legacy Apps Script already had destructuring of arrays added fairly recently, but v8 gives full a destructuring capabilitity. These destructuring and spreading capabilities, which at first may again seem a little like syntactic sugar, have contributed greatly to  the development of state management frameworks such as Vuex and Redux for client side apps. V8 brings some of that cleanliness to Apps Script.

Source: Apps Script V8: spreading and destructuring – Desktop liberation

JavaScript V8 Arrow functions, this and that – Desktop liberation

V8 adds the arrow function declarator from modern JavaScript. This is a handy shorthand but it’s more than just that. There are some behavioral differences too that you’ll need to understand before diving in. The old way of declaring functions still exists of course, and there will always be a need for it.

More tips and guidance from Bruce Mcpherson for developers migrating code to the Apps Script V8 runtime.

Source: JavaScript V8 Arrow functions, this and that – Desktop liberation

Apps Script V8: Maps and Sets – Desktop liberation

Sets and Maps can often be a cleaner way of storing data than using Objects or Arrays, even though at first glance they may seem a little redundant. Unlike an array, they are aware of what else is in the map or set (so you can avoid duplicates), and unlike an object, you can use anything as the key – including the item value itself

Given many Apps Script projects focus on manipulating data Bruce Mcpherson provides a useful introduction to Maps and Sets.

Source: Apps Script V8: Maps and Sets – Desktop liberation

Apps Script V8: Template literals – Desktop liberation

It’s a shorthand way of using a template into which variables are subsituted in a string. This allows for better reuse of string structures, and a few other goodies besides (like all V8 additions, it’s more than just a syntactical spruce up)

Bruce Mcpherson has a very useful post highlighting the benefits of using the new V8 runtime when working with string output

Source: Apps Script V8: Template literals – Desktop liberation

Google Apps Script V8 variable scopes – var, const and let – Desktop liberation

One of the key things that V8 has sorted out is the scope of variables. Using var to declare variables meant that anything declared within the scope of a function could easily be accidentally overwritten, causing hard to track down errors. ES6 (since it’s commonly known as V8 in Apps Script – I’ll be referring to it as V8 from now on), has added const and let to the variable declaration vocabulary to help prevent these kind of problems.

With the introduction to the new Google Apps Script V8 runtime new opportunities are available for writing clean and robust code in the script editor using modern JavaScript syntax. In this post from Bruce Mcpherson you can learn some of the basics of var , const and let .

Source: JavaScript V8 variable scopes – Desktop liberation

Roughly matching text – Desktop Liberation

When dealing with matching in sheets, you sometimes need to get close matches. This post shares the “Rough” namespace of the cUseful library

Source: Roughly matching text – Desktop Liberation

Google Drive as cache – Desktop Liberation

If you are after performance and self cleansing then the CacheService is the best solution for caching, and if you are after permanence and small amounts of data, the properties service is a good solution. You can also get round many of the limitations of each service using my properties and cache plugins, covered in Squeezing more into (and getting more out of) Cache services

Source: Google Drive as cache – Desktop Liberation

Subscribe to Apps Script Pulse...