如何从 Excel 2003 VBA 写入“Google 电子表格”

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

提问by Diego Castro

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

我有一个Excel 2003文件，其中一行类似于：

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

我需要单击“按钮”并将该行添加为Google 电子表格上的最后一行

Similar to:

相似：

Is it possible?

是否可以？

Should I use the command-line Google tools?

我应该使用命令行 Google 工具吗？

Is there a better way? Easier way?

有没有更好的办法？更简单的方法？

How would you do it?

你会怎么做？

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

（一旦我知道如何将VBA 中的“东西”添加到Google Docs 中，我该如何将其添加到最后一行？）

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.

更多信息：我有一个Excel 2003“程序”，可以保存公司的所有销售额（包含客户信息），我想做一个全局地址簿，我的（非它）同事可以轻松更新。

回答by ChrLipp

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:

您不需要 OAuth 或电子表格 API。Google 电子表格允许使用简单的表单输入数据，这也意味着 HTTP POST 可以解决问题。您只需要准备电子表格以通过以下表单接受数据条目：

• 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

• 登录您的 Google 文档帐户
• 创建电子表格或打开现有电子表格
• 单击工具/创建表单
• 在表单描述中添加任何内容只是为了启用“保存”按钮
• 保存表格
• 复制表单创建页面底部链接中显示的formkey值
• 现在您可以在没有 OAuth 的情况下在电子表格中发布一个简单的帖子

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

如果您的系统上有它，您现在可以使用curl测试该条目（用表格中的 formkey 替换 formkey 占位符）：

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.

接下来，我们尝试通过以下代码从我们的 Excel 工作表执行表单 POST。之前添加对“Microsoft XML，v3.0”的引用。用您想要的值替换 column1。

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

希望有帮助

回答by transistor1

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.

我已经开始回答你的问题，但意识到这比我开始使用OAuth 2.0 API时想象的要简单得多。我认为如果您可以公开您的 Google 电子表格会容易得多，但我怀疑这对销售数据是否可取。

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.

这很重要的原因是身份验证部分。下面的 ASP OAuth 可能适用于某些工作，但我注意到它使用 Session 变量和其他一些 ASP 对象，因此您必须进行大量调整。

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

有一个谷歌电子表格 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.

电子表格文档引用的 OAuth 2.0 链接已过时。您可以在此处处理OAuth 请求，这应该可以帮助您入门。

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

API 函数由带有 XML 的 GET/POST 请求调用，您可以使用 XMLHTTP 对象调用它。

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

首先，在你的 Excel 项目中引用 Microsoft XML (Tools->References->Microsoft XML, v6.0)

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

在您的 VBA 中，您基本上使用以下内容发送 XML 请求：

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 libraryfor your VBA code.

您应该能够为您的 VBA 代码调整这个 OAuth 2.0 ASP 库。

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

这是一个如何使用 OAuth 库的 ASP 示例；再次因为 ASP 和 VBA 都使用 VBScript 语法，它可能会被调整。

回答by transistor1

• 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 APIto create a new spreadsheet.

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

• 不要在 Excel 中使用 VBA，而是使用带有 Excel Interop COM 包的成熟的 VB.NET 应用程序。它的语法在很大程度上类似于 VBA 命令，但在 VB.NET 中转换到 Google 文档会容易得多。

• 使用Google Documents List API创建新的电子表格。

• 使用Google 电子表格 API将数据移动到您的在线电子表格中。

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

这两个 Google API 都是 REST API，因此您必须在 VB.NET 中使用 HttpRequest 对象。这是如何使用它们的一个很好的示例，只需更改 URL 以使其适合 Google。Google Spreadsheets 甚至提供了一个库，可以抽象出其中的许多步骤。

回答by Peter

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)

• 创建 Google 电子表格
• 创建一个包含三个字段的表单（在“工具”-“创建表单”下）（您可以稍后在测试后添加字段）

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.

返回到 Google 电子表格，它应该创建了一个新工作表（称为“表单响应”），其标题与设置表单时给出的字段名称相匹配。A 列将自动作为标题“时间戳”，您的字段名称将作为 B、C 和 D 列等的标题。

• 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 – Referencesselect the Microsoft XML.v3.0, (Microsoft XML Core Services)

• 现在点击“表格”-“转到实时表格”
• 写单击并“检查”（Ctrl-Shift-I）并单击网络
• 为每个相应字段输入一些数据（任何内容），然后单击“提交”
• 单击“标题” 单击“formResponse”。
• 在“常规”下复制表格的网址。注意一个“？” 需要在 url 的末尾。
  同样在标题下找到“表单数据”。这将用于在 MyURL 中查找每个字段的 entry.123456789。每个字段都需要这些数字。
• 将代码中的 xxxxxxxx 替换为表单的相应编号。
• 打开您的 Excel 电子表格并复制到如下所示的子程序中
• 在 VBA 编辑器中单击工具 - 参考选择 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

运行此宏并观察第 2 行的数据出现在 Google 电子表格中