Google Docs to Excel Asynchronously

I’ve been playing around with creating a promise based framework for VBA for a while now, mainly just to see if it was possible. Here’s a practical implementation that gets the data from a large number of google spreadsheets into Excel, all at the same time.

Here’s some of the objectives

  • Orchestrate asynchronous and timer actions using a similar approach to jQuery promises
  • Pure VBA only (although I do use a couple of Windows API)
  • Simple to use (although difficult to grasp at first as it turns out)

A different approach. 

Javascript developers, who are used to callbacks, timeouts and even promises, will find all this pretty straightforward. VBA developers, more used to a sequential approach will have some initial conceptual problems. In fact, it doesn’t look a lot like VBA anymore aside from syntactically and most of the code is about preparing for handling things that will happen eventually.

An example 

In this example we’ll get some data from a URL asynchronously, and store it in a cell. Behind the scenes, the data retrieval is kicked off and control is handed back to Excel right away. When the data arrives it will populate the cell and clean up the memory it was hanging on to.

Private Sub smallTest()
    ‘ you have to add your callbacks in a class as below
    Dim callbacks As yourCallbacks, load As cPromise
   
    ‘ in case anything old async remnants are hanging around
    clearRegister
   
    ‘ whatever you will be calling back should be defined here
    Set callbacks = New yourCallbacks
   
    ‘ add a playing worksheets if not already there
    addPlaySheets Array(“rest”)
   
    ‘ we’ll pass the range so that it gets carried forward when resolved
    Set load = loadData(“https://ajax.googleapis.com/ajax/services/search/patent?v=1.0&rsz=8&q=mouse”, Range(“rest!a1”))
   
    ‘ and then do these things when it’s all over
    load.done(callbacks, “populate”) _
        .done(register, “tearDown”) _
        .fail callbacks, “show”


    ‘ when we get here, all the previous activity is still probably going on.
    Debug.Print “i could be doing something else while Im waiting”
   
End Sub

Something more complicated

This time we’ll go to Google Spreadsheets, read the schema of a workbook, then retrieve each of the worksheets in that workbook at the same time. Many web services, Google Docs included, will fail if too many requests come at once or if they are busy. So in addition, exponential backoff is also implemented. All this is orchestrated through promises. Here is the calling procedure.

Option Explicit
‘ example application using promises to get multiple data streams from google docs
Public Sub asynchDocs()
    Dim url As String, key As String
    key = “0At2ExLh4POiZdFd0YUhpZVRPUGxFcW85X2xkMm1vY2c”
    url = “https://spreadsheets.google.com/feeds/worksheets/” + key + “/public/basic?alt=json”
    
    ‘ kick this off in the background
    doDocsWithBackoff url
    ‘
    ‘ we can go and do something else now
    Debug.Print “Im doing something else – have a nice day”
    
End Sub
Public Sub doDocsWithBackoff(url As String)
    Dim b As cBackoff, p As cPromise, prs As cPromise, _
        process As cDocsImporter, doneSchema As cDeferred, doneSheets As cDeferred
        
    ‘ always to this for a fresh start – it will clear up any memory/timers from previously
    clearRegister
    
    ‘ this class knows how to get google spreadsheet
    Set process = New cDocsImporter
    Set doneSchema = New cDeferred
    
    ‘ first phase, we get the schema, and release up memory
    Set b = New cBackoff
    b.execute(url) _
        .done(process, “getSchema”, doneSchema) _
        .fail process, “show”
    
    ‘ although this will be done synchronously, using a promise means we can know its done
    Set prs = deleteAllTheSheets
    
    ‘ we’ll have resolved doneSchema when the previous .done is executed
    ‘ we’ll need another deferred to tell us when all this is over
    Set doneSheets = New cDeferred
    when(Array(prs, doneSchema.promise)) _
        .done(process, “getSheets”, doneSheets, url) _
        .fail process, “show”


    ‘ now we can clear everything up
    doneSheets.promise _
        .done(register, “teardown”) _
        .done(process, “teardown”) _
        .fail process, “show”


End Sub

More information

For more information on how this is implemented, or to download the workbook see Excel Liberation. This is fairly extreme VBA so this early version will be a little fragile and buggy Check back often for new versions.

Plugins classes

So far I’ve created event driven timers, getting data from a web service and asynchronous ADO operations. I may add others over time, and welcome any contributions you make. The details of how to create these and the code for the promises structure can also be found on the Excel Liberation site.

For more stuff like this see Excel Liberation

(function() {
var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true;
po.src = ‘https://apis.google.com/js/plusone.js’;
var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s);
})();

About brucemcp 224 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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*