Поиск по интернету, к сожалению, не помог. Подскажите пожалуйста, есть ли какое-либо решение по переносу данных из excel в google sheets? Если кто-нибудь уже решал проблему или решает сейчас — поделитесь, пожалуйста, имеющейся инфой.
Спасибо!
Добрый день, господа!
Поиск по интернету, к сожалению, не помог. Подскажите пожалуйста, есть ли какое-либо решение по переносу данных из excel в google sheets? Если кто-нибудь уже решал проблему или решает сейчас — поделитесь, пожалуйста, имеющейся инфой.
есть ли какое-либо решение по переносу данных из excel в google sheets?
Есть - самое простое: вручную Ctrl+C в Excel - Ctrl+V в таблице Google. И вряд ли стоит как-то автоматизировать эту процедуру, хотя при желании можно поизвращаться.
Если Вас не устраивает такой простой и естественный способ переноса, то расскажите подробнее что именно хотите делать.
есть ли какое-либо решение по переносу данных из excel в google sheets?
Есть - самое простое: вручную Ctrl+C в Excel - Ctrl+V в таблице Google. И вряд ли стоит как-то автоматизировать эту процедуру, хотя при желании можно поизвращаться.
Если Вас не устраивает такой простой и естественный способ переноса, то расскажите подробнее что именно хотите делать.
Gustav, Была сложная задача - ежедневное обновление файла на диске. Файл (Excel) изначально собирался вручную, далее загружался в гугл.докс, к которому есть доступ у нескольких человек.
Как это сделать - разобраться не смог, решил отложить до лучших времен.
Gustav, Была сложная задача - ежедневное обновление файла на диске. Файл (Excel) изначально собирался вручную, далее загружался в гугл.докс, к которому есть доступ у нескольких человек.
Как это сделать - разобраться не смог, решил отложить до лучших времен. akobir
Хорошая новость перед Новым годом - я почти разобрался с более-менее человеческим автоматическим экспортом данных из Excel в Google Spreadsheet. Задача решается (кто бы мог подумать! :)) с помощью HTTP-запросов методами GET и POST. При использовании GET передаваемые данные (немного) включаются текстом прямо в адресную строку браузера (в ее конец). Если же данных много, то гораздо удобнее использовать метод POST, помещая данные в так называемое "тело" запроса.
Предполагаю, что ниже я напишу несколько сообщений, освещая в каждом из них конкретный шаг общего техпроцесса экспорта из таблицы Excel в таблицу Google. И вот в этом (первом) сообщении привожу отлаженный код двух процедур на VBA для экспорта данных каждым из вышеназванных методов.
[vba]
Код
Option Explicit
Const webAppId As String = "AKfycby_kyXm7SKw6PuZh3zo1MuULHGncHuOixa3JTQcZweNe8Ah1pc" 'Id моего веб-приложения, доступного всем после авторизации
Sub sendGET() Dim httpRequest As Object 'MSXML2.XMLHTTP Dim URL As String Dim ssId As String Dim sheetName As String
ssId = "1a027RBvGjNcJZs4nyULg-gjq75OI1-kYzpcmIsp7yFc" 'здесь указать Id своей таблицы Google (из адресной строки) sheetName = encodeURL("Лист5") 'здесь указать имя своего листа
Set httpRequest = CreateObject("MSXML2.XMLHTTP") httpRequest.Open "GET", URL, False httpRequest.Send End Sub
Sub sendPOST() Dim httpRequest As Object 'MSXML2.XMLHTTP Dim URL As String Dim requestBody As Variant Dim row As Integer, col As Integer
Dim ssId As String Dim sheetName As String
ssId = "1a027RBvGjNcJZs4nyULg-gjq75OI1-kYzpcmIsp7yFc" 'здесь указать Id своей таблицы Google (из адресной строки) sheetName = encodeURL("Лист5") 'здесь указать имя своего листа
requestBody = "ssId=" & ssId _ & "&sheetName=" & sheetName For row = 1 To 500: For col = 1 To 10 requestBody = requestBody & "&row=" & row & "&col=" & col & "&txt=" & encodeURL("хрю " & row & " " & col) Next col, row
Public Function encodeURL(str As String) 'https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba Dim ScriptEngine As Object Dim encoded As String
Set ScriptEngine = CreateObject("scriptcontrol") ScriptEngine.Language = "JScript"
Процедура 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. Но об этом - в следующей "серии". Продолжение следует...
Хорошая новость перед Новым годом - я почти разобрался с более-менее человеческим автоматическим экспортом данных из Excel в Google Spreadsheet. Задача решается (кто бы мог подумать! :)) с помощью HTTP-запросов методами GET и POST. При использовании GET передаваемые данные (немного) включаются текстом прямо в адресную строку браузера (в ее конец). Если же данных много, то гораздо удобнее использовать метод POST, помещая данные в так называемое "тело" запроса.
Предполагаю, что ниже я напишу несколько сообщений, освещая в каждом из них конкретный шаг общего техпроцесса экспорта из таблицы Excel в таблицу Google. И вот в этом (первом) сообщении привожу отлаженный код двух процедур на VBA для экспорта данных каждым из вышеназванных методов.
[vba]
Код
Option Explicit
Const webAppId As String = "AKfycby_kyXm7SKw6PuZh3zo1MuULHGncHuOixa3JTQcZweNe8Ah1pc" 'Id моего веб-приложения, доступного всем после авторизации
Sub sendGET() Dim httpRequest As Object 'MSXML2.XMLHTTP Dim URL As String Dim ssId As String Dim sheetName As String
ssId = "1a027RBvGjNcJZs4nyULg-gjq75OI1-kYzpcmIsp7yFc" 'здесь указать Id своей таблицы Google (из адресной строки) sheetName = encodeURL("Лист5") 'здесь указать имя своего листа
Set httpRequest = CreateObject("MSXML2.XMLHTTP") httpRequest.Open "GET", URL, False httpRequest.Send End Sub
Sub sendPOST() Dim httpRequest As Object 'MSXML2.XMLHTTP Dim URL As String Dim requestBody As Variant Dim row As Integer, col As Integer
Dim ssId As String Dim sheetName As String
ssId = "1a027RBvGjNcJZs4nyULg-gjq75OI1-kYzpcmIsp7yFc" 'здесь указать Id своей таблицы Google (из адресной строки) sheetName = encodeURL("Лист5") 'здесь указать имя своего листа
requestBody = "ssId=" & ssId _ & "&sheetName=" & sheetName For row = 1 To 500: For col = 1 To 10 requestBody = requestBody & "&row=" & row & "&col=" & col & "&txt=" & encodeURL("хрю " & row & " " & col) Next col, row
Public Function encodeURL(str As String) 'https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba Dim ScriptEngine As Object Dim encoded As String
Set ScriptEngine = CreateObject("scriptcontrol") ScriptEngine.Language = "JScript"
Процедура 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
Серия 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 - можете просмотреть:
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 и работать абсолютно автономно с исключительно своими секретными данными, никому их не показывая
Серия 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 - можете просмотреть:
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
Серия 3-я. Поговорим об авторизации, аутентификации и всяких там credentials и token. Вы, наверное, заметили, что ничего подобного в представленном коде явно не указано - ни в VBA, ни в GAS. Ответ на естественно возникающее "почему" очень прост - во время выполнения кода VBA вы должны быть заранее вошедшими в свой аккаунт Google в вашем браузере... [p.s.]...Internet Explorer (именно в нём).[/p.s.]
И вот тут у меня не обошлось без подводных камней, о которых я сейчас немного расскажу и на которые я практически полностью потратил прошлое воскресенье. Дело в том, что в подобном коде VBA можно как-то указывать и логин, и пароль - как в явном, так и в зашифрованном виде. Так, в явном виде логин и пароль можно указать, например, 4-м и 5-м параметром в следующем операторе: [vba]
[/vba] Однако просто указание моего логина и пароля (для аккаунта Google) к успеху не привело. Подозреваю, что надо было указать еще какую-то заголовочную опцию, как мелькало в попадавшихся интернет-примерах, типа такой: [vba]
Код
httpRequest.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
[/vba] или еще какой-нибудь. Были также эксперименты с другими объектами: [vba]
[/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-ю серию", но мне еще хочется кое-что рассказать, поэтому продолжение снова следует...
Серия 3-я. Поговорим об авторизации, аутентификации и всяких там credentials и token. Вы, наверное, заметили, что ничего подобного в представленном коде явно не указано - ни в VBA, ни в GAS. Ответ на естественно возникающее "почему" очень прост - во время выполнения кода VBA вы должны быть заранее вошедшими в свой аккаунт Google в вашем браузере... [p.s.]...Internet Explorer (именно в нём).[/p.s.]
И вот тут у меня не обошлось без подводных камней, о которых я сейчас немного расскажу и на которые я практически полностью потратил прошлое воскресенье. Дело в том, что в подобном коде VBA можно как-то указывать и логин, и пароль - как в явном, так и в зашифрованном виде. Так, в явном виде логин и пароль можно указать, например, 4-м и 5-м параметром в следующем операторе: [vba]
[/vba] Однако просто указание моего логина и пароля (для аккаунта Google) к успеху не привело. Подозреваю, что надо было указать еще какую-то заголовочную опцию, как мелькало в попадавшихся интернет-примерах, типа такой: [vba]
Код
httpRequest.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
[/vba] или еще какой-нибудь. Были также эксперименты с другими объектами: [vba]
[/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
если существует возможность написания самодостаточного кода, который бы не требовал заранее входа в аккаунт Google, то я бы с удовольствием с ним познакомился. Поэтому, если кто-то уже имеет подобный опыт или заинтересуется и освоит тему после этой публикации - милости прошу к нашему шалашу!
Благодарю за сериал - помог решить аналогичную задачу) Я опубликовал google скрипт который принимает HTTP запросы и парсит их в переходную google таблицу как приложение с доступом "Anyone even anonimous" В результате код VBA успешно отправляет запросы (и POST и GET) и данные заносятся в гугл таблицу даже если перед этим вышел в хроме из всех Google аккаунтов, (IE вообще не открылвал и удалил его по-максимуму уже давно).
Выяснилось, что scriptcontrol умеет только 32-бит, соответственно на моей 64-битной системе и офисе он не работает и вызывает ошибку но начиная с 2013-й версии в Excel есть встроенный метод WorksheetFunction.encodeURL(str)
если существует возможность написания самодостаточного кода, который бы не требовал заранее входа в аккаунт Google, то я бы с удовольствием с ним познакомился. Поэтому, если кто-то уже имеет подобный опыт или заинтересуется и освоит тему после этой публикации - милости прошу к нашему шалашу!
Благодарю за сериал - помог решить аналогичную задачу) Я опубликовал google скрипт который принимает HTTP запросы и парсит их в переходную google таблицу как приложение с доступом "Anyone even anonimous" В результате код VBA успешно отправляет запросы (и POST и GET) и данные заносятся в гугл таблицу даже если перед этим вышел в хроме из всех Google аккаунтов, (IE вообще не открылвал и удалил его по-максимуму уже давно).
Выяснилось, что scriptcontrol умеет только 32-бит, соответственно на моей 64-битной системе и офисе он не работает и вызывает ошибку но начиная с 2013-й версии в Excel есть встроенный метод WorksheetFunction.encodeURL(str)Gst
Сообщение отредактировал Gst - Четверг, 08.02.2018, 14:17
Tablacus Script Control - 64-битная версия Script Control'а. API - аналогично.
Да, я когда разбирался с ошибкой читал про Таблакус проблема в том, что функция должна работать не на моем компьютере, а на ресепшене где свои сисадмины и прочее и инсталлировать какие-либо пакеты - большой вопрос в общем должно работать по возможности только то, что установлено по умолчанию (кроме самого скрипта конечно))
Tablacus Script Control - 64-битная версия Script Control'а. API - аналогично.
Да, я когда разбирался с ошибкой читал про Таблакус проблема в том, что функция должна работать не на моем компьютере, а на ресепшене где свои сисадмины и прочее и инсталлировать какие-либо пакеты - большой вопрос в общем должно работать по возможности только то, что установлено по умолчанию (кроме самого скрипта конечно))Gst
Сообщение отредактировал Gst - Четверг, 08.02.2018, 17:06
У функции WorksheetFunction.encodeURL(str) выявилась неприятная особенность
Когда ей на вход мой скрипт передал достаточно большое, но вполне разумное количество данных (некоторые данные по около сотни записям за 2 месяца) она выдала ошибку опытным путем выяснил, что она (как и несколько других текстовых функций экселя, которые я попробовал для интереса в самом листе книги эксель) затыкается когда длинна строки на выходе достигает 32768
глядя на число - думаю это связано с представлением 16 битных целых чисел (макс 65535 разделяется на - 32768 +32767) в каком либо внутреннем счетчике этих функций Вот так... в 2018 году Эксель, который зачем то теперь содержит миллион строк на листе О_о во внутренней своей логике ограничен 16-ю битами(
У функции WorksheetFunction.encodeURL(str) выявилась неприятная особенность
Когда ей на вход мой скрипт передал достаточно большое, но вполне разумное количество данных (некоторые данные по около сотни записям за 2 месяца) она выдала ошибку опытным путем выяснил, что она (как и несколько других текстовых функций экселя, которые я попробовал для интереса в самом листе книги эксель) затыкается когда длинна строки на выходе достигает 32768
глядя на число - думаю это связано с представлением 16 битных целых чисел (макс 65535 разделяется на - 32768 +32767) в каком либо внутреннем счетчике этих функций Вот так... в 2018 году Эксель, который зачем то теперь содержит миллион строк на листе О_о во внутренней своей логике ограничен 16-ю битами(Gst
Сообщение отредактировал Gst - Пятница, 09.02.2018, 20:59
Gustav, Я сделал post-запросом через форму гугла и всё отлично работает, таблица заполняется. Никаких сторонних кодов и тп. Только небольшой код в VBA на отправку post. Пример брал здесь: __youtube.com/watch?v=qE0l2VIllV4
Gustav, Я сделал post-запросом через форму гугла и всё отлично работает, таблица заполняется. Никаких сторонних кодов и тп. Только небольшой код в VBA на отправку post. Пример брал здесь: __youtube.com/watch?v=qE0l2VIllV4oup0e
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]
Который, помимо краткости, работает и быстро и с длинными строками и на всех системах до которых я смог дотянуться (и где затыкались то один то другой способы из перечисленных выше)
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
Сообщение отредактировал Gst - Вторник, 13.02.2018, 00:26
Gst, Да, у меня данные нужно было как-раз построчно слать. Поэтому что быстро нашел, то и сгодилось. При этом не пришлось особо что-то настраивать. Сделал форму, привязал к таблице и шлю данные. Для нескольких строк за раз, такое решение, конечно, не вариант.
Цитата
Я опубликовал google скрипт который принимает HTTP запросы и парсит их в переходную google таблицу как приложение с доступом "Anyone even anonimous"
Можете поделиться вашим скриптом?
Gst, Да, у меня данные нужно было как-раз построчно слать. Поэтому что быстро нашел, то и сгодилось. При этом не пришлось особо что-то настраивать. Сделал форму, привязал к таблице и шлю данные. Для нескольких строк за раз, такое решение, конечно, не вариант.
Цитата
Я опубликовал google скрипт который принимает HTTP запросы и парсит их в переходную google таблицу как приложение с доступом "Anyone even anonimous"
Для корректной работы 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)
Для корректной работы 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)
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....heetsv4alexkl
Здравствуйте! Сейчас 2022 год. Пытаюсь добавить данные , как в постах выше, не получается. В формах теперь новый вид. Как сейчас добавить данные в гугл таблицу. Подскажите пожалуйста.
Здравствуйте! Сейчас 2022 год. Пытаюсь добавить данные , как в постах выше, не получается. В формах теперь новый вид. Как сейчас добавить данные в гугл таблицу. Подскажите пожалуйста.TvoiExcel
Столкнулся с похожей задачей. Возможно кому-то будет полезно решение, которое сейчас я тестирую. В тестовом режиме работает.
Т.к. я и близко не программист и не специалист ИТ, сильно не пинайте. Писать скрипты на гугл и vba я начал только два месяца назад)
Итак. У меня есть https ссылка на обновляемый файл xlsx Я отлично связывал с ним свой эксель файл и получал всегда в нем актуальные данные. Необходимость дальнейшей автоматизации процесса потребовала отражать получаемые данные в google sheet Сделать так, чтобы заработали встроенные функции IMPORT в гугл я не смог. Может не хватает знаний. Решение было следующим: 1. Подключение Google Drive как локального диска на компьютере: G:\Папка\ 2. Создание эксель файла и связывание с актуальным файлом на https 3. Написание короткого vba на импортирование данных в формат csv и запись на G:\Папка\ 4. Запуск макроса по открытию файла 5. Установка в планировщике задач запуск эксель и открытие файла 6. Открытие общего доступа к папке гугл и копирование ID папки Дальше была попытка использовать стандартную функцию IMPORTDATA(), но ссылка все время становилась недействительной, хотя и не менялась по содержанию. Поэтому: 7. Скрипт в google sheet - на поиск файла с определенным названием в папке с сохраненным ID - выделение id файла csv - импорт файла csv в google sheet - триггер на запуск скрипта
Сейчас тестирую. Вроде работает, актуальные данные с https поступают в мой файл эксель, а следом в google sheet) Если не в тему - админы удалите)
Здравствуйте!
Столкнулся с похожей задачей. Возможно кому-то будет полезно решение, которое сейчас я тестирую. В тестовом режиме работает.
Т.к. я и близко не программист и не специалист ИТ, сильно не пинайте. Писать скрипты на гугл и vba я начал только два месяца назад)
Итак. У меня есть https ссылка на обновляемый файл xlsx Я отлично связывал с ним свой эксель файл и получал всегда в нем актуальные данные. Необходимость дальнейшей автоматизации процесса потребовала отражать получаемые данные в google sheet Сделать так, чтобы заработали встроенные функции IMPORT в гугл я не смог. Может не хватает знаний. Решение было следующим: 1. Подключение Google Drive как локального диска на компьютере: G:\Папка\ 2. Создание эксель файла и связывание с актуальным файлом на https 3. Написание короткого vba на импортирование данных в формат csv и запись на G:\Папка\ 4. Запуск макроса по открытию файла 5. Установка в планировщике задач запуск эксель и открытие файла 6. Открытие общего доступа к папке гугл и копирование ID папки Дальше была попытка использовать стандартную функцию IMPORTDATA(), но ссылка все время становилась недействительной, хотя и не менялась по содержанию. Поэтому: 7. Скрипт в google sheet - на поиск файла с определенным названием в папке с сохраненным ID - выделение id файла csv - импорт файла csv в google sheet - триггер на запуск скрипта
Сейчас тестирую. Вроде работает, актуальные данные с https поступают в мой файл эксель, а следом в google sheet) Если не в тему - админы удалите)piskarevag
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Сейчас 2022 год. Пытаюсь добавить данные , как в постах выше, не получается. В формах теперь новый вид. Как сейчас добавить данные в гугл таблицу. Подскажите пожалуйста.
