Here's where it gets really interesting. Let's say that we need to get some large number of spreadsheets from Google Docs. We want to do it asynchronously, and we also know that google docs will fail if it receives to many requests, or it's too busy. So we have to orchestrate and deal with all of the following.
All code mentioned is in the promises.xlsm workbook, which you can download here.
I've generalized this so it can be used to retrieve any Google Spreadsheet. Here's the shell. You can substitute in the key for any Google Spreadsheet. The only thing of consequence here is that the import process is kicked off asynchronously, and control will immediately be returned to the caller, which will then exit.
NOTE: with the new Google Sheets, this method has been largely deprecated. There is a complete revamp of this here Get Data From Google Docs. You can also check out the Sheets apps from Apps built with data abstraction, which can both read and write from Excel to Sheets, but this post may be useful since it is about asynchronicity more than the utility of the app itself.
Here's the code that kicks everything off. Let's walk through
When executing this asynchronously, one of the issues is to stop things disappearing from memory when the module that initiates them goes out of scope. One way is create a tangle of public variables - but that will be hard to manage, even harder to clear out, and impossible when the task is data driven as it is in this case. The promise framework maintains a register of objects that are going to be needed later, and keeps them in memory. This simply clears out any hanging around from previous asynch sessions. It should be the first thing called.
The reason we use a promise here is that next we'll want to retrieve each of the spreadsheets from withing the Google Workbook. However we don't want to start doing that until both the schema has finished being retrieved and the old sheets have been deleted. It is almost certain that the sheet deletion would be finished being executed before the Schema was returned anyway, but we can use both promises now before continuing.
When actually returns a promise. This means that you can use .done() and .fail(). All the promises need to be successful for when().done() to execute. If any one fails, then when().fail() will be executed instead. The promises to be evaluated are presented as an array(promise1,promise2,...promisen). When() will receive a signal when each of those promises is either resolved or rejected. In our case we want both of array(prs, doneSchema.promise()) to be successfully resolved before continuing. We'll also need another promise. This time to signal that we've got all the data. process.getSheets() will resolve (or reject) doneSheets when all the data has been retrieved and the sheets populated. Aside from taking multiple promises, When() will pass on the data from the last promise in the array - in this case the data that was signalled by doneScheme.resolve(data).
Since the procedure will have long exited by now, its up to you to clean up the objects that will have been forced to remain in memory beyond their regular scope. We can detect when everything is completed either by using When() . done() or fail(). In our case, we know that doneSheets.promise() could only have been resolved if everything else had been done, so we can simply test for the doneSheets.promise().done() signal
All the hard work interpreting google docs formats is done by calling back various methods in this class. There's nothing much new in here, json and google docs importing having been covered elsewhere in this site, but here's how it all fits together, and how the promises are rejected or resolved.
This will be called when it's time to extract the schema from the data returned by the cBackoff class. Every method that is likely to be called by a .done() or a .fail() should have the same argument list.
a As Variant - this is the data that will have been signalled in .resolve(array(data)) and will be an array of whatever
Optional defer As cDeferred - this can be used to signal completion of this task through a promise
Optional args As Variant - this is another array that this time can be passed via .done()
The json of the schema get parsed and creates a cJobject . We register it so that it doesnt slip out of memory, but also so that it can be easily cleaned up later (cjobject has a teardown class that will be executed by register.teardown()). Finally we resolve the work and pass on the cJobject containing the schema data.
Once getSchema (and deleteallthesheets) is resolved, this will be called to go and get each of the sheets referenced in the schema. Much f the code here is simply about converting google wire protocol. The interesting part is this section....
Each sheet is retrieved asynchronously using a cBackoff, and its promise is stored in an array of promises. On each resolution, the storeData() method will be called to move the data into the Excel sheet. So there are a whole stream of data fetches and data stores all happening at the same time.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Classes > Promises in VBA >