The Sheets V4 API is a very nice piece of work that exposes almost all of the Sheets object model in a REST API. Here's a VBA wrapper for that API. I'll add to it over time, but this version allows you to do this - in just a few lines of VBA.
Let's get straight down to some examples. For the purposes of demo, I've made a sample workbook - sheetsAPI.xlsm - which you can get from the downloads page, under the data manipulation folder.
It has two buttons - one to get this sheet from google, and another to send it there.
You can pass the name of the sheet (or a list of sheets separated by commas) to pull the data from Google. Here' im getting the activesheet.
And the same principle to push to google
The sheet ID is the Google Drive ID of the workbook to write or read to. In my example, I'm simply returning a fixed id for a test spreadsheet. You may want to implement a form or a picker to select from multiple sheets. In any case you'll need to change this to whatever your own sheet id is.
People usually find OAUTH2 a little scary. I've tried to simplify it as much as possible. Here's what you need to do.
Enable this API
Create a new oauth2 client ID
Run the one off function, and you'll get something like this
And that's it - you can use the example once you create the Google Sheet you want to play with and enter its id. You delete the once off function now if you like.
This will work on Windows 10 with Excel 2016. It will probably also work with older versions of both Windows and Excel too. It will need a few tweaks to work on Excel for Mac. If anybody would like to help with a Mac version then please ping me I can point you in the correct version.
Some older installations have missing modern DLL versions, or some objects are blocked by policy. I've added some workarounds to to use different objects for external access from excel, as they have changed over time. If you get an error about xmlhttp, then if you can find out which xmlhttp object you do have installed, or is not blocked, then you can extend the function below (in the cBrowser class), to use it instead. If you do, then please leave feedback on the community so it can be implemented for others too, so more versions can be covered.
I'll be adding to this api over time to be able to further collaborate between the two platforms.
Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Integrating Apps with other platforms >