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.

Answer 3

I spent the last couple of days trying to find a good easy solution to this problem.

None seemed to work for me so I had to utilize bits and pieces from various posts I found on-line.

Steps to follow:

• Create a Google spreadsheet
• Create a form (under “Tools” – “Create a form”) with a three fields (you can add fields later after testing)

Go back to the Google spreadsheet and it should have created a new sheet (called "Form Responses") with headings matching the field names given when setting up the form. Column A will automatically have as a heading “Timestamp” with your field names as headings for columns B, C & D etc.

• Now click on “Form” – “Go to Live Form”
• Write-click and “Inspect” (Ctrl-Shift-I) and click on Network
• Enter some data (anything) for each of the respective fields and click on “Submit”
• Click on “Headers” Click on “formResponse”.
• Under “General” copy the url of the form. Note a “?” is required at the end of the url.
  Also under headers find “Form Data”. This will be used in MyURL Look for the entry.123456789 for each of the fields. You will need these numbers for each field.
• Replace xxxxxxxx in your code with your form's respective numbers.
• Open up your Excel spreadsheet and copy in the sub-routine shown below
• In the VBA editor click on Tools – References select the Microsoft XML.v3.0, (Microsoft XML Core Services)

Code:

Sub Write_Info_To_Google()
    Dim ScriptEngine
    Dim http As Object
    Dim myURL As String
    Set http = CreateObject("MSXML2.ServerXMLHTTP")
    Set ScriptEngine = CreateObject("MSScriptControl.ScriptControl")
    ScriptEngine.Language = "JScript"
    ScriptEngine.AddCode "function encode(str) {return encodeURIComponent(str);}"

    myURL = "https://docs.google.com/forms/d/e/ . . . . /formResponse?" & _
            "entry.xxxxxxxxx=" + Cells(2, 1).Text + _
            "&entry.yyyyyyyyy=" + Cells(2, 2).Text + _
            "&entry.zzzzzzzzz=" + Cells(2, 3).Text
    http.Open "GET", myURL, False
    http.setRequestHeader "User-Agent", "Google Chrome 70.03538.102 (compatible; MSIE _ 
    6.0; Windows NT 5.0)"
    http.send
End Sub

Run this macro and watch your data from row 2 appear in the Google spreadsheet

Answer 4

• Rather than use VBA in Excel, use a full fledged VB.NET application with the Excel Interop COM package. It's syntax is largely similar to the VBA commands, but to do the transferring to Google docs will be much easier in VB.NET.

• Use the Google Documents List API to create a new spreadsheet.

• Use the Google Spreadsheets API to move the data into your online spreadsheet.

Both Google APIs are REST APIs, so you'll have to use HttpRequest objects in VB.NET. Here is a great example of how to use them, just change the URLs so they are appropriate for Google. Google Spreadsheets even offers a library that abstracts away many of those steps.