Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Заполнить google sheets данными из excel, при помощи vba - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Заполнить google sheets данными из excel, при помощи vba
Заполнить google sheets данными из excel, при помощи vba
akobir Дата: Среда, 13.12.2017, 19:20 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 201
Репутация: 9 ±
Замечаний: 0% ±

Excel 2010
Добрый день, господа!

Поиск по интернету, к сожалению, не помог.
Подскажите пожалуйста, есть ли какое-либо решение по переносу данных из excel в google sheets?
Если кто-нибудь уже решал проблему или решает сейчас — поделитесь, пожалуйста, имеющейся инфой.

Спасибо!


e-mail: akobir.ismailov@gmail.com
 
Ответить
СообщениеДобрый день, господа!

Поиск по интернету, к сожалению, не помог.
Подскажите пожалуйста, есть ли какое-либо решение по переносу данных из excel в google sheets?
Если кто-нибудь уже решал проблему или решает сейчас — поделитесь, пожалуйста, имеющейся инфой.

Спасибо!

Автор - akobir
Дата добавления - 13.12.2017 в 19:20
Hugo Дата: Среда, 13.12.2017, 20:05 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3137
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
На всякий случай: тема на гуглском форуме


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеНа всякий случай: тема на гуглском форуме

Автор - Hugo
Дата добавления - 13.12.2017 в 20:05
Gustav Дата: Четверг, 14.12.2017, 13:18 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1828
Репутация: 726 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
есть ли какое-либо решение по переносу данных из excel в google sheets?

Есть - самое простое: вручную Ctrl+C в Excel - Ctrl+V в таблице Google. И вряд ли стоит как-то автоматизировать эту процедуру, хотя при желании можно поизвращаться.

Если Вас не устраивает такой простой и естественный способ переноса, то расскажите подробнее что именно хотите делать.

И в дополнение к ссылке от Hugo - еще одна тема для размышления: http://www.excelworld.ru/forum/23-34804-1 .


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
есть ли какое-либо решение по переносу данных из excel в google sheets?

Есть - самое простое: вручную Ctrl+C в Excel - Ctrl+V в таблице Google. И вряд ли стоит как-то автоматизировать эту процедуру, хотя при желании можно поизвращаться.

Если Вас не устраивает такой простой и естественный способ переноса, то расскажите подробнее что именно хотите делать.

И в дополнение к ссылке от Hugo - еще одна тема для размышления: http://www.excelworld.ru/forum/23-34804-1 .

Автор - Gustav
Дата добавления - 14.12.2017 в 13:18
akobir Дата: Понедельник, 18.12.2017, 15:20 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 201
Репутация: 9 ±
Замечаний: 0% ±

Excel 2010
Gustav, Была сложная задача - ежедневное обновление файла на диске.
Файл (Excel) изначально собирался вручную, далее загружался в гугл.докс, к которому есть доступ у нескольких человек.

Как это сделать - разобраться не смог, решил отложить до лучших времен. :)


e-mail: akobir.ismailov@gmail.com
 
Ответить
СообщениеGustav, Была сложная задача - ежедневное обновление файла на диске.
Файл (Excel) изначально собирался вручную, далее загружался в гугл.докс, к которому есть доступ у нескольких человек.

Как это сделать - разобраться не смог, решил отложить до лучших времен. :)

Автор - akobir
Дата добавления - 18.12.2017 в 15:20
Gustav Дата: Пятница, 22.12.2017, 19:32 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1828
Репутация: 726 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Хорошая новость перед Новым годом - я почти разобрался с более-менее человеческим автоматическим экспортом данных из Excel в Google Spreadsheet. Задача решается (кто бы мог подумать! :)) с помощью HTTP-запросов методами GET и POST. При использовании GET передаваемые данные (немного) включаются текстом прямо в адресную строку браузера (в ее конец). Если же данных много, то гораздо удобнее использовать метод POST, помещая данные в так называемое "тело" запроса.

Предполагаю, что ниже я напишу несколько сообщений, освещая в каждом из них конкретный шаг общего техпроцесса экспорта из таблицы Excel в таблицу Google. И вот в этом (первом) сообщении привожу отлаженный код двух процедур на VBA для экспорта данных каждым из вышеназванных методов.


Процедура sendGET выводит некоторый текст в 3 ячейки таблицы Google - B5,B6,B7. Координаты ячеек задаются параметрами row и col (номер строки и столбца), а значение ячейки - параметром txt (это просто я так придумал в этом примере). Имена параметров отделяются от значений знаками равенства (=), а пары "переменная-значение" - знаками амперсанда (&).

Для процедуры sendPOST соблюдаются аналогичные правила относительно параметров и значений. Причем для sendPOST такой порядок "закреплен" указанием в заголовке запроса соответствующего параметра типа контента: "Content-Type", "application/x-www-form-urlencoded". Возможно использование гораздо более прогрессивных типов контента (JSON, xml), но в рамках примера ограничимся указанным простейшим типом. Обратите внимание, что синтаксис пар "переменная-значение" в нашем частном случае совпадает и для метода POST, и для метода GET, что в определенном смысле удобно в учебных целях.

В методе sendPOST заполняются ячейки таблицы Google в диапазоне из 500 строк и 10 столбцов. Заполнение происходит неким периодическим значением, генерируемым прямо в цикле. Я не стал отяжелять пример чтением передаваемых данных из ячеек таблицы Excel, чтобы не приводить саму эту таблицу. При желании каждый может самостоятельно дополнить пример чтением данных из ячеек своей собственной таблицы.

Теперь зададимся вопросом - в какой таблице Google осядут передаваемые данные? Как видно, за это отвечают две переменные: ssId - "страшный" идентификатор файла таблицы (44 символа) и sheetName - имя листа внутри файла. При использовании кириллицы в названии листа его следует подвергнуть операции шифрования при помощи приведенной функции encodeURL. То же самое при наличии кириллицы надо сделать и при передаче текстового значения ячейки.

В качестве Id файла и имени листа вы сможете указать СВОИ значения и МОЙ скрипт (после ВАШЕГО разрешения) заполнит ВАШИ ячейки значениями из Excel. Но об этом - в следующей "серии". Продолжение следует...


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Пятница, 22.12.2017, 19:37
 
Ответить
СообщениеХорошая новость перед Новым годом - я почти разобрался с более-менее человеческим автоматическим экспортом данных из Excel в Google Spreadsheet. Задача решается (кто бы мог подумать! :)) с помощью HTTP-запросов методами GET и POST. При использовании GET передаваемые данные (немного) включаются текстом прямо в адресную строку браузера (в ее конец). Если же данных много, то гораздо удобнее использовать метод POST, помещая данные в так называемое "тело" запроса.

Предполагаю, что ниже я напишу несколько сообщений, освещая в каждом из них конкретный шаг общего техпроцесса экспорта из таблицы Excel в таблицу Google. И вот в этом (первом) сообщении привожу отлаженный код двух процедур на VBA для экспорта данных каждым из вышеназванных методов.


Процедура sendGET выводит некоторый текст в 3 ячейки таблицы Google - B5,B6,B7. Координаты ячеек задаются параметрами row и col (номер строки и столбца), а значение ячейки - параметром txt (это просто я так придумал в этом примере). Имена параметров отделяются от значений знаками равенства (=), а пары "переменная-значение" - знаками амперсанда (&).

Для процедуры sendPOST соблюдаются аналогичные правила относительно параметров и значений. Причем для sendPOST такой порядок "закреплен" указанием в заголовке запроса соответствующего параметра типа контента: "Content-Type", "application/x-www-form-urlencoded". Возможно использование гораздо более прогрессивных типов контента (JSON, xml), но в рамках примера ограничимся указанным простейшим типом. Обратите внимание, что синтаксис пар "переменная-значение" в нашем частном случае совпадает и для метода POST, и для метода GET, что в определенном смысле удобно в учебных целях.

В методе sendPOST заполняются ячейки таблицы Google в диапазоне из 500 строк и 10 столбцов. Заполнение происходит неким периодическим значением, генерируемым прямо в цикле. Я не стал отяжелять пример чтением передаваемых данных из ячеек таблицы Excel, чтобы не приводить саму эту таблицу. При желании каждый может самостоятельно дополнить пример чтением данных из ячеек своей собственной таблицы.

Теперь зададимся вопросом - в какой таблице Google осядут передаваемые данные? Как видно, за это отвечают две переменные: ssId - "страшный" идентификатор файла таблицы (44 символа) и sheetName - имя листа внутри файла. При использовании кириллицы в названии листа его следует подвергнуть операции шифрования при помощи приведенной функции encodeURL. То же самое при наличии кириллицы надо сделать и при передаче текстового значения ячейки.

В качестве Id файла и имени листа вы сможете указать СВОИ значения и МОЙ скрипт (после ВАШЕГО разрешения) заполнит ВАШИ ячейки значениями из Excel. Но об этом - в следующей "серии". Продолжение следует...

Автор - Gustav
Дата добавления - 22.12.2017 в 19:32
Gustav Дата: Пятница, 22.12.2017, 20:40 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1828
Репутация: 726 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Серия 2-я. Имея в распоряжении текст процедур в Excel, вы уже можете, как я и обещал в конце первой серии, прямо взять да и заполнить данными свои таблицы Google! Для этого нужно сделать следующее:

1. В обеих процедурах указать свои значения ssId и sheetName.
2. В процедуре sendGET установить точку прерывания на первую строку после окончательного формирования URL - в нашем случае это строка:
[vba]
Код
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
[/vba]
3. Запустить процедуру sendGET и после останова на указанной строке получить значение переменной URL. Сделать это можно в Окне отладки, набрав и исполнив оператор:
[vba]
Код
? URL
[/vba]
4. Далее скопировать выведенную в Окно отладки текстовую строку, поместить ее в строку вашего браузера и выполнить.
5. При возникновении запроса авторизации - разрешить МОЕЙ процедуре писАть в ВАШИ таблицы.

В этом месте возникает некоторая неловкость, ибо только очень смелый человек может пустить в свой "огород" неизвестного "козла". Но мне скрывать-то нечего, и доступ к "козлу", причем, самый прозрачный, сейчас же ниже будет предоставлен!

А вот и ссылка на МОЙ скриптовый файл, который будет транслировать данные из ВАШИХ процедур Excel в ВАШИ таблицы Google - можете просмотреть:

https://drive.google.com/open?id....QipD7xR

А вот его содержимое - код Google Apps Script:

[vba]
Код
function doGet(e){
  processHttpRequest(e);
}

function doPost(e){
  processHttpRequest(e);
}

function processHttpRequest(e) {
  var ss = SpreadsheetApp.openById(e.parameter.ssId);
  var sheet = ss.getSheetByName(e.parameter.sheetName);

  for(i=0; i<e.parameters.row.length; i++) {    
    var row = Number(e.parameters.row[i]);
    var col = Number(e.parameters.col[i]);
    var range = sheet.getRange(row, col);
    range.setValue(e.parameters.txt[i]);
  }    
}
[/vba]
Как видите, приведенные скрипты не собираются делать ничего, кроме того, как обрабатывать ваши запросы GET и POST по нашей экспериментальной задаче. Никакого скрытого троянского кода по взлому вашего почтового ящика или еще какой-нибудь подобной хакерской лабуды! doGet и doPost - это такие же зарезервированные имена функций с особым смыслом, как, например, уже многим знакомая функция onEdit в таблицах.

Продолжаем (точнее, завершаем) начатую выше последовательность шагов:
6. После предоставления разрешений вернитесь в Excel и последовательно запустите, уже без точки останова, процедуру sendGET, а затем и sendPOST. После каждого запуска смотрите как меняется содержимое ваших таблиц Google.

А если оно вдруг не захочет меняться, то в следующей "серии" я расскажу, почему это может быть и как с этим бороться. Продолжение следует...

P.S. Кстати, разумеется, выше вы можете не использовать мои скрипты, а сделать их копию на своём диске Google и работать абсолютно автономно с исключительно своими секретными данными, никому их не показывая :)


Мой tip box - яд 41001663842605
 
Ответить
СообщениеСерия 2-я. Имея в распоряжении текст процедур в Excel, вы уже можете, как я и обещал в конце первой серии, прямо взять да и заполнить данными свои таблицы Google! Для этого нужно сделать следующее:

1. В обеих процедурах указать свои значения ssId и sheetName.
2. В процедуре sendGET установить точку прерывания на первую строку после окончательного формирования URL - в нашем случае это строка:
[vba]
Код
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
[/vba]
3. Запустить процедуру sendGET и после останова на указанной строке получить значение переменной URL. Сделать это можно в Окне отладки, набрав и исполнив оператор:
[vba]
Код
? URL
[/vba]
4. Далее скопировать выведенную в Окно отладки текстовую строку, поместить ее в строку вашего браузера и выполнить.
5. При возникновении запроса авторизации - разрешить МОЕЙ процедуре писАть в ВАШИ таблицы.

В этом месте возникает некоторая неловкость, ибо только очень смелый человек может пустить в свой "огород" неизвестного "козла". Но мне скрывать-то нечего, и доступ к "козлу", причем, самый прозрачный, сейчас же ниже будет предоставлен!

А вот и ссылка на МОЙ скриптовый файл, который будет транслировать данные из ВАШИХ процедур Excel в ВАШИ таблицы Google - можете просмотреть:

https://drive.google.com/open?id....QipD7xR

А вот его содержимое - код Google Apps Script:

[vba]
Код
function doGet(e){
  processHttpRequest(e);
}

function doPost(e){
  processHttpRequest(e);
}

function processHttpRequest(e) {
  var ss = SpreadsheetApp.openById(e.parameter.ssId);
  var sheet = ss.getSheetByName(e.parameter.sheetName);

  for(i=0; i<e.parameters.row.length; i++) {    
    var row = Number(e.parameters.row[i]);
    var col = Number(e.parameters.col[i]);
    var range = sheet.getRange(row, col);
    range.setValue(e.parameters.txt[i]);
  }    
}
[/vba]
Как видите, приведенные скрипты не собираются делать ничего, кроме того, как обрабатывать ваши запросы GET и POST по нашей экспериментальной задаче. Никакого скрытого троянского кода по взлому вашего почтового ящика или еще какой-нибудь подобной хакерской лабуды! doGet и doPost - это такие же зарезервированные имена функций с особым смыслом, как, например, уже многим знакомая функция onEdit в таблицах.

Продолжаем (точнее, завершаем) начатую выше последовательность шагов:
6. После предоставления разрешений вернитесь в Excel и последовательно запустите, уже без точки останова, процедуру sendGET, а затем и sendPOST. После каждого запуска смотрите как меняется содержимое ваших таблиц Google.

А если оно вдруг не захочет меняться, то в следующей "серии" я расскажу, почему это может быть и как с этим бороться. Продолжение следует...

P.S. Кстати, разумеется, выше вы можете не использовать мои скрипты, а сделать их копию на своём диске Google и работать абсолютно автономно с исключительно своими секретными данными, никому их не показывая :)

Автор - Gustav
Дата добавления - 22.12.2017 в 20:40
Gustav Дата: Суббота, 23.12.2017, 15:42 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1828
Репутация: 726 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Серия 3-я. Поговорим об авторизации, аутентификации и всяких там credentials и token. Вы, наверное, заметили, что ничего подобного в представленном коде явно не указано - ни в VBA, ни в GAS. Ответ на естественно возникающее "почему" очень прост - во время выполнения кода VBA вы должны быть заранее вошедшими в свой аккаунт Google в вашем браузере... [p.s.]...Internet Explorer (именно в нём).[/p.s.]

И вот тут у меня не обошлось без подводных камней, о которых я сейчас немного расскажу и на которые я практически полностью потратил прошлое воскресенье. Дело в том, что в подобном коде VBA можно как-то указывать и логин, и пароль - как в явном, так и в зашифрованном виде. Так, в явном виде логин и пароль можно указать, например, 4-м и 5-м параметром в следующем операторе:
[vba]
Код
httpRequest.Open "POST", URL, False, логин, пароль
[/vba]
Однако просто указание моего логина и пароля (для аккаунта Google) к успеху не привело. Подозреваю, что надо было указать еще какую-то заголовочную опцию, как мелькало в попадавшихся интернет-примерах, типа такой:
[vba]
Код
httpRequest.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
[/vba]
или еще какой-нибудь. Были также эксперименты с другими объектами:
[vba]
Код
CreateObject("WinHttp.WinHttpRequest.5.1")
CreateObject("MSXML2.ServerXMLHTTP")
[/vba]
но лист моей целевой таблицы Google всё равно оставался девственно чистым...

В общем, к вечеру изрядно намучившись с комбинациями объектов и опций, я перестал молотить по "клаве" и задумался... На моем компьютере установлено два браузера: Google Chrome и Internet Explorer, причем, IE является браузером по умолчанию... Хромом я обычно орудую в своем Гугл-диске и, естественно, поэтому всё время нахожусь в аккаунте, не выходя из него... А вот в IE захожу редко и что там сейчас?.. Запустил, так и есть - в IE я не в аккаунте Google! Быстро вошёл в аккаунт, запустил процедуру sendGET, дрожащей мышью открыл целевую гугл-таблицу - есть!! Ячейки B5:B7 заполнились переданными значениями!

Таким образом, окончательно правило я бы сформулировал так: "Во время выполнения кода VBA вы должны заранее войти в свой аккаунт Google в вашем браузере ПО УМОЛЧАНИЮ". [p.s.]НЕТ, НЕ ТАК! "Перед выполнением кода VBA вы должны заранее войти в свой аккаунт Google в вашем браузере Internet Explorer(!)" Да-да, получается, что CreateObject("MSXML2.XMLHTTP") дружен именно с IE. И если вдруг на вашем компьютере IE не установлен, то по состоянию на сегодня я, увы, не знаю, чем вам помочь в рассматриваемом вопросе... Разумеется, ручное выполнение GET-запроса возможно в любом браузере, хотя это и будет несколько менее элегантно, чем из автоматической процедуры[/p.s.]

Мне хотелось, чтобы код VBA получился минимально возможным - только самое необходимое. И я отчасти рад, что в нём сейчас отсутствуют операторы авторизации. С другой стороны, если существует возможность написания самодостаточного кода, который бы не требовал заранее входа в аккаунт Google, то я бы с удовольствием с ним познакомился. Поэтому, если кто-то уже имеет подобный опыт или заинтересуется и освоит тему после этой публикации - милости прошу к нашему шалашу!

Ну, а я на этом заканчиваю "3-ю серию", но мне еще хочется кое-что рассказать, поэтому продолжение снова следует...


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 24.12.2017, 04:24
 
Ответить
СообщениеСерия 3-я. Поговорим об авторизации, аутентификации и всяких там credentials и token. Вы, наверное, заметили, что ничего подобного в представленном коде явно не указано - ни в VBA, ни в GAS. Ответ на естественно возникающее "почему" очень прост - во время выполнения кода VBA вы должны быть заранее вошедшими в свой аккаунт Google в вашем браузере... [p.s.]...Internet Explorer (именно в нём).[/p.s.]

И вот тут у меня не обошлось без подводных камней, о которых я сейчас немного расскажу и на которые я практически полностью потратил прошлое воскресенье. Дело в том, что в подобном коде VBA можно как-то указывать и логин, и пароль - как в явном, так и в зашифрованном виде. Так, в явном виде логин и пароль можно указать, например, 4-м и 5-м параметром в следующем операторе:
[vba]
Код
httpRequest.Open "POST", URL, False, логин, пароль
[/vba]
Однако просто указание моего логина и пароля (для аккаунта Google) к успеху не привело. Подозреваю, что надо было указать еще какую-то заголовочную опцию, как мелькало в попадавшихся интернет-примерах, типа такой:
[vba]
Код
httpRequest.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
[/vba]
или еще какой-нибудь. Были также эксперименты с другими объектами:
[vba]
Код
CreateObject("WinHttp.WinHttpRequest.5.1")
CreateObject("MSXML2.ServerXMLHTTP")
[/vba]
но лист моей целевой таблицы Google всё равно оставался девственно чистым...

В общем, к вечеру изрядно намучившись с комбинациями объектов и опций, я перестал молотить по "клаве" и задумался... На моем компьютере установлено два браузера: Google Chrome и Internet Explorer, причем, IE является браузером по умолчанию... Хромом я обычно орудую в своем Гугл-диске и, естественно, поэтому всё время нахожусь в аккаунте, не выходя из него... А вот в IE захожу редко и что там сейчас?.. Запустил, так и есть - в IE я не в аккаунте Google! Быстро вошёл в аккаунт, запустил процедуру sendGET, дрожащей мышью открыл целевую гугл-таблицу - есть!! Ячейки B5:B7 заполнились переданными значениями!

Таким образом, окончательно правило я бы сформулировал так: "Во время выполнения кода VBA вы должны заранее войти в свой аккаунт Google в вашем браузере ПО УМОЛЧАНИЮ". [p.s.]НЕТ, НЕ ТАК! "Перед выполнением кода VBA вы должны заранее войти в свой аккаунт Google в вашем браузере Internet Explorer(!)" Да-да, получается, что CreateObject("MSXML2.XMLHTTP") дружен именно с IE. И если вдруг на вашем компьютере IE не установлен, то по состоянию на сегодня я, увы, не знаю, чем вам помочь в рассматриваемом вопросе... Разумеется, ручное выполнение GET-запроса возможно в любом браузере, хотя это и будет несколько менее элегантно, чем из автоматической процедуры[/p.s.]

Мне хотелось, чтобы код VBA получился минимально возможным - только самое необходимое. И я отчасти рад, что в нём сейчас отсутствуют операторы авторизации. С другой стороны, если существует возможность написания самодостаточного кода, который бы не требовал заранее входа в аккаунт Google, то я бы с удовольствием с ним познакомился. Поэтому, если кто-то уже имеет подобный опыт или заинтересуется и освоит тему после этой публикации - милости прошу к нашему шалашу!

Ну, а я на этом заканчиваю "3-ю серию", но мне еще хочется кое-что рассказать, поэтому продолжение снова следует...

Автор - Gustav
Дата добавления - 23.12.2017 в 15:42
Gst Дата: Четверг, 08.02.2018, 14:16 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
если существует возможность написания самодостаточного кода, который бы не требовал заранее входа в аккаунт Google, то я бы с удовольствием с ним познакомился. Поэтому, если кто-то уже имеет подобный опыт или заинтересуется и освоит тему после этой публикации - милости прошу к нашему шалашу!

Благодарю за сериал - помог решить аналогичную задачу)
Я опубликовал google скрипт который принимает HTTP запросы и парсит их в переходную google таблицу как приложение с доступом "Anyone even anonimous"
В результате код VBA успешно отправляет запросы (и POST и GET) и данные заносятся в гугл таблицу даже если перед этим вышел в хроме из всех Google аккаунтов, (IE вообще не открылвал и удалил его по-максимуму уже давно).

В функции encodeURL у Вас:
Set ScriptEngine = CreateObject("scriptcontrol")

Выяснилось, что scriptcontrol умеет только 32-бит, соответственно на моей 64-битной системе и офисе он не работает и вызывает ошибку
но начиная с 2013-й версии в Excel есть встроенный метод WorksheetFunction.encodeURL(str)


Сообщение отредактировал Gst - Четверг, 08.02.2018, 14:17
 
Ответить
Сообщение
если существует возможность написания самодостаточного кода, который бы не требовал заранее входа в аккаунт Google, то я бы с удовольствием с ним познакомился. Поэтому, если кто-то уже имеет подобный опыт или заинтересуется и освоит тему после этой публикации - милости прошу к нашему шалашу!

Благодарю за сериал - помог решить аналогичную задачу)
Я опубликовал google скрипт который принимает HTTP запросы и парсит их в переходную google таблицу как приложение с доступом "Anyone even anonimous"
В результате код VBA успешно отправляет запросы (и POST и GET) и данные заносятся в гугл таблицу даже если перед этим вышел в хроме из всех Google аккаунтов, (IE вообще не открылвал и удалил его по-максимуму уже давно).

В функции encodeURL у Вас:
Set ScriptEngine = CreateObject("scriptcontrol")

Выяснилось, что scriptcontrol умеет только 32-бит, соответственно на моей 64-битной системе и офисе он не работает и вызывает ошибку
но начиная с 2013-й версии в Excel есть встроенный метод WorksheetFunction.encodeURL(str)

Автор - Gst
Дата добавления - 08.02.2018 в 14:16
Gustav Дата: Четверг, 08.02.2018, 16:09 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1828
Репутация: 726 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
WorksheetFunction.encodeURL(str)

Вот это интересно! Спасибо!

scriptcontrol умеет только 32-бит, соответственно на моей 64-битной системе и офисе он не работает и вызывает ошибку

А это - ответная любезность! Там про Tablacus Script Control - 64-битная версия Script Control'а. API - аналогично.


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
WorksheetFunction.encodeURL(str)

Вот это интересно! Спасибо!

scriptcontrol умеет только 32-бит, соответственно на моей 64-битной системе и офисе он не работает и вызывает ошибку

А это - ответная любезность! Там про Tablacus Script Control - 64-битная версия Script Control'а. API - аналогично.

Автор - Gustav
Дата добавления - 08.02.2018 в 16:09
Gst Дата: Четверг, 08.02.2018, 17:06 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Tablacus Script Control - 64-битная версия Script Control'а. API - аналогично.

Да, я когда разбирался с ошибкой читал про Таблакус
проблема в том, что функция должна работать не на моем компьютере, а на ресепшене где свои сисадмины и прочее и инсталлировать какие-либо пакеты - большой вопрос
в общем должно работать по возможности только то, что установлено по умолчанию (кроме самого скрипта конечно))


Сообщение отредактировал Gst - Четверг, 08.02.2018, 17:06
 
Ответить
Сообщение
Tablacus Script Control - 64-битная версия Script Control'а. API - аналогично.

Да, я когда разбирался с ошибкой читал про Таблакус
проблема в том, что функция должна работать не на моем компьютере, а на ресепшене где свои сисадмины и прочее и инсталлировать какие-либо пакеты - большой вопрос
в общем должно работать по возможности только то, что установлено по умолчанию (кроме самого скрипта конечно))

Автор - Gst
Дата добавления - 08.02.2018 в 17:06
Gst Дата: Пятница, 09.02.2018, 20:53 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
У функции WorksheetFunction.encodeURL(str) выявилась неприятная особенность

Когда ей на вход мой скрипт передал достаточно большое, но вполне разумное количество данных (некоторые данные по около сотни записям за 2 месяца) она выдала ошибку
опытным путем выяснил, что она (как и несколько других текстовых функций экселя, которые я попробовал для интереса в самом листе книги эксель) затыкается когда длинна строки на выходе достигает 32768

глядя на число - думаю это связано с представлением 16 битных целых чисел (макс 65535 разделяется на - 32768 +32767) в каком либо внутреннем счетчике этих функций
Вот так... в 2018 году Эксель, который зачем то теперь содержит миллион строк на листе О_о во внутренней своей логике ограничен 16-ю битами(


Сообщение отредактировал Gst - Пятница, 09.02.2018, 20:59
 
Ответить
СообщениеУ функции WorksheetFunction.encodeURL(str) выявилась неприятная особенность

Когда ей на вход мой скрипт передал достаточно большое, но вполне разумное количество данных (некоторые данные по около сотни записям за 2 месяца) она выдала ошибку
опытным путем выяснил, что она (как и несколько других текстовых функций экселя, которые я попробовал для интереса в самом листе книги эксель) затыкается когда длинна строки на выходе достигает 32768

глядя на число - думаю это связано с представлением 16 битных целых чисел (макс 65535 разделяется на - 32768 +32767) в каком либо внутреннем счетчике этих функций
Вот так... в 2018 году Эксель, который зачем то теперь содержит миллион строк на листе О_о во внутренней своей логике ограничен 16-ю битами(

Автор - Gst
Дата добавления - 09.02.2018 в 20:53
oup0e Дата: Понедельник, 12.02.2018, 02:52 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Gustav, Я сделал post-запросом через форму гугла и всё отлично работает, таблица заполняется.
Никаких сторонних кодов и тп. Только небольшой код в VBA на отправку post.
Пример брал здесь: __youtube.com/watch?v=qE0l2VIllV4
 
Ответить
СообщениеGustav, Я сделал post-запросом через форму гугла и всё отлично работает, таблица заполняется.
Никаких сторонних кодов и тп. Только небольшой код в VBA на отправку post.
Пример брал здесь: __youtube.com/watch?v=qE0l2VIllV4

Автор - oup0e
Дата добавления - 12.02.2018 в 02:52
Gst Дата: Вторник, 13.02.2018, 00:18 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Gustav, Я сделал post-запросом через форму гугла и всё отлично работает, таблица заполняется.
Никаких сторонних кодов и тп. Только небольшой код в VBA на отправку post.

Попробовал - да через форму VBA шлет данные в гугл таблицу как говорится "без регистрации и смс"
Но, удобно это ИМХО для отправки за раз небольшого объема структурированных данных, т.к. в таблицу по запросу просто добавляются строки с данными согласно прописанным полям формы

Если например надо диапазон ячеек переслать (притом, что заранее неизвестно количество столбцов = полей) или не добавлять а обновлять данные в таблице (к тому же в разных ее местах)
То удобнее конечно через скрипт как вебприложение

Касательно URL кодирования - все методы что перепробовал имели свои косяки:
ScriptControl - не работает в 64 битных версиях
Tablacus Script Control - необходимо чтобы пользователь скачивал и устанавливал его себе
WorksheetFunction.encodeURL() - не включена в версии младше 2013
+баг с внутренним счетчиком = выкидывает при превышении длины строки 32677 на входе(а еще важнее что на выходе т.к. заранее не проверить)
Написанный на самом VBA енкодер - работает медленно (в 50-70 раз медленнее вышеперечисленных)

Но в конце концов наткнулся на способ через скрипт HTML файла

[vba]
Код
Public Function EncodeUriComponent(str)
    Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
        Set objHtmlfile = CreateObject("htmlfile")
        objHtmlfile.parentWindow.execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
    End If
    EncodeUriComponent = objHtmlfile.parentWindow.encode(str)
End Function
[/vba]

Который, помимо краткости, работает и быстро и с длинными строками и на всех системах до которых я смог дотянуться (и где затыкались то один то другой способы из перечисленных выше)


Сообщение отредактировал Gst - Вторник, 13.02.2018, 00:26
 
Ответить
Сообщение
Gustav, Я сделал post-запросом через форму гугла и всё отлично работает, таблица заполняется.
Никаких сторонних кодов и тп. Только небольшой код в VBA на отправку post.

Попробовал - да через форму VBA шлет данные в гугл таблицу как говорится "без регистрации и смс"
Но, удобно это ИМХО для отправки за раз небольшого объема структурированных данных, т.к. в таблицу по запросу просто добавляются строки с данными согласно прописанным полям формы

Если например надо диапазон ячеек переслать (притом, что заранее неизвестно количество столбцов = полей) или не добавлять а обновлять данные в таблице (к тому же в разных ее местах)
То удобнее конечно через скрипт как вебприложение

Касательно URL кодирования - все методы что перепробовал имели свои косяки:
ScriptControl - не работает в 64 битных версиях
Tablacus Script Control - необходимо чтобы пользователь скачивал и устанавливал его себе
WorksheetFunction.encodeURL() - не включена в версии младше 2013
+баг с внутренним счетчиком = выкидывает при превышении длины строки 32677 на входе(а еще важнее что на выходе т.к. заранее не проверить)
Написанный на самом VBA енкодер - работает медленно (в 50-70 раз медленнее вышеперечисленных)

Но в конце концов наткнулся на способ через скрипт HTML файла

[vba]
Код
Public Function EncodeUriComponent(str)
    Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
        Set objHtmlfile = CreateObject("htmlfile")
        objHtmlfile.parentWindow.execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
    End If
    EncodeUriComponent = objHtmlfile.parentWindow.encode(str)
End Function
[/vba]

Который, помимо краткости, работает и быстро и с длинными строками и на всех системах до которых я смог дотянуться (и где затыкались то один то другой способы из перечисленных выше)

Автор - Gst
Дата добавления - 13.02.2018 в 00:18
oup0e Дата: Вторник, 13.02.2018, 01:00 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Gst, Да, у меня данные нужно было как-раз построчно слать. Поэтому что быстро нашел, то и сгодилось. При этом не пришлось особо что-то настраивать. Сделал форму, привязал к таблице и шлю данные. Для нескольких строк за раз, такое решение, конечно, не вариант.

Цитата
Я опубликовал google скрипт который принимает HTTP запросы и парсит их в переходную google таблицу как приложение с доступом "Anyone even anonimous"

Можете поделиться вашим скриптом?


Сообщение отредактировал oup0e - Вторник, 13.02.2018, 01:00
 
Ответить
СообщениеGst, Да, у меня данные нужно было как-раз построчно слать. Поэтому что быстро нашел, то и сгодилось. При этом не пришлось особо что-то настраивать. Сделал форму, привязал к таблице и шлю данные. Для нескольких строк за раз, такое решение, конечно, не вариант.

Цитата
Я опубликовал google скрипт который принимает HTTP запросы и парсит их в переходную google таблицу как приложение с доступом "Anyone even anonimous"

Можете поделиться вашим скриптом?

Автор - oup0e
Дата добавления - 13.02.2018 в 01:00
Gst Дата: Вторник, 13.02.2018, 01:31 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Так кроме общей части, которая содержалась в коде, приведенном Gustav:
[vba]
Код
function doGet(e){
...
}

function doPost(e){
...
}
[/vba]
Наполнение определяется конкретной задачей

Например для вставки скопированного диапазона будет нечто вроде:

[vba]
Код
function doPost(e){
processHttpRequest(e);
return HtmlService.createHtmlOutput('<b>POST Request was recieved</b>');
}

function processHttpRequest(e) {

  var ss = SpreadsheetApp.openById('ID Вашей таблицы');
  var sheet = ss.getSheetByName('Имя Вашего листа');
  
  var content = e.parameter.content;

  var parsedContent = JSON.parse(content);
  
  var ResultArray = [parsedContent[0].slice()];
  
  for (var i=1; i<parsedContent.length; i++) {
      ResultArray.push( parsedContent[i].slice() )
  }
  
  
  var needRows = ResultArray.length;
  var needCols = ResultArray[0].length;
  
  sheet.getRange(1, 1, needRows, needCols)
       .setValues(ResultArray)
  
}
[/vba]

Для корректной работы JSON файл, который я отправляю POST запросом формируется из заданного диапазона ячеек R1C1:RnCn в виде строки
{"content":[[r1c1, r1c2, ... ,r1cn],[r2c1, r2c2, ... ,r2cn], ..., [rnc1, rnc2, ... ,rncn]}

функцией
[vba]
Код
Public Function ToArrJSON(rng As Range) As String
    ' Make sure there are two columns in the range
    If rng.Columns.Count < 2 Then
        ToArrJSON = CVErr(xlErrNA)
        Exit Function
    End If

    Dim dataLoop, headerLoop As Long
    ' Get the first row of the range as a header range
    Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).Address)
    
    Dim colCount As Long: colCount = headerRange.Columns.Count
    
    Dim json As String: json = "["
    
    For dataLoop = 0 To rng.Rows.Count
        ' Include the first header row as well
        If dataLoop > 0 Then
            ' Start data row
            Dim rowJson As String: rowJson = "["
            
            For headerLoop = 1 To colCount
                rowJson = rowJson & """" & rng.Value2(dataLoop, headerLoop) & """"
                rowJson = rowJson & ","
            Next headerLoop
            
            ' Strip out the last comma
            rowJson = Left(rowJson, Len(rowJson) - 1)
            
            ' End data row
            json = json & rowJson & "],"
        End If
    Next
    
    ' Strip out the last comma
    json = Left(json, Len(json) - 1)
    
    json = json & "]"
    
    ToArrJSON = json
End Function
[/vba]З.Ы. Наверное можно проще, например в джаваскрипт я бы просто прошелся по строкам "двумерного" массива join-ом, но с VBA я вообще мало знаком и лень было копаться какая там структура массива/диапазона - поэтому просто по-быстрому переписал под себя первую функцию что нагуглил))

Ну а сам запрос:
[vba]
Код


Sub sendPOST(sheetName As String, title As String, content)

Const URLgoogle As String = "https://script.google.com/macros/s/"
Const webAppId As String = "Id Скрипта - веб приложения"
    
    Dim httpRequest As Object 'MSXML2.XMLHTTP
    
    Dim URL As String
    URL = URLgoogle & webAppId
    
    Dim requestBody As Variant
    requestBody = "&content=" & EncodeUriComponent(content)
    
        
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    httpRequest.Open "POST", URL, False
    httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    httpRequest.Send requestBody

Set httpRequest = Nothing
End Sub
[/vba]


Сообщение отредактировал Gst - Вторник, 13.02.2018, 11:34
 
Ответить
СообщениеТак кроме общей части, которая содержалась в коде, приведенном Gustav:
[vba]
Код
function doGet(e){
...
}

function doPost(e){
...
}
[/vba]
Наполнение определяется конкретной задачей

Например для вставки скопированного диапазона будет нечто вроде:

[vba]
Код
function doPost(e){
processHttpRequest(e);
return HtmlService.createHtmlOutput('<b>POST Request was recieved</b>');
}

function processHttpRequest(e) {

  var ss = SpreadsheetApp.openById('ID Вашей таблицы');
  var sheet = ss.getSheetByName('Имя Вашего листа');
  
  var content = e.parameter.content;

  var parsedContent = JSON.parse(content);
  
  var ResultArray = [parsedContent[0].slice()];
  
  for (var i=1; i<parsedContent.length; i++) {
      ResultArray.push( parsedContent[i].slice() )
  }
  
  
  var needRows = ResultArray.length;
  var needCols = ResultArray[0].length;
  
  sheet.getRange(1, 1, needRows, needCols)
       .setValues(ResultArray)
  
}
[/vba]

Для корректной работы JSON файл, который я отправляю POST запросом формируется из заданного диапазона ячеек R1C1:RnCn в виде строки
{"content":[[r1c1, r1c2, ... ,r1cn],[r2c1, r2c2, ... ,r2cn], ..., [rnc1, rnc2, ... ,rncn]}

функцией
[vba]
Код
Public Function ToArrJSON(rng As Range) As String
    ' Make sure there are two columns in the range
    If rng.Columns.Count < 2 Then
        ToArrJSON = CVErr(xlErrNA)
        Exit Function
    End If

    Dim dataLoop, headerLoop As Long
    ' Get the first row of the range as a header range
    Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).Address)
    
    Dim colCount As Long: colCount = headerRange.Columns.Count
    
    Dim json As String: json = "["
    
    For dataLoop = 0 To rng.Rows.Count
        ' Include the first header row as well
        If dataLoop > 0 Then
            ' Start data row
            Dim rowJson As String: rowJson = "["
            
            For headerLoop = 1 To colCount
                rowJson = rowJson & """" & rng.Value2(dataLoop, headerLoop) & """"
                rowJson = rowJson & ","
            Next headerLoop
            
            ' Strip out the last comma
            rowJson = Left(rowJson, Len(rowJson) - 1)
            
            ' End data row
            json = json & rowJson & "],"
        End If
    Next
    
    ' Strip out the last comma
    json = Left(json, Len(json) - 1)
    
    json = json & "]"
    
    ToArrJSON = json
End Function
[/vba]З.Ы. Наверное можно проще, например в джаваскрипт я бы просто прошелся по строкам "двумерного" массива join-ом, но с VBA я вообще мало знаком и лень было копаться какая там структура массива/диапазона - поэтому просто по-быстрому переписал под себя первую функцию что нагуглил))

Ну а сам запрос:
[vba]
Код


Sub sendPOST(sheetName As String, title As String, content)

Const URLgoogle As String = "https://script.google.com/macros/s/"
Const webAppId As String = "Id Скрипта - веб приложения"
    
    Dim httpRequest As Object 'MSXML2.XMLHTTP
    
    Dim URL As String
    URL = URLgoogle & webAppId
    
    Dim requestBody As Variant
    requestBody = "&content=" & EncodeUriComponent(content)
    
        
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    httpRequest.Open "POST", URL, False
    httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    httpRequest.Send requestBody

Set httpRequest = Nothing
End Sub
[/vba]

Автор - Gst
Дата добавления - 13.02.2018 в 01:31
alexkl Дата: Вторник, 06.11.2018, 21:55 | Сообщение № 16
Группа: Пользователи
Ранг: Прохожий
Сообщений: 1
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Gst, Можно из VBA через Google Sheets API читать и писать в Google Sheets. Через Apps Script API из VBA можно выполнять функции на Javascript из Google Sheets. Вот ссылка http://ramblings.mcpher.com/Home....heetsv4
 
Ответить
СообщениеGst, Можно из VBA через Google Sheets API читать и писать в Google Sheets. Через Apps Script API из VBA можно выполнять функции на Javascript из Google Sheets. Вот ссылка http://ramblings.mcpher.com/Home....heetsv4

Автор - alexkl
Дата добавления - 06.11.2018 в 21:55
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Заполнить google sheets данными из excel, при помощи vba
  • Страница 1 из 1
  • 1
Поиск:

jooble Яндекс цитирования
© 2010-2020 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!