How to write to a "Google Spreadsheet" from Excel 2003 VBA

Question

I Have an Excel 2003 file with a line similar to this:

I need to click "the button" and it adds that line as the last one on a Google Spreadsheet

Similar to:

Is it possible?

Should I use the command-line Google tools?

Is there a better way? Easier way?

How would you do it?

(once I know how to add "stuff" from VBA to Google Docs, how the f do i add it to the last line?)

More info: I have an Excel 2003 "program" that saves all of the company's sales (with the customer info), and I'd like do make a global address book that's easily updated by my (non it) co-workers.

Answer 1

You don't need OAuth or the spreadsheet API. Google Spreadsheet allows data entry with a simple form, which means also that a HTTP POST will do the trick. You just need to prepare your spreadsheet to accept data entries via a form as follows:

• Login to your Google Docs account
• Create a spreadsheet or open an existing one
• Click on Tools / Create a form
• Add anything in the form description just to enable the Save button
• Save the form
• Copy the formkey value displayed in the link at the bottom of the form creation page
• Now you can issue a simple post into the spreadsheet without OAuth

You can test the entry now with curl if you have it on your system (replace the formkey placeholder with the formkey from your table):

curl.exe -v -k "http://spreadsheets.google.com/formResponse?formkey=<formkey>" -d "entry.0.single=test&entry.1.single=test2&pageNumber=0&backupCache=&submit=Submit"

Next we try to execute the form POST from our Excel sheet via the following code. Add a reference to "Microsoft XML, v3.0" before. Replace column1 with your desired values.

Dim httpRequest as XMLHTTP
Set httpRequest = New XMLHTTP
httpRequest.Open "POST", "http://spreadsheets.google.com/formResponse?formkey=<formkey>&amp;ifq", False
httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpRequest.Send "entry.0.single=" + column1 + "&entry.1.single=" + column2 + "&pageNumber=0&backupCache&submit=Submit"

'Check result in the following vars
httpRequest.status
httpRequest.statusText

Hope that helps

Answer 2

I had started answering your question, but realized that it was much less trivial than I thought it was when I started playing with the OAuth 2.0 API. I think it would be a lot easier if you could make your Google spreadsheet public, but I doubt that is advisable with sales data.

The reason this is non-trivial is the authentication part. The ASP OAuth below is probably usable with some work, but I noticed it uses Session variables and some other ASP objects, so you'd have to do a lot of tweaking.

In that light, here is my original answer, if it helps.

There is a google spreadsheet API: https://developers.google.com/google-apps/spreadsheets/#adding_a_list_row

The OAuth 2.0 link that the spreadsheet docs refer to is out-of-date. You can play with the OAuth requests here, which should help you get started.

API functions are called by GET/POST requests with XML, which you can call using the XMLHTTP object.

First, reference Microsoft XML in your Excel project (Tools->References->Microsoft XML, v6.0)

In your VBA, you essentially use the following to send XML requests:

Dim x as MSXML2.XMLHTTP
Set x = New MSXML2.XMLHTTP
x.Open "POST", "http://example.com/", False
x.Send "<xmldata></xmldata>"

You should be able to adapt this OAuth 2.0 ASP library for your VBA code.

This is an ASP example of how to use that OAuth library; again since both the ASP and the VBA are using the VBScript syntax, it could probably be adapted.