There are quite a few exchange rate APIs out there to get the latest and historic exchange rates. Google used to have a nice, simple and free one – the finance API, but that was […]
This article will cover the translation of the Sheets workbook database type functions for Apps Script. All of them will be covered. Motivation If you are handling data from sheets it might be useful to […]
In Every Google Apps Script project on Github visualized I introduced Vizzy which uses GitHub data as its source. That same data package is available publicly in case you want to build something on top of […]
Create beautiful Apps Script add-ons with Vuetify and Vue.js The idea of ‘reactive programming’ has been around for many years, but frameworks such as React, Vue and so on have moved it into the mainstream. […]
Looking into how various operations perform as the size of the sheet increase provided some interesting data. The objective is to see whether the time to execute particular operations is directly proportional to the size […]
In Google Oauth2 VBA authentication I showed how to use this class. All associated libraries are available in the cDataSet.xlsm library downloadable here, or through gistthat, as described here. You’ll need the cRest module. Examples […]
Data from sheets is organized as an array of rows of arrays of column values. Sometimes that’s not convenient and prevents you from effectively using the useful mapping array methods that I covered in Highlight […]
I covered this topic in Highlight duplicate rows in a sheet – map, filter and every, so now we’ll build on that a little and introduce the .reduce Array function. As before the problem is […]
Someone asked this question on the forum the other day, so I thought it would make a good example of how to use Array functions to simplify looking at sheet data. I normally would use […]
In Apps Script, it’s easy var timeStamp = new Date().getTime(); var date = new Date(timestamp); But sheets doesn’t use JavaScript timestamps for dates, it uses the number of days since 1st Jan 1900, with hours/mins/secs […]
Excel Tables are a nice feature. They size automatically and they are easy to reference. Google Sheets doesn’t have that and it can be difficult to find data on a sheet, where there are blank […]
You all know that trying to minimize calls to the spreadsheet API from Apps Script can dramatically speed things up. Let’s take a look at hiding and showing rows and columns, where we’re starting from […]
The Itunes API is very nice. You can find details on it at ITunes API I thought I may as well add an Apps Script wrapper library to it, since it can provide a wealth […]
Sometimes you need to fill a range in a sheet, either with a single value, or with some calculated value. It’s a pretty straightforward pattern, but if you are an Excel user, you’ll be used […]
This is a very small Apps Script snippet to generate column addresses from column numbers that can be useful in things like Sheet Addons. For example 1 gives A, 27 – AA, 703 – AAA […]
In Using a cross filter with Google Apps Script I showed how to use the powerful cross filter library to do some data wrangling. But using some of JavaScript’s Advanced Array functions, and working with […]
This is a repoduction of the tutorial, which can be found at https://tryretool.com/blog/google-sheets-app-tutorial/ Access the tutorial and documentation. Contents: Background Live demo Reading data from Google Sheets Displaying data in a table Creating a form […]
It is all about Google Wire Connect to Google Docs Get live data into Excel Use data from Gadgets NOTE: with the new Google Sheets, this method has been largely deprecated. There is a complete […]
Why so slow? Note – this article was written when Apps Script first came out. Since that time, the speed of Sheets access has increased significantly, but I leave this article here for interest. The Data […]
What can you learn here? Using cDataSet Using cRest Using cJobject Putting the Rest response on the sheets get it now Now that we have the tools to generate existing or adhoc rest queries, let’s look at how the data […]
Installable triggers and unpublished add-ons As described in Setting up onChange trigger and When test add-ons doesn’t work you can’t install a trigger in test mode. The solution is to use your add-on as a library to a container […]
When you’re ready to start watching for changes, the server will emulate a change to get the whole ball rolling. Here’s the Server.init code, which will provoke a push back to the client with the […]
Demo Here’s a video showing the final proof of concept of using fisheye distortion to navigate around a google sheet Implementation The principle of this kind of projection is to enlarge items closer to the […]
Demo This demo is a proof of concept for Ephemeral exchange push notification. The code is on github. Here’s a video showing the capabilities. Continue Reading about Pushing changes from Google Sheets server to client […]
Detecting changes from an add-on sidebar or dialog You’ll find various techniques for this around this site, but they are all based on polling. This means that the client app running in the browser occasionally […]
Sheets Developer Meta Data – Apps Script advanced service, shows how to use the Sheets Advanced service to work with Sheets DeveloperMetada, and since the resources from this service are a little complicated you may […]
Recently, developer metadata was added to the capabilities of the Sheets V4 API, as described here Sheets API – Developer Metadata (which used the API directly). If you don’t know about Metadata, take a look at that […]
Sheets Developer Meta Data – Apps Script advanced service, shows how to used the Sheets Advanced service to work with Sheets DeveloperMetada. This post goes a little further and adds a library to simplify access […]
In December, Google released new Sheets, saying they are faster and better than the old – including working offline. I’ve made a couple of tests to compare the old and the new – a simple operation […]
If you use blogger.com you may want to get some data about your posts into a spreadsheet for analysis. For example, the d3 concept browser analyses the Excel Liberation site and blog each night. There […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines