Внешняя обработка на управляемой форме для 1С версии 8.3 для доступа к сервисам Google Sheets c использованием протокола авторизации OAuth 2.0. Выполняет получение списка листов таблицы гугл, чтение таблицы гугл в табличный документ 1С и запись из табличного документа 1С в таблицу гугл. Тестирование проводилось на платформе 1С:Предприятие 8.3 (8.3.11.2954).

Внимание! В обработке использовано 3 функции из БСП:
ОбщегоНазначенияКлиентСервер.СообщитьПользователю()
СтроковыеФункцииКлиентСервер.ВставитьПараметрыВСтроку()
СтроковыеФункцииКлиентСервер.ПодставитьПараметрыВСтроку()

Обновление

18.05.18. Версия обработки 1.0.2.

Добавлена функция "Удалить строки". Демонстрация возможность удаления строк из таблицы Google указав определенный диапазон индексов строк для удаления.

Функции

Авторизация в google используя протокол OAuth 2.0
1. Запрос для получения authorization code ("Код доступа").
2. Запрос для получения access_token и refresh token.
3. Запрос для получения access_token используя refresh token.
Взаимодействие из 1С с Google Sheets.
1. Получение списка листов таблицы гугл
2. Чтение данных из таблицы гугл и заполнение табличного документа 1С.
3. Запись данных из табличного документа 1С в таблицу гугл.
Дополнительно
1. Сохранение параметров подключения и списка листов таблицы в данных формы.
2. Условное представление элементов в зависимости от срока действия токена.
3. Формирование ссылки для перехода к выбранному листу таблицы при выборе названия листа из списка.

Используемые источники

В качестве основы были использованы обработки для обычных форм разработанные пользователем: echo77.
Некоторая информация была получена из статей на infostart.ru.:
1. Google Analytics API, Oauth2.0 и 1c8
2. Получение доступа к скачиванию гугл-таблицы
Информация с сайта гугл:

Инструкция

Авторизация в google

Получить client ID and client secret

Необходимо используя Google API Console в рамках созданного проекта получить client ID and client secret

Запросить "Код доступа"

Для доступа к данным гугл необходимо при каждом запросе передавать access_token. При этом чтобы получить access_token, необходимо сначало получить authorization code "Код доступа".

В обработке на закладке “Параметры подключения”, при нажатии на “Запросить” — открывается окно браузера и выполняется переход на страницу авторизации гугл. При этом обязательно должны быть указаны “Client id” и “Client secret”, полученные на предыдущем шаге.

Код доступа можно использовать только один раз для получения access_token. Если access_token утерян, необходимо получить новый “Код доступа” который использовать для получения нового access_token (или использовать refresh token для получения нового access token)

Получить access_token

Любой запрос для доступа к данным требует указания access token-а в параметрах запроса. Для первичного получения “access token”, необходимы: “Client id”, “Client secret” и “Код доступа”.

“Aсcess token” имеет непродолжительное время жизни (3600 сек.).После окончания действия, необходимо:

— либо использовать refresh token и обновить access token

— либо опять повторить процесс: получить код доступа, используя новый код доступа — получить новый access token.

При нажатии "Получить" будет выполнен запрос для получения Аccess token и Refresh token, и будет рассчитан “Срок действия токена”

Запрос возвращает .json содержащий требуемую информацию, и токены заполняются в соответствующие поля на форме обработки:

После получения “Access token”, пока он действует можно обращаться к данным в гугл.

Обновить access_token используя refresh_token

После того как истек срок действия “access token” или раньше, можно использовать “refresh token”, чтобы получить новый access token, без использования браузера и получения кода доступа.

Существуют определенные ограничения на использование “refresh token” (подробности по ссылке)

Работа с таблицей гугл (Google Sheet)

Подключение к таблице и получение списка листов таблицы

Необходимо указать ссылку на таблицу гугл в строке “Ссылка на таблицу google”.

При указании ссылки, программа получит “ID таблицы”, выполнит подключение к таблице, прочитает список листов таблицы и заполнит список выбора для поля “Лист”.

При выборе листа из списка — формируется новый URL для доступ к таблице. По кнопке “открыть” выполняется переход в браузере к выбранному листу таблицы.

Чтение данных из таблицы гугл в табличный документ 1С

По кнопке “Прочитать таблицу” выполняется запрос, результат запроса обрабатывается и заполняется табличный документ

Запись данных табличного документа 1С в таблицу гугл

По кнопке “Записать таблицу” выполняется запрос в котором передается информация для записи в таблицу гугл.

18.05.18. Удаление строк из таблицы Google

Для удаления строк необходимо указать диапазон индексов С — ПО. Нумерация с 0-ля. Например для удаления 2-ой строки в таблице необходимо задать диапазон С = 1 ПО = 2.

Под спойлером процедура демонстрирующая удаление строк:

&НаКлиенте
Процедура УдалитьСтрокиОбработка()
ИмяЛиста = Элементы.idЛистаТаблицы.СписокВыбора.НайтиПоЗначению(idЛистаТаблицы).Представление;

Данные = Новый Структура();
Данные.Вставить("requests",Новый Массив);
requests = Данные.requests;

request = Новый Структура("deleteDimension",Новый Структура());
deleteDimension = request.deleteDimension;
deleteDimension.Вставить("range", Новый Структура());
range = deleteDimension.range;
range.Вставить("sheetId",idЛистаТаблицы);
range.Вставить("dimension", "ROWS");
range.Вставить("startIndex",УдалитьИндексС);
range.Вставить("endIndex",УдалитьИндексПО);

requests.Добавить(request);

НастройкиСериализации = Новый НастройкиСериализацииJSON;
НастройкиСериализации.ВариантЗаписиДаты = ВариантЗаписиДатыJSON.ЛокальнаяДатаСоСмещением;
НастройкиСериализации.ФорматСериализацииДаты = ФорматДатыJSON.ISO;

ЗаписьJSON = Новый ЗаписьJSON;
ЗаписьJSON.УстановитьСтроку();
ЗаписатьJSON(ЗаписьJSON, Данные, НастройкиСериализации);
СтрJSON = ЗаписьJSON.Закрыть();

Host = "sheets.googleapis.com";

SSL = Новый ЗащищенноеСоединениеOpenSSL(Неопределено, Неопределено);
Соединение = Новый HTTPСоединение(Host, , , , ,ТаймаутСоединения,SSL);

Заголовки = Новый Соответствие;
Заголовки.Вставить("Authorization", "Bearer " + access_token);

Парам = IdТаблицы + ":batchUpdate";

HTTPЗапрос = Новый HTTPЗапрос("/v4/spreadsheets/" + Парам, Заголовки);
HTTPЗапрос.УстановитьТелоИзСтроки(СтрJSON, КодировкаТекста.UTF8);
HTTPОтвет = Соединение.ОтправитьДляОбработки(HTTPЗапрос);

СтрокаОтвет = HTTPОтвет.ПолучитьТелоКакСтроку();
Если HTTPОтвет.КодСостояния > 299 Тогда
ОбщегоНазначенияКлиентСервер.СообщитьПользователю(
"Не удалось обновить данные таблицы: Код состояния: "+HTTPОтвет.КодСостояния,,,);

Текст = Новый ТекстовыйДокумент;
Текст.УстановитьТекст(СтрокаОтвет);
Текст.Показать("Код_состояния-"+HTTPОтвет.КодСостояния);

Возврат;
КонецЕсли;

КонецПроцедуры

60 Comments

Dzenn 10.04.2018 at 12:36

Спасибо за такие возможности! Не скачивал (эту версию), но уверен, что хорошая, годная разработка. Пользователям 1С теперь доступны чтение/запись гугл таблиц, и это прекрасно.

Reply ↓
Ko1t 10.04.2018 at 13:19

(1) И Вам спасибо за оценку 🙂

Reply ↓
Артано 11.04.2018 at 04:56

Вмемориз

Reply ↓
Duketm 11.04.2018 at 09:17

Хорошая работа. Спасибо. Актуальная вещь

Reply ↓
UncleVader 11.04.2018 at 09:27

Круто, умница

Reply ↓
Ko1t 11.04.2018 at 09:37

Артано Майаров, Евгений Сахненко, Вадим Скакун, спасибо коллеги 🙂

Reply ↓
DrAku1a 11.04.2018 at 10:48

Респект! Очень пригодится!

Reply ↓
Ko1t 11.04.2018 at 11:11

(7) Спасибо, Андрей!

Reply ↓
starik-2005 12.04.2018 at 10:16

Не читал, но + )))

Reply ↓
Ko1t 12.04.2018 at 10:17

(9) Спасибо, 🙂

Reply ↓
logarifm 13.04.2018 at 01:48

Пробежался глазами — годный инструмент но не проверялеще. +

Reply ↓
KAV2 13.04.2018 at 03:24

Smartsheet бы еще!

Reply ↓
Ko1t 13.04.2018 at 09:40

(11) Заранее спасибо 🙂

Reply ↓
Ko1t 13.04.2018 at 09:41

(12) Smartsheet судя по всему классная штука, но для меня на данный момент не актуальная 🙂

Reply ↓
rintik 17.05.2018 at 12:05

Спасибо большое. Работает. Только не могу разобраться как удалять строки.

Reply ↓
Ko1t 17.05.2018 at 12:38

(15) Пожалуйста ).

Ссылка на пример запроса по удалению строк или колонок:

https://developers.google.com/sheets/api/samples/rowcolumn#delete_rows_or_columns

Reply ↓
rintik 17.05.2018 at 13:01

(16) Да пытаюсь разобраться, только у гугла для меня один ответ «»message»: «Invalid JSON payload received. Unknown name «requests»: Cannot find field.»,»

Reply ↓
Ko1t 18.05.2018 at 12:41

Reply ↓
rintik 18.05.2018 at 14:24

(18) Круть. Еще раз спасибо большое.

Reply ↓
kolya_tlt 23.05.2018 at 09:46

нравятся мне проекты ради проектов 🙂

Reply ↓
Ko1t 23.05.2018 at 11:21

(20) как показывает практика, полученный в процессе опыт конвертируется в итоге в материальные и не только блага 🙂

Reply ↓
user658699_heinrich2906 30.06.2018 at 14:55

Подскажите:

1. Код обработки открытый?

2. Реализована ли возможность формировать многостраничную гугл таблицу?

3. Можно ли из обработки прописать пользователей (гугл), которым будет доступна созданная гугл таблица?

Reply ↓
Ko1t 30.06.2018 at 23:07

(24)

1. Код обработки открытый?

> Да

2. Реализована ли возможность формировать многостраничную гугл таблицу?

> Нет

3. Можно ли из обработки прописать пользователей (гугл), которым будет доступна созданная гугл таблица?

> Нет

p.s.

Все что может обработка изложено в описании со скриншотами 🙂

Reply ↓
leobrn 06.07.2018 at 14:21

Если считывать публичную таблицу обязательно ли нужны токены доступа?

Reply ↓
Ko1t 09.07.2018 at 12:05

(26)

Данная обработка при каждого обращении к данным передает в заголовке токен доступа.

Какая авторизация нужна для чтения публичных и приватных данных, а также ответ на ваш вопрос можно получить по ссылке: https://developers.google.com/sheets/api/guides/authorizing

Reply ↓
Ko1t 09.07.2018 at 12:24

(26)

Данная обработка при каждом обращении к данным передает токен доступа.

Я не проверял как будет происходить доступ к публичным данным, но почитать об этом можно по ссылке: https://developers.google.com/sheets/api/guides/authorizing

Reply ↓
torch 02.10.2018 at 17:05

Запустил Вашу обработку. Не могу получить листы моей гугл-таблицы. Ошибка:

{

«error»: {

«code»: 403,

«message»: «Request had insufficient authentication scopes.»,

«status»: «PERMISSION_DENIED»

}

}

Судя по описанию жалуется на способ идентификации. Непонятно… Может сталкивались с этой ошибкой?

Reply ↓
Ko1t 02.10.2018 at 18:17

(31)

Добрый день, судя по ошибке есть какая-то проблема с токеном доступа который вы используете.

Используемый вами токен доступа не позволяет обращаться к запрашиваемым данным.

Я лично с такой ошибкой не сталкивался.

Моя рекомендация будет, заново проделать настройки как это написано в разделе «Инструкция/Авторизация в google», сверяясь с приведенными скринами экрана.

Reply ↓
torch 18.11.2018 at 18:05

Господа, еще один трабл. Я при каждом изменении остатков обновляю гугл-таблицу, но с определенного момента у клиентов в гугл-таблице перестали обновляться данные. У одного из них я почистил кеш и куки — все заработало. Но это не выход — клиентов много и всем объяснять нет возможности, писать инструкции без толку. Предполагаю что надо как-то снести все версии изменений гугл-таблицы и оставить только последнюю, актуальную версию, но не могу найти где это делается. Может кто-то сталкивался с такой проблемой?

Reply ↓
Ko1t 24.11.2018 at 11:59

(33)

Добрый день. Я пробовал искать решения по очистке истории…

В основном сталкивался с такими ответами:

https://productforums.google.com/forum/#!topic/docs/h_NWUzDL3Po

Т.е. удалить историю нельзя программными способами.

История очищается только если делать копию файла.

Reply ↓
kassbar 01.03.2019 at 20:58

(31) «Request had insufficient authentication scopes» не задан ни один параметр scopes очевидно.

Reply ↓
kassbar 01.03.2019 at 21:01

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

например, получить все строки со значением в колонке А1 = 1.

можно ли получить например только колонки А и С ?

Reply ↓
kassbar 01.03.2019 at 22:58

а для чего такая сложная схема с идентификацией ? можно через постоянный API-ключ получать доступ:

https://sheets.googleapis.com/v4/spreadsheets/idтаблицы/?key=АпиКлюч&includeGridData=true

Reply ↓
Ko1t 03.03.2019 at 16:43

(37)

а для чего такая сложная схема с идентификацией ?

В рамках данной обработки реализован рекомендуемый Гуглом метод аутентификации OAuth 2.0 описанный по ссылке: https://developers.google.com/identity/protocols/OAuth2

можно через постоянный API-ключ получать доступ

Я не уверен что правильно понял, что вы имеете ввиду, но мне было бы интересно узнать подробнее.

Сбросьте пжл. ссылки где можно изучить детали про такой способ подключения? Может быть он более простой, но менее безопасный?

Reply ↓
kassbar 03.03.2019 at 19:22

(38)

Я не уверен что правильно понял, что вы имеете ввиду

я же написал запрос, которым можно получить доступ к опубликованной по ссылке таблице, зная ее id и свой apikey.

вводите в этом запросе id таблицы, api-ключ гугла и получаете ее в json.

Лично мне не требовалось вносить в нее изменения, только читать данные, поэтому я дальше не копал.

Reply ↓
Ko1t 03.03.2019 at 21:53

(36)

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

например, получить все строки со значением в колонке А1 = 1.

Судя повсему используя текущий API это не получиться сделать. Доступны только следующие возможности фильтации при запросе данных:

https://developers.google.com/sheets/api/reference/rest/v4/DataFilter

Что-то похожее можно сделать для данных отображаемых при просмотре используя Filters

Возможно если программно выполнить SetBasicFilterRequest и затем чтение из таблицы, будут прочитанных отфильтрованные данные… надо пробовать.

можно ли получить например только колонки А и С ?

https://developers.google.com/sheets/api/guides/concepts#a1_notation

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGetByDataFilter

Судя по всему, что используя batchGetByDataFilter, можно задать отдельные dataFilters[], и получить только колонки А и С

Reply ↓
kassbar 04.03.2019 at 00:13

(40)

Возможно если программно выполнить SetBasicFilterRequest и затем чтение из таблицы, будут прочитанных отфильтрованные данные… надо пробовать.

пробовал, через batchupdate ставил фильтры — не работает, возвращает всю таблицу.

Как-то очень печально такое от гугла видеть.

Пришлось через запросы «select…» к таблице получать нужные данные

«https://docs.google.com/spreadsheets/d/id_таблицы/gviz/tq?tqx=out:html&tq=select*»

выгрузить в csv: «tqx=out:csv»

работает только для опубликованных «по ссылке» таблиц

Reply ↓
kassbar 04.03.2019 at 17:57

Еще вопрос, получалось ли у кого-то выгрузить не публичный файл типа «таблица Гугл» (доступ открыт пользователю, публичной ссылки нет) через API GoogleDrive ?

У меня запросы типа

Новый HTTPЗапрос(«drive/v3/files/» +idТаблицы+»/export/?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet»)

всегда возвращает ошибку «Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup».

Подключил API Google+ и API Google Drive с областью действия «https://www.googleapis.com/auth/drive»

Reply ↓
simy4 16.03.2019 at 11:32

Укажите пожалуйста в вашей публикации, что для функционирования обработки требуется наличие БСП. Для меня данное требование оказалось сюрпризом. Неприятным.

Reply ↓
Ko1t 16.03.2019 at 14:47

(43) Добрый день.

Да, действительно в обработке использовано 3 функции, которые входят в состав БСП:

ОбщегоНазначенияКлиентСервер.СообщитьПользователю()

СтроковыеФункцииКлиентСервер.ВставитьПараметрыВСтроку()

СтроковыеФункцииКлиентСервер.ПодставитьПараметрыВСтроку()

Как мне кажется, основные сценарии использования данной обработки предполагают наличие данных функций в составе конфигурации, из которой данная обработка запускается.

В противном случае, мне кажеться исправление проблемы занимает относительно мало времени.

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

Но да, я согласен что следует упоминуть в описание обработки данный нюанс, что я и сделаю в ближайшее время.

Reply ↓
German_Tagil 19.03.2019 at 06:13

Добавил в избранное…

меня заинтерисовало — переходим на БП 3.0

на КА 1.1 у меня была допиленная обработка https://infostart.ru/public/66842/

Может кто-то находил подобное на УФ?

Reply ↓

Ko1t 19.03.2019 at 10:58

(45) Добрый день, если я правильно понял что вам надо, то это функционал из БСП

https://its.1c.ru/db/bsp302doc#content:1867:1

5.18 Загрузка данных из файла
5.18 Загрузка данных из файла Программа предоставляет возможность загрузки табличных данных из файлов в произвольные списки (справочники) и табличные части документов. Например, для разового переноса нормативно-справочной информации при переходе с других программ, или для регулярной загрузки данных в табличные части при заполнении документов. Можно легко расширять варианты загрузок без вмешательства в конфигурацию через подключение внешних обработок. Подробнее см. раздел документации Дополнительные отчеты и обработки. Загрузка данных из файла: ■ позволяет задействовать для ввода данных возможности других программ (например, можно вводить данные в Excel, а потом загрузить их в программу); ■ не требует для ввода данных в файл наличия на компьютере программы 1С:Предприятие, поэтому ввод данных в такой файл может осуществляться в удаленных офисах, небольших складах, точках розничных продаж, т.е. там, где может не быть возможности наладить полноценную работу программы 1С:Предприятие (например, компьютер уже устарел для работы с данной программой); ■ позволяет подключить к вводу данных дополнительные кадры. Ввод данных во внешний файл требует только соблюдения структуры таблицы, а также знания правил ввода в той программе, для которой он предназначен, например, Excel, поэтому может быть поручен сотрудникам, которые имеют только начальные навыки работы с компьютером.

5.18 Загрузка данных из файла

5.18 Загрузка данных из файла

Программа предоставляет возможность загрузки табличных данных из файлов в произвольные списки (справочники) и табличные части документов. Например, для разового переноса нормативно-справочной информации при переходе с других программ, или для регулярной загрузки данных в табличные части при заполнении документов.

Можно легко расширять варианты загрузок без вмешательства в конфигурацию через подключение внешних обработок. Подробнее см. раздел документации Дополнительные отчеты и обработки.

Загрузка данных из файла:

■ позволяет задействовать для ввода данных возможности других программ (например, можно вводить данные в Excel, а потом загрузить их в программу);

■ не требует для ввода данных в файл наличия на компьютере программы 1С:Предприятие, поэтому ввод данных в такой файл может осуществляться в удаленных офисах, небольших складах, точках розничных продаж, т.е. там, где может не быть возможности наладить полноценную работу программы 1С:Предприятие (например, компьютер уже устарел для работы с данной программой);

■ позволяет подключить к вводу данных дополнительные кадры. Ввод данных во внешний файл требует только соблюдения структуры таблицы, а также знания правил ввода в той программе, для которой он предназначен, например, Excel, поэтому может быть поручен сотрудникам, которые имеют только начальные навыки работы с компьютером.

Reply ↓

German_Tagil 20.03.2019 at 05:42

Доступ к данному материалу ограничен

Reply ↓
Mogilnikova 20.03.2019 at 16:04

Добавила в избранное. Однажды ненастным днем оно мне пригодится!

Reply ↓
German_Tagil 21.03.2019 at 10:35

(48) угу я точно также поступаю

Reply ↓
Lera_1c 01.08.2019 at 11:08

Очень пригодилось! Все разжевано, благодарю!

Reply ↓
Ko1t 01.08.2019 at 12:12

(50) Спасибо! 🙂

Reply ↓
skyboy13 06.09.2019 at 17:46

А на автоматическое обновление таблицы в регламентных задачах ее заточить можно?

Reply ↓
Ko1t 08.09.2019 at 11:23

(52) возможно для таких задач лучше подойдет такое решение как по ссылке https://infostart.ru/public/805071/ (хотя я сам лично пока его не изучал досконально)

Reply ↓
Cthulhu 09.10.2019 at 13:55

(53) не т.е. если оттуда аккуратно «дернуть» блок авторизации и заменить в этом решении — то его можно будет использовать для регламентных заданий — я правильно понимаю?..

Reply ↓
Ko1t 12.10.2019 at 11:07

(54) Для регламентных обработчиков, в теории (я не пробовал), я полагаю также можно использовать и OAuth 2.0 (именно его я использую в моей публикации).

Сценарий при этом может быть такой:

1. Интерактивно: Получаем Код доступа (требуется ручной ввод данных)

2. Автоматически: Получаем и сохраняем access_token и refresh_token на основании Кода доступа

3. Автоматически: По расписанию: Выполняем необходимую обработку данных в гугл таблицах, при этом контролируя время жизни access_token.

4. Автоматически: При окончании срока действия: Обновляем access_token используя refresh_token

5. Автоматически: Если что-то пошло не так: Отправляем оповещение на почту ответственному за процесс, чтобы он вмешался интерактивно, и например заново запросил Код доступа.

6. Интерактивно: Если требуется: Переходим к Шаг-1.

Reply ↓

Cthulhu 12.10.2019 at 11:19

(55) о. т.е. стартовое получение рефреш-токена дает возможность в дальнейшем в любое время (без повторного диалогового логина) программно получать свежий токен и с ним обновлять целевую гугл-таблицу, да? пожалуй, так и бум делать…

а переноса форматирования нет — я правильно понимаю?.. наверное, оно и понятно — там нешуточное ковыряние в api команд подразумевается — поэтому, да?..

и — спасибо огромное, очень толковое решение!

…
(и — за что отдельный респект — предельно корректное общение по существу с аккуратными и конкретными формулировками — сие мне, как перфекционисту, особо радует глаз и Cortex praefrontalis)