Как писать в электронную таблицу Google из Excel 2003 VBA

Вам не нужен OAuth или API электронных таблиц. Таблица Google позволяет вводить данные в простой форме, что также означает, что HTTP POST сделает свое дело. Вам просто нужно подготовить электронную таблицу для приема данных, вводимых через форму, как показано ниже:

• Войдите в свою учетную запись Google Docs.
• Создайте электронную таблицу или откройте существующую
• Нажмите Инструменты / Создать форму.
• Добавьте что-нибудь в описание формы, чтобы активировать кнопку Сохранить
• Сохраните форму
• Скопируйте значение ключа формы, отображаемое в ссылке внизу страницы создания формы.
• Теперь вы можете отправить простой пост в электронную таблицу без OAuth.

Теперь вы можете протестировать запись с помощью 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"

Затем мы пытаемся выполнить форму POST из нашего листа Excel с помощью следующего кода. Добавьте ссылку на "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

надеюсь, это поможет

Я начал отвечать на ваш вопрос, но понял, что это было гораздо менее тривиально, чем я думал, когда начал играть с OAuth 2.0 API. Я думаю, было бы намного проще, если бы вы могли сделать свою электронную таблицу Google общедоступной, но я сомневаюсь, что это целесообразно с данными о продажах.

Причина, по которой это нетривиально, - это часть аутентификации. Приведенный ниже ASP OAuth, вероятно, можно использовать для некоторой работы, но я заметил, что он использует переменные сеанса и некоторые другие объекты ASP, поэтому вам придется много настраивать.

В этом свете вот мой первоначальный ответ, если он поможет.

Существует API таблиц Google: https://developers.google.com/google-apps/spreadsheets/#adding_a_list_row

Ссылка OAuth 2.0, на которую ссылаются документы электронной таблицы, устарела. Вы можете поиграть с запросами OAuth здесь, что должно помочь вам начать работу.

Функции API вызываются запросами GET / POST с XML, которые можно вызывать с помощью объекта XMLHTTP.

Сначала укажите Microsoft XML в своем проекте Excel (Инструменты-> Ссылки-> Microsoft XML, v6.0)

В вашем VBA вы, по сути, используете следующее для отправки XML-запросов:

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

Вы должны уметь адаптировать эту библиотеку ASP OAuth 2.0 для своего кода VBA.

Это пример ASP использования этой библиотеки OAuth; Опять же, поскольку и ASP, и VBA используют синтаксис VBScript, его, вероятно, можно было бы адаптировать.

Я провел последние пару дней, пытаясь найти хорошее простое решение этой проблемы.

Ни один из них, похоже, не работал для меня, поэтому мне пришлось использовать фрагменты из различных сообщений, которые я нашел в Интернете.

Что нужно сделать:

• Создайте электронную таблицу Google
• Создайте форму (в разделе «Инструменты» - «Создать форму») с тремя полями (вы можете добавить поля позже после тестирования)

Вернитесь к электронной таблице Google, и она должна была создать новый лист (названный «Ответы на форму») с заголовками, соответствующими именам полей, указанным при настройке формы. Столбец A автоматически будет иметь заголовок «Отметка времени» с вашими именами полей в качестве заголовков для столбцов B, C и D и т. Д.

• Теперь нажмите «Форма» - «Перейти к живой форме».
• Удерживая нажатой клавишу «Запись», нажмите «Проверить» (Ctrl-Shift-I) и нажмите «Сеть».
• Введите данные (что угодно) в каждое из соответствующих полей и нажмите «Отправить».
• Щелкните «Заголовки». Щелкните «formResponse».
• В разделе «Общие» скопируйте URL-адрес формы. Отметьте «?» является обязательным в конце URL-адреса.
  Также под заголовками найдите «Данные формы». Это будет использоваться в MyURL. Найдите запись 123456789 для каждого поля. Эти числа понадобятся вам для каждого поля.
• Замените xxxxxxxx в своем коде соответствующими числами вашей формы.
• Откройте электронную таблицу Excel и скопируйте ее в подпрограмме, показанной ниже.
• В редакторе VBA нажмите Инструменты - Ссылки, выберите Microsoft XML.v3.0, (Основные службы Microsoft XML).

Код:

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

Запустите этот макрос и посмотрите, как ваши данные из строки 2 появятся в таблице Google.

• Вместо использования VBA в Excel используйте полноценное приложение VB.NET с COM-пакетом Excel Interop. Его синтаксис во многом аналогичен командам VBA, но перенос документов в Google будет намного проще в VB.NET.

• Используйте API списка документов Google, чтобы создать новую электронную таблицу.

• Используйте Google Spreadsheets API, чтобы переместить данные в электронную таблицу в Интернете.

Оба API Google являются REST API, поэтому вам придется использовать объекты HttpRequest в VB.NET. Вот отличный пример того, как их использовать, просто измените URL-адреса, чтобы они подходили для Google. Google Spreadsheets даже предлагает библиотеку, которая абстрагирует многие из этих шагов.