GoogleSheet API

Tram Ho

This article will introduce readers to the Google Sheets API, as well as the common terminology of this API.

Next is a small example using python to load data from google sheet to local and then display this data on the screen.

….

Introducing the Google Sheets API

Google Sheets API as its name implies, this is an api developed by google, this API allows users to read and edit spreadsheet. Users using this API usually have two main ways to interact with spreadsheet:

Both of the above APIs are very easy to use, especially for developers, plus the Reading / writing api of any spreadsheet also offers a lot of options, making it easier to customize as well. to the user’s customzie needs.

Common term

Spreadsheet ID

Each api request requires the required parameter SpreadsheetId , this parameter is used to determine which spreadsheet you will connect to, in the lower example the spreadsheetId is the middle part of / d / and / edit . SpreadsheetId is made up of letters and numbers and comes with a few special characters

https://docs.google.com/spreadsheets/d/ spreadsheetId / edit # gid = 0

Sheet ID

Each sheet in the spreadsheet has a unique title and IDs, sheetId is often used in the Sheets API to determine if the sheet is being read or is being updated. sheetId is the value of the gid parameter. SheetId is made up of numbers and comes with a few special characters

https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid= sheetId

A1 notation (A1 notation)

Sometimes using the api google sheet will have some api that require A1 notation. This notation will refer to group of cells in a spreadsheet, this notation is often used in formula cells. Such as:

  • Sheet1! A1: B2 will refer to the first two cells in the top 2 rows of Sheet1.

Use the google sheet API with Python

Turn on the Google Sheets API (GSA)

To be able to access the google sheet file, the first step is to start GSA, fortunately Google has done all these hard work, my job is just click the button and download the credentials file. .json

Install Google Client library

Next is the library installation step, the libraries used here are google-api-python-client , google-auth-httplib2 , google-auth-oauthlib

Go code

Create a new python file, then import the newly installed libraries

Next you will need to initialize the variables needed to get the data

Here, there are 3 variables which are initialized, the SCOPES variable is used to define the list of scopes that you want to access, the variable SPREADSHEET_ID is used to specify the Spreadsheet you want to access, the variable RANGE_NAME is used to determine you will get data in. sheet and the range in which you will get the data.

token.pickle file will save my login information, this information includes access and refresh token generated when I log in and authorize the application to access google sheet successfully.

The above code will check the data in the token.pickle file and then load these data and then assign the creds variable, the creds variable will be used to verify when calling GSA.

In case the file token.pickle does not exist, or does exist but the data is invalid, then login will be required, if login is successful, the login data will be saved for use next time. The code below will take care of this task

Once the credentials have been obtained, it will be a step to initiate the service to be able to call the api

At this point we can call the Sheets API

If the RANGE_NAME is passed incorrectly, then a HttpError 400 Unable to parse range will be returned

If you pass the SPREADSHEET_ID wrong, there will be 1 HttpError 404 Requested entity was not found.

Ẹnjoy

If passed, both RANGE_NAME and SPREADSHEET_ID

PythonGoogle Sheet
Share the news now

Source : Viblo