Forum

Connect to Google API to read GoogleSheet

Ninh Giang Thi	#1 Nov 12, 2019 05:27 AM
Active user Points:: 0 Joined:: Mar 14, 2012	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?
	Profile

Aleksandr Biryukov

Nov 14, 2019 07:09 PM

Administrator

Points:: 0
Joined:: Oct 3, 2019

Hello Ninh Giang Thi!

This is easy enough to do.

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

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.

Profile

Aleksandr Biryukov	#3 Nov 14, 2019 07:18 PM
Administrator Points:: 0 Joined:: Oct 3, 2019	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/
	Profile

Ninh Giang Thi	#4 Nov 15, 2019 03:57 AM
Active user Points:: 0 Joined:: Mar 14, 2012	Thank you, Aleksandr Biryukov
	Profile

Aleksandr Biryukov	#5 Nov 15, 2019 06:22 AM
Administrator Points:: 0 Joined:: Oct 3, 2019	Did you manage to read the data? If you still have any questions, then ask
	Profile

Ninh Giang Thi	#6 Nov 16, 2019 05:12 AM
Active user Points:: 0 Joined:: Mar 14, 2012	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?
	Profile

Aleksandr Biryukov

Nov 18, 2019 11:11 AM

Administrator

Points:: 0
Joined:: Oct 3, 2019

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.

Profile