Universal expansion 1C for Google Spreadsheets and Documents - take and use
This article is for those who use G Suite and 1C.
PIK Digital is an IT company that makes construction technological. Last year we transferred the PIK Group of Companies to the G Suite . In order to fully work with Google Spreadsheets and Documents, it was necessary to refine our systems.
One of these improvements is the extension for uploading reports and 1C forms to Google Disk in the format of Google Tables and Google Docs . In the article we will share a ready-made solution and useful information on its implementation. Expansion is free. Take and use.
Download extension
View code on github
To work expansion will require:
Next, we consider each stage separately and give code examples.
To work you need a Google account . Without it, the extension will not be able to interact with the G Suite services. Already have a valid account - use it.
To activate access to the Google Drive API, you need to create a project in the Google Cloud Platform (GCP). There are two options for free work with GCP:
Both methods have limitations, more about them described in here . We recommend the option with a 12-month trial period. After the trial period expires, the money will not be debited unless you start using paid services. Everything you need for integration work for free.
We register the project using the console.cloud.google.com link, click “Select a project”, then “Create a project”. After creating the project, select it by pressing the button “Select project” again.
Create credentials to connect to G Suite using OAUth 2.0 protocol. In GCP, we go to the menu item "API and services" - "Credentials" - "Create credentials" - "OAUth key identifier".
You will be prompted to create an access request window. This window is formed when the user is asked permission to access his data.
Leave the “Open Access” access type and enter the name of the application, which will be displayed when the employee starts the project and requires consent to access the data. Click “Save”.
After that, set the "Application Type". Select Other Types and enter your OAuth Client ID. Please note that this is not the same as the display name of the application. As a result, a window opens with the ID and secret of the client. Save them, this is important.
To gain access to the API functionality, go to the “API Library” menu item, find the required library and enable it.
Since we want to work with the G Suite formats, we need to connect the Google Drive API library to the project. In addition, we need the Google Sheets API library, Google Docs API. Find and connect it in the same way.
Now it is possible to form requests to the API. Recall: our task is to upload data from the 1C report of a standard form to Google Spreadsheet or Google Document.
Download extension 1C.
In 1C there is an opportunity to independently install configuration extensions. If this is your first challenge, watch our short video on how to do it.
Our extension works with the platform starting with version 8.3.10, on configurations of BP (starting with 3.0.65 and higher), ERP (starting with 2.4.6 and higher) and UT (starting with 11.4.6 and higher).
If you see a security warning when you install the extension, click Yes. You will see a message stating that "The current execution of the action was interrupted to issue a warning." This means that you have enabled the safe mode, therefore, the procedure for adding an extension must be repeated, and the safe mode must be disabled. After adding restart 1C.
If the installation was successful, then you will see the "Save to Google Drive" button in reports and print forms.
It remains to configure the settings for connecting to the Google API, and you can work. Again select the menu item "All functions" and in the section "Processing" look for the item "Connection parameters to Google API."
We fill in the Clientid, Clientsecret and “Port” fields with the values obtained when creating a project in GCP. By default, the file name and the folder where the temporary keys used for the connection will be stored are substituted. Key data (refresh and access-tokens) of a user is saved for security purposes in the current user's temporary files directory, in the folder <folder name> and in the file <file name>. If the same Google project is created in different configurations, you can make the file name the same in all of them, so as not to create multiple files with the same information. When you save the connection settings are stored in the storage of general settings.
Now you can save reports in Google Spreadsheets and Google Docs formats on Google Drive .
When you first try to save a report, you will need to go through a one-time user authorization procedure in each configuration in order for the service to receive access rights to save data.
In the window that appears, enter your Gmail email address and password. Further
in the window that opens, click "Allow". So you give the application to save information in your Google Drive.
You will need to enter the file name, select its type and directory to save on a shared or private disk. Click “Save”. After saving the report will open in the browser. Enjoy watching.
Now let's see how this works inside the 1C platform.
To connect, the following query line is generated to the Google service:
client_id— customer ID, in which we previously saved the value of a variable.
scope - addresses of the API libraries that we will use. If you need to connect multiple libraries, list them separated by spaces.
This query string is needed for a one-time connection of our client application to the Google service. You can enter the query string in the browser, in response, the service will return a URL similar to the following:
You can add a field for HTML data in the 1C form designer and execute a query through it. We need from this URL to get the value of <CODE> - service access code.
It remains to receive two tokens by making an HTTP request. As a result, the query returns a string with the access and refresh values in JSON format. Refresh-token does not lose relevance over time, and access-token lives 60 minutes and then requires updating. To obtain these tokens, we will need the just received <CODE>, as well as the client identifier and secret that we saved earlier.
The code could be something like this:
The expires_in variable contains the lifetime of the access token.
The following is a request to update the token:
Now let's see how to save a spreadsheet document on Google Drive and then convert it to Google Tables format.
This code uses the multipart upload method to upload a file.
We form a POST request from two data blocks. In the first block, write the title and mimeType of the file. In the second block, the contents of the file are transferred. Blocks are defined by separators, which are specified at the end and beginning of the block.
Learn more about this in the article on inserting Google Drive knowledge base files.
This is how it works. We hope that now you will not waste time converting the data unloaded from 1C.
The author of our extension is the leading developer of PIK Digital Brazhnikova Maria mbrazh
PIK Digital is an IT company that makes construction technological. Last year we transferred the PIK Group of Companies to the G Suite . In order to fully work with Google Spreadsheets and Documents, it was necessary to refine our systems.
One of these improvements is the extension for uploading reports and 1C forms to Google Disk in the format of Google Tables and Google Docs . In the article we will share a ready-made solution and useful information on its implementation. Expansion is free. Take and use.
Download extension
View code on github
To work expansion will require:
- Sign up for a Google Account
- Create a project in the Google Cloud Platform
- Create OAuth Client
- Configure access to Google Drive API
- Install extension 1C
- Set up a connection to Google API in 1C
Next, we consider each stage separately and give code examples.
Google Account Registration
To work you need a Google account . Without it, the extension will not be able to interact with the G Suite services. Already have a valid account - use it.
Creating a project in GCP
To activate access to the Google Drive API, you need to create a project in the Google Cloud Platform (GCP). There are two options for free work with GCP:
- A 12-month trial period with a budget of $ 300.
- Always Free — access most GCP resources.
Both methods have limitations, more about them described in here . We recommend the option with a 12-month trial period. After the trial period expires, the money will not be debited unless you start using paid services. Everything you need for integration work for free.
We register the project using the console.cloud.google.com link, click “Select a project”, then “Create a project”. After creating the project, select it by pressing the button “Select project” again.
Creating an OAuth Client
Create credentials to connect to G Suite using OAUth 2.0 protocol. In GCP, we go to the menu item "API and services" - "Credentials" - "Create credentials" - "OAUth key identifier".
You will be prompted to create an access request window. This window is formed when the user is asked permission to access his data.
Leave the “Open Access” access type and enter the name of the application, which will be displayed when the employee starts the project and requires consent to access the data. Click “Save”.
After that, set the "Application Type". Select Other Types and enter your OAuth Client ID. Please note that this is not the same as the display name of the application. As a result, a window opens with the ID and secret of the client. Save them, this is important.
Connecting the Google Drive API library
To gain access to the API functionality, go to the “API Library” menu item, find the required library and enable it.
Since we want to work with the G Suite formats, we need to connect the Google Drive API library to the project. In addition, we need the Google Sheets API library, Google Docs API. Find and connect it in the same way.
Installing an extension in user mode
Now it is possible to form requests to the API. Recall: our task is to upload data from the 1C report of a standard form to Google Spreadsheet or Google Document.
Download extension 1C.
In 1C there is an opportunity to independently install configuration extensions. If this is your first challenge, watch our short video on how to do it.
Our extension works with the platform starting with version 8.3.10, on configurations of BP (starting with 3.0.65 and higher), ERP (starting with 2.4.6 and higher) and UT (starting with 11.4.6 and higher).
If you see a security warning when you install the extension, click Yes. You will see a message stating that "The current execution of the action was interrupted to issue a warning." This means that you have enabled the safe mode, therefore, the procedure for adding an extension must be repeated, and the safe mode must be disabled. After adding restart 1C.
If the installation was successful, then you will see the "Save to Google Drive" button in reports and print forms.
Setting parameters for connecting to Google API in 1C
It remains to configure the settings for connecting to the Google API, and you can work. Again select the menu item "All functions" and in the section "Processing" look for the item "Connection parameters to Google API."
We fill in the Clientid, Clientsecret and “Port” fields with the values obtained when creating a project in GCP. By default, the file name and the folder where the temporary keys used for the connection will be stored are substituted. Key data (refresh and access-tokens) of a user is saved for security purposes in the current user's temporary files directory, in the folder <folder name> and in the file <file name>. If the same Google project is created in different configurations, you can make the file name the same in all of them, so as not to create multiple files with the same information. When you save the connection settings are stored in the storage of general settings.
Saving a report
Now you can save reports in Google Spreadsheets and Google Docs formats on Google Drive .
When you first try to save a report, you will need to go through a one-time user authorization procedure in each configuration in order for the service to receive access rights to save data.
In the window that appears, enter your Gmail email address and password. Further
in the window that opens, click "Allow". So you give the application to save information in your Google Drive.
You will need to enter the file name, select its type and directory to save on a shared or private disk. Click “Save”. After saving the report will open in the browser. Enjoy watching.
The technical side of the issue
Now let's see how this works inside the 1C platform.
To connect, the following query line is generated to the Google service:
АдресПодключения="https://accounts.google.com/o/oauth2/auth"+"?" + "response_type=code" + "&client_id=" + ид_клиента + "&redirect_uri=http://localhost" + "&access_type=offline" + "&scope=https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive.apps.readonly https://www.googleapis.com/auth/drive.file https://www.googleapis.com/auth/drive.appdata https://www.googleapis.com/auth/drive.metadata; client_id— customer ID, in which we previously saved the value of a variable.
scope - addresses of the API libraries that we will use. If you need to connect multiple libraries, list them separated by spaces.
This query string is needed for a one-time connection of our client application to the Google service. You can enter the query string in the browser, in response, the service will return a URL similar to the following:
http://localhost/?code=<КОД>&scope=https://www.googleapis.com/auth/admin.directory.user%20https://www.googleapis.com/auth/admin.directory.orgunit You can add a field for HTML data in the 1C form designer and execute a query through it. We need from this URL to get the value of <CODE> - service access code.
It remains to receive two tokens by making an HTTP request. As a result, the query returns a string with the access and refresh values in JSON format. Refresh-token does not lose relevance over time, and access-token lives 60 minutes and then requires updating. To obtain these tokens, we will need the just received <CODE>, as well as the client identifier and secret that we saved earlier.
The code could be something like this:
Сервер = "accounts.google.com"; Ресурс = "/o/oauth2/token"; СтрокаЗапроса = СтрШаблон("client_id=%1&client_secret=%2&grant_type=authorization_code&code=%3&redirect_uri=http://localhost", ид_клиента, секрет_клиента, КодДоступа); Соединение = Новый HTTPСоединение(Сервер,443,,,,,Новый ЗащищенноеСоединениеOpenSSL); Заголовки = Новый Соответствие; Заголовки.Вставить("Content-Type","application/x-www-form-urlencoded”) ЗапросHTTP = Новый HTTPЗапрос(Ресурс,Заголовки); ЗапросHTTP.УстановитьТелоИзСтроки(СтрокаЗапроса); Ответ = Соединение.ВызватьHTTPМетод("POST", ЗапросHTTP); Если НЕ Ответ.КодСостояния = 200 Тогда ОбщегоНазначенияКлиентСервер.СообщитьПользователю(СтрШаблон("Ошибка получения параметров авторизации: %1", Ответ.ПолучитьТелоКакСтроку())); Возврат; КонецЕсли; Строка = Ответ.ПолучитьТелоКакСтроку(); Чтение = Новый ЧтениеJSON(); Чтение.УстановитьСтроку(Строка); Фабрика = ФабрикаXDTO.ПрочитатьJSON(Чтение); Чтение.Закрыть(); ТокенДоступа = Фабрика.access_token; ТокенОбновления = Фабрика.refresh_token; ВремяЖизниТокена = Фабрика.expires_in; The expires_in variable contains the lifetime of the access token.
The following is a request to update the token:
Сервер = "accounts.google.com"; Ресурс = "/o/oauth2/token"; ТокенОбновления = GoogleНастройки.refresh_token; ид_клиента = GoogleНастройки.client_id; секрет_клиента = GoogleНастройки.client_secret; СтрокаЗапроса = "grant_type=refresh_token" + "&client_id=" + ид_клиента + "&client_secret=" + секрет_клиента + "&refresh_token=" + ТокенОбновления; Saving a tabular document to Google Drive
Now let's see how to save a spreadsheet document on Google Drive and then convert it to Google Tables format.
//разделитель для HTTP сообщения Разделитель = "file_for_drive"; //определяем HTTP заголовок по типу файла ЗаголовокПоТипуФайла = СоответствиеТипуФайлаЗаголовкамGoogle()[ТипФайла]; ЗаголовкиОсновногоЗапроса.Вставить("Content-Type", "multipart/related; boundary=" + Разделитель); //----- //первый запрос //----- //заголовки Заголовки = Новый Массив; Заголовки.Добавить("Content-Type: application/json; charset=UTF-8"); //запрос метаданных //формируем json с параметрами имя файла, заголовок и родитель ТелоЗапроса = СформироватьТелоЗапросаСозданиеФайлаGSs(ИмяФайла, ЗаголовокПоТипуФайла, id_папки); ДвоичныеДанныеСообщения = GoogleAPI_ОбщегоНазначения.СоздатьСообщение_Текст(Заголовки, ТелоЗапроса); //----- //конец первого запроса //----- //----- //второй запрос //----- //заголовки Заголовки.Очистить(); Заголовки.Добавить("Content-Type: " + ЗаголовокПоТипуФайла); Заголовки.Добавить("uploadType: media"); //запрос метаданных ВременныйФайл = ПолучитьИмяВременногоФайла(ТипФайла); ТабДок.Записать(ВременныйФайл, ТипТабличногоДокументаПоТипуФайла()[ТипФайла]); ДвоичныеДанныеФайла = GoogleAPI_ОбщегоНазначения.СоздатьСообщение_Файл(Заголовки, Новый ДвоичныеДанные(ВременныйФайл)); //----- //конец второго запроса //----- //----- // Формируем основное составное сообщение. МассивСообщений = Новый Массив; МассивСообщений.Добавить(ДвоичныеДанныеСообщения); МассивСообщений.Добавить(ДвоичныеДанныеФайла); ДвоичныеДанныеТело = GoogleAPI_ОбщегоНазначения.ПолучаемДвоичныеДанныеДляПакетныхСообщенийHTTP(Разделитель, МассивСообщений); //----- ЗапросHTTP = Новый HTTPЗапрос("/upload/drive/v2/files" + "?uploadType=multipart&convert=true",ЗаголовкиОсновногоЗапроса); ЗапросHTTP.УстановитьТелоИзДвоичныхДанных(ДвоичныеДанныеТело); Ответ =Соединение.ВызватьHTTPМетод("POST", ЗапросHTTP); Если Не Ответ.КодСостояния = 200 Тогда ТекстОшибок = "Ошибка HTTP запроса " + Ответ.ПолучитьТелоКакСтроку() + ", код ошибки " + Ответ.КодСостояния; GoogleAPI_ОбщегоНазначения.ЗаписьОшибкиПриРаботеСGS(ТекстОшибок, ". Запись документа"); Возврат ""; КонецЕсли; HTTPСоединение = Неопределено; ЗапросHTTP = Неопределено; Попытка УдалитьФайлы(ВременныйФайл); Исключение ЗаписьЖурналаРегистрации("Удалить файлы",УровеньЖурналаРегистрации.Ошибка,,,ОписаниеОшибки()); КонецПопытки; ОтветHTTPТело = Ответ.ПолучитьТелоКакСтроку(); ОбъектJSON = GoogleAPI_ПроцедурыРаботыСGSheets.СформироватьЗначениеОбъектаJSON(ОтветHTTPТело); //возвращаем ссылку на документ из google drive Возврат GoogleAPI_ПроцедурыРаботыСGSheets.ПрочитатьСвойствоВСтрокеОбъектаJSON(ОбъектJSON, "alternateLink"); This code uses the multipart upload method to upload a file.
We form a POST request from two data blocks. In the first block, write the title and mimeType of the file. In the second block, the contents of the file are transferred. Blocks are defined by separators, which are specified at the end and beginning of the block.
Learn more about this in the article on inserting Google Drive knowledge base files.
This is how it works. We hope that now you will not waste time converting the data unloaded from 1C.
The author of our extension is the leading developer of PIK Digital Brazhnikova Maria mbrazh
Source: https://habr.com/ru/post/437824/