In Google Apps Script lists and validation I showed how to use custom functions from Google Spreadsheets to perform common spreadsheet tasks like lookup, index, filter, sort, match and apply dataValidation using cloud based lists.
Of course this data, and the functions behind them, can also be accessed by a rest query, returning some JSON for the results, and used by anything that can deal with JSON. In this example, I’ll show how to use these functions to get selected data into Excel. Using this method, you can use the same single list across multiple platforms.
Here’s a slide primer on these functions in case you’re not familiar with them.
Google Apps ScriptDb
The data for these lists are stored in one or more ScriptDBs. We’ll look at 2 ways of getting data out of these lists.
As a noSQL query
In Using ScriptDB as a noSQL store, I showed how to generally get data from scriptDb as a restquery. We can use this to pick up the data in these lists, but this just gives the plain data. For example,
Will get the list named billboardhot100, from the library named blister, looking like this live query.
Using the rest to Excel library, we can easily get this data into an excel table, using this general purpose example. This gives us a table that looks like this.
However, it would be better to get access to the custom function capabilities.
Access custom functions as REST queries
https://script.google.com/macros/s/AKfycbzBskBK17poScDU9yHnfgmgPHyvgNejM3zxV7niGdhLeXPjw7Y4/exec
Let’s say we want the name of the top 10 in the billboardhot100. (this is a list that automatically updates every day through a Google Apps Script timed trigger).
This query will get it
?func=blisterList&listName=blister.billboardhot100&maxMatch=10&sortId=rank_this_week&listId=title
We’ve already set up a rest library entry in VBA that looks like this
With .add("blisterFunctions") .add "restType", erRestType.erSingleQuery .add "url", "https://script.google.com/macros/s/AKfycbzBskBK17poScDU9yHnfgmgPHyvgNejM3zxV7niGdhLeXPjw7Y4/exec" .add "results", "" .add "treeSearch", False .add "ignore", vbNullString End With
And this little piece of code can be used for any custom function query to populate an excel sheet
Public Sub testBlisterFunction() Dim q As String, joc As cJobject, job As cJobject ' change this to the appropriate query q = "?func=blisterList&listName=blister.billboardhot100&maxMatch=10&sortId=sequence&listId=title" ' exceute the query, unravel the JSON, and populate the sheet With restQuery("blisterFunctions", "blisterFunctions", , _ , , , , False, , , True, , , , , , q) For Each job In .jObject.children For Each joc In job.children .dset.headingRow.where.Resize(1, 1) _ .Offset(job.childIndex - 1, joc.childIndex - 1).value = joc.value Next joc Next job .tearDown End With End Sub
giving this result
Implementing as custom functions in Excel
For more stuff like this see Excel Liberation