REST access to list management cloud based functions – plus a VBA example

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,

https://script.google.com/a/macros/mcpher.com/s/AKfycbzhzIDmgY9BNeBu87puxMVUlMkJ4UkD_Yvjdt5MhOxR1R6RG88/exec?type=jsonp&source=scriptdb&module=blister&library=blister&query={“package”:{“name”:”billboardhot100″}}

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

All these custom functions are accessible through this Google Apps Script Webapp endpoint, applying these parameters

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

Now with this general purpose custom function executor, we can delegate the work to Google Apps Script, and Excel’s only function is to unravel the web response. We could easily create custom functions to mimic that, but do we really want to recalculate lookups and so in with a web access in an excel function ? In my next post we’ll see how that went.
For all the details on how to get the Google Apps Script code and Excel examples, see Excel Liberation.

For more stuff like this see Excel Liberation

 

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.