Connect to Google API to read GoogleSheet

Understanding basics of 1C:Enterprise platform. To start working with 1C:Enterprise platform visit Getting started page

#1
People who like this:0Yes/0No
Active user
Rating: 4
Joined: Mar 14, 2012
Company: GSPD

Dear all,

I want to read data from GoogleSheet on 1C. I try to search on internet but they always guide C#, and I do not know how to do it on 1C, can you help me?

 
#2
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Hello Ninh Giang Thi!

This is easy enough to do.

Suppose we want to read this GoogleSheet: https://docs.google.com/spreadsheets/d/1HeZl6PwpnBAqP4KmUvhMmIDqcx6UEmGwFmpt4lC7­qdE

We go to Google and read the API description: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get

There are all the methods that we may need to work with GoogleSheets.

You will also need an API_KEY.

After that, write in 1C the following code:


Code
&AtServer
Procedure ReadFromGoogleSheetAtServer()

   tempFile = GetTempFileName();
   Connection = New HTTPConnection("sheets.googleapis.com", , , , , ,New OpenSSLSecureConnection());
   
   ID_GoogleSheet = "1HeZl6PwpnBAqP4KmUvhMmIDqcx6UEmGwFmpt4lC7qdE";
   
   Connection.Get("/v4/spreadsheets/" + ID_GoogleSheet + "/values/A1:C7?key=[YOUR_API_KEY]", tempFile);
   
   ReaderJSON = New JSONReader;
   ReaderJSON.OpenFile(tempFile);
   
   data = ReadJSON(ReaderJSON);
   
   ReaderJSON.Close();
   
   For Each Column In data.values Do
      
      For Each Row In Column Do
         
         // In "Row" you will get your value...
         Message(Row);
         
      EndDo;
      
   EndDo;
   
EndProcedure

&AtClient
Procedure ReadFromGoogleSheet(Command)
   ReadFromGoogleSheetAtServer();
EndProcedure



This way you can easily read data from GoogleSheets.

 
#3
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

By the way, we are planning a mailing list with simple development examples.

You can subscribe in the footer on the main page of our website: https://1c-dn.com/

 
#4
People who like this:0Yes/0No
Active user
Rating: 4
Joined: Mar 14, 2012
Company: GSPD

Thank you, Aleksandr Biryukov

 
#5
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Did you manage to read the data?

If you still have any questions, then ask :-)

 
#6
People who like this:0Yes/0No
Active user
Rating: 4
Joined: Mar 14, 2012
Company: GSPD

Thank you, I can read data from GoogleSheet now,
But I do not know how to read each child sheet if in sheet have many child sheet?

 
#7
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

To read different sheets from GoogleSheet, you need to slightly modify the parameters of the HTTP request.

Here is an example of a basic query:

Code
Connection.Get("/v4/spreadsheets/" + ID_GoogleSheet + "/values/A1:C7?key=[YOUR_API_KEY]", tempFile);


Here we read the data from the first sheet from the range A1:C7.

We just need to add to this parameter the name of the sheet from which we want to read data.

For example - 'Sheet 2'!A1:C7

In 1C, it will look like this:

Code
Connection.Get("/v4/spreadsheets/" + ID_GoogleSheet + "/values/'Sheet 2'!A1:C7?key=[YOUR_API_KEY]", tempFile);


That is, changing the parameter 'Sheet 2'! A1: C7 you can access different sheets of GoogleSheet.

 
Subscribe