Поиск по интернету, к сожалению, не помог. Подскажите пожалуйста, есть ли какое-либо решение по переносу данных из 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 для экспорта данных каждым из вышеназванных методов.
Option Explicit
Const webAppId AsString = "AKfycby_kyXm7SKw6PuZh3zo1MuULHGncHuOixa3JTQcZweNe8Ah1pc"'Id моего веб-приложения, доступного всем после авторизации
Sub sendGET() Dim httpRequest AsObject'MSXML2.XMLHTTP Dim URL AsString Dim ssId AsString Dim sheetName AsString
ssId = "1a027RBvGjNcJZs4nyULg-gjq75OI1-kYzpcmIsp7yFc"'здесь указать Id своей таблицы Google (из адресной строки)
sheetName = encodeURL("Лист5") 'здесь указать имя своего листа
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
httpRequest.Open "GET", URL, False
httpRequest.Send EndSub
Sub sendPOST() Dim httpRequest AsObject'MSXML2.XMLHTTP Dim URL AsString Dim requestBody AsVariant Dim row AsInteger, col AsInteger
Dim ssId AsString Dim sheetName AsString
ssId = "1a027RBvGjNcJZs4nyULg-gjq75OI1-kYzpcmIsp7yFc"'здесь указать Id своей таблицы Google (из адресной строки)
sheetName = encodeURL("Лист5") 'здесь указать имя своего листа
requestBody = "ssId=" & ssId _
& "&sheetName=" & sheetName For row = 1To500: For col = 1To10
requestBody = requestBody & "&row=" & row & "&col=" & col & "&txt=" & encodeURL("хрю " & row & " " & col) Next col, row
PublicFunction encodeURL(str AsString) 'https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba Dim ScriptEngine AsObject Dim encoded AsString
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 для экспорта данных каждым из вышеназванных методов.
Option Explicit
Const webAppId AsString = "AKfycby_kyXm7SKw6PuZh3zo1MuULHGncHuOixa3JTQcZweNe8Ah1pc"'Id моего веб-приложения, доступного всем после авторизации
Sub sendGET() Dim httpRequest AsObject'MSXML2.XMLHTTP Dim URL AsString Dim ssId AsString Dim sheetName AsString
ssId = "1a027RBvGjNcJZs4nyULg-gjq75OI1-kYzpcmIsp7yFc"'здесь указать Id своей таблицы Google (из адресной строки)
sheetName = encodeURL("Лист5") 'здесь указать имя своего листа
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
httpRequest.Open "GET", URL, False
httpRequest.Send EndSub
Sub sendPOST() Dim httpRequest AsObject'MSXML2.XMLHTTP Dim URL AsString Dim requestBody AsVariant Dim row AsInteger, col AsInteger
Dim ssId AsString Dim sheetName AsString
ssId = "1a027RBvGjNcJZs4nyULg-gjq75OI1-kYzpcmIsp7yFc"'здесь указать Id своей таблицы Google (из адресной строки)
sheetName = encodeURL("Лист5") 'здесь указать имя своего листа
requestBody = "ssId=" & ssId _
& "&sheetName=" & sheetName For row = 1To500: For col = 1To10
requestBody = requestBody & "&row=" & row & "&col=" & col & "&txt=" & encodeURL("хрю " & row & " " & col) Next col, row
PublicFunction encodeURL(str AsString) 'https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba Dim ScriptEngine AsObject Dim encoded AsString
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 - в нашем случае это строка:
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
3. Запустить процедуру sendGET и после останова на указанной строке получить значение переменной URL. Сделать это можно в Окне отладки, набрав и исполнив оператор:
? URL
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]);
}
}
Как видите, приведенные скрипты не собираются делать ничего, кроме того, как обрабатывать ваши запросы GET и POST по нашей экспериментальной задаче. Никакого скрытого троянского кода по взлому вашего почтового ящика или еще какой-нибудь подобной хакерской лабуды! doGet и doPost - это такие же зарезервированные имена функций с особым смыслом, как, например, уже многим знакомая функция onEdit в таблицах.
Продолжаем (точнее, завершаем) начатую выше последовательность шагов: 6. После предоставления разрешений вернитесь в Excel и последовательно запустите, уже без точки останова, процедуру sendGET, а затем и sendPOST. После каждого запуска смотрите как меняется содержимое ваших таблиц Google.
А если оно вдруг не захочет меняться, то в следующей "серии" я расскажу, почему это может быть и как с этим бороться. Продолжение следует...
P.S. Кстати, разумеется, выше вы можете не использовать мои скрипты, а сделать их копию на своём диске Google и работать абсолютно автономно с исключительно своими секретными данными, никому их не показывая
Серия 2-я. Имея в распоряжении текст процедур в Excel, вы уже можете, как я и обещал в конце первой серии, прямо взять да и заполнить данными свои таблицы Google! Для этого нужно сделать следующее:
1. В обеих процедурах указать свои значения ssId и sheetName. 2. В процедуре sendGET установить точку прерывания на первую строку после окончательного формирования URL - в нашем случае это строка:
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
3. Запустить процедуру sendGET и после останова на указанной строке получить значение переменной URL. Сделать это можно в Окне отладки, набрав и исполнив оператор:
? URL
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]);
}
}
Как видите, приведенные скрипты не собираются делать ничего, кроме того, как обрабатывать ваши запросы GET и POST по нашей экспериментальной задаче. Никакого скрытого троянского кода по взлому вашего почтового ящика или еще какой-нибудь подобной хакерской лабуды! doGet и doPost - это такие же зарезервированные имена функций с особым смыслом, как, например, уже многим знакомая функция onEdit в таблицах.
Продолжаем (точнее, завершаем) начатую выше последовательность шагов: 6. После предоставления разрешений вернитесь в Excel и последовательно запустите, уже без точки останова, процедуру sendGET, а затем и sendPOST. После каждого запуска смотрите как меняется содержимое ваших таблиц Google.
А если оно вдруг не захочет меняться, то в следующей "серии" я расскажу, почему это может быть и как с этим бороться. Продолжение следует...
P.S. Кстати, разумеется, выше вы можете не использовать мои скрипты, а сделать их копию на своём диске Google и работать абсолютно автономно с исключительно своими секретными данными, никому их не показывая Gustav
Серия 3-я. Поговорим об авторизации, аутентификации и всяких там credentials и token. Вы, наверное, заметили, что ничего подобного в представленном коде явно не указано - ни в VBA, ни в GAS. Ответ на естественно возникающее "почему" очень прост - во время выполнения кода VBA вы должны быть заранее вошедшими в свой аккаунт Google в вашем браузере...
И вот тут у меня не обошлось без подводных камней, о которых я сейчас немного расскажу и на которые я практически полностью потратил прошлое воскресенье. Дело в том, что в подобном коде VBA можно как-то указывать и логин, и пароль - как в явном, так и в зашифрованном виде. Так, в явном виде логин и пароль можно указать, например, 4-м и 5-м параметром в следующем операторе:
Однако просто указание моего логина и пароля (для аккаунта Google) к успеху не привело. Подозреваю, что надо было указать еще какую-то заголовочную опцию, как мелькало в попадавшихся интернет-примерах, типа такой:
httpRequest.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
или еще какой-нибудь. Были также эксперименты с другими объектами:
но лист моей целевой таблицы Google всё равно оставался девственно чистым...
В общем, к вечеру изрядно намучившись с комбинациями объектов и опций, я перестал молотить по "клаве" и задумался... На моем компьютере установлено два браузера: Google Chrome и Internet Explorer, причем, IE является браузером по умолчанию... Хромом я обычно орудую в своем Гугл-диске и, естественно, поэтому всё время нахожусь в аккаунте, не выходя из него... А вот в IE захожу редко и что там сейчас?.. Запустил, так и есть - в IE я не в аккаунте Google! Быстро вошёл в аккаунт, запустил процедуру sendGET, дрожащей мышью открыл целевую гугл-таблицу - есть!! Ячейки B5:B7 заполнились переданными значениями!
Таким образом, окончательно правило я бы сформулировал так: "Во время выполнения кода VBA вы должны заранее войти в свой аккаунт Google в вашем браузере ПО УМОЛЧАНИЮ".
Мне хотелось, чтобы код VBA получился минимально возможным - только самое необходимое. И я отчасти рад, что в нём сейчас отсутствуют операторы авторизации. С другой стороны, если существует возможность написания самодостаточного кода, который бы не требовал заранее входа в аккаунт Google, то я бы с удовольствием с ним познакомился. Поэтому, если кто-то уже имеет подобный опыт или заинтересуется и освоит тему после этой публикации - милости прошу к нашему шалашу!
Ну, а я на этом заканчиваю "3-ю серию", но мне еще хочется кое-что рассказать, поэтому продолжение снова следует...
Серия 3-я. Поговорим об авторизации, аутентификации и всяких там credentials и token. Вы, наверное, заметили, что ничего подобного в представленном коде явно не указано - ни в VBA, ни в GAS. Ответ на естественно возникающее "почему" очень прост - во время выполнения кода VBA вы должны быть заранее вошедшими в свой аккаунт Google в вашем браузере...
И вот тут у меня не обошлось без подводных камней, о которых я сейчас немного расскажу и на которые я практически полностью потратил прошлое воскресенье. Дело в том, что в подобном коде VBA можно как-то указывать и логин, и пароль - как в явном, так и в зашифрованном виде. Так, в явном виде логин и пароль можно указать, например, 4-м и 5-м параметром в следующем операторе:
Однако просто указание моего логина и пароля (для аккаунта Google) к успеху не привело. Подозреваю, что надо было указать еще какую-то заголовочную опцию, как мелькало в попадавшихся интернет-примерах, типа такой:
httpRequest.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
или еще какой-нибудь. Были также эксперименты с другими объектами:
но лист моей целевой таблицы Google всё равно оставался девственно чистым...
В общем, к вечеру изрядно намучившись с комбинациями объектов и опций, я перестал молотить по "клаве" и задумался... На моем компьютере установлено два браузера: Google Chrome и Internet Explorer, причем, IE является браузером по умолчанию... Хромом я обычно орудую в своем Гугл-диске и, естественно, поэтому всё время нахожусь в аккаунте, не выходя из него... А вот в IE захожу редко и что там сейчас?.. Запустил, так и есть - в IE я не в аккаунте Google! Быстро вошёл в аккаунт, запустил процедуру sendGET, дрожащей мышью открыл целевую гугл-таблицу - есть!! Ячейки B5:B7 заполнились переданными значениями!
Таким образом, окончательно правило я бы сформулировал так: "Во время выполнения кода VBA вы должны заранее войти в свой аккаунт Google в вашем браузере ПО УМОЛЧАНИЮ".
Мне хотелось, чтобы код 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 файла
Который, помимо краткости, работает и быстро и с длинными строками и на всех системах до которых я смог дотянуться (и где затыкались то один то другой способы из перечисленных выше)
Gustav, Я сделал post-запросом через форму гугла и всё отлично работает, таблица заполняется. Никаких сторонних кодов и тп. Только небольшой код в VBA на отправку post.
Попробовал - да через форму VBA шлет данные в гугл таблицу как говорится "без регистрации и смс" Но, удобно это ИМХО для отправки за раз небольшого объема структурированных данных, т.к. в таблицу по запросу просто добавляются строки с данными согласно прописанным полям формы
Если например надо диапазон ячеек переслать (притом, что заранее неизвестно количество столбцов = полей) или не добавлять а обновлять данные в таблице (к тому же в разных ее местах) То удобнее конечно через скрипт как вебприложение
Касательно URL кодирования - все методы что перепробовал имели свои косяки: ScriptControl - не работает в 64 битных версиях Tablacus Script Control - необходимо чтобы пользователь скачивал и устанавливал его себе WorksheetFunction.encodeURL() - не включена в версии младше 2013 +баг с внутренним счетчиком = выкидывает при превышении длины строки 32677 на входе(а еще важнее что на выходе т.к. заранее не проверить) Написанный на самом VBA енкодер - работает медленно (в 50-70 раз медленнее вышеперечисленных)
Но в конце концов наткнулся на способ через скрипт HTML файла
Который, помимо краткости, работает и быстро и с длинными строками и на всех системах до которых я смог дотянуться (и где затыкались то один то другой способы из перечисленных выше)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]}
функцией
PublicFunction ToArrJSON(rng As Range) AsString ' Make sure there are two columns in the range If rng.Columns.Count < 2Then
ToArrJSON = CVErr(xlErrNA) ExitFunction EndIf
Dim dataLoop, headerLoop AsLong ' Get the first row of the range as a header range Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).Address)
Dim colCount AsLong: colCount = headerRange.Columns.Count
Dim json AsString: json = "["
For dataLoop = 0To rng.Rows.Count ' Include the first header row as well If dataLoop > 0Then ' Start data row Dim rowJson AsString: rowJson = "["
' Strip out the last comma
rowJson = Left(rowJson, Len(rowJson) - 1)
' End data row
json = json & rowJson & "]," EndIf Next
' Strip out the last comma
json = Left(json, Len(json) - 1)
json = json & "]"
ToArrJSON = json EndFunction
З.Ы. Наверное можно проще, например в джаваскрипт я бы просто прошелся по строкам "двумерного" массива join-ом, но с VBA я вообще мало знаком и лень было копаться какая там структура массива/диапазона - поэтому просто по-быстрому переписал под себя первую функцию что нагуглил))
Ну а сам запрос:
Sub sendPOST(sheetName AsString, title AsString, content)
Для корректной работы JSON файл, который я отправляю POST запросом формируется из заданного диапазона ячеек R1C1:RnCn в виде строки {"content":[[r1c1, r1c2, ... ,r1cn],[r2c1, r2c2, ... ,r2cn], ..., [rnc1, rnc2, ... ,rncn]}
функцией
PublicFunction ToArrJSON(rng As Range) AsString ' Make sure there are two columns in the range If rng.Columns.Count < 2Then
ToArrJSON = CVErr(xlErrNA) ExitFunction EndIf
Dim dataLoop, headerLoop AsLong ' Get the first row of the range as a header range Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).Address)
Dim colCount AsLong: colCount = headerRange.Columns.Count
Dim json AsString: json = "["
For dataLoop = 0To rng.Rows.Count ' Include the first header row as well If dataLoop > 0Then ' Start data row Dim rowJson AsString: rowJson = "["
' Strip out the last comma
rowJson = Left(rowJson, Len(rowJson) - 1)
' End data row
json = json & rowJson & "]," EndIf Next
' Strip out the last comma
json = Left(json, Len(json) - 1)
json = json & "]"
ToArrJSON = json EndFunction
З.Ы. Наверное можно проще, например в джаваскрипт я бы просто прошелся по строкам "двумерного" массива join-ом, но с VBA я вообще мало знаком и лень было копаться какая там структура массива/диапазона - поэтому просто по-быстрому переписал под себя первую функцию что нагуглил))
Ну а сам запрос:
Sub sendPOST(sheetName AsString, title AsString, 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