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.
 

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.

 

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

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


*


sixteen − 11 =