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:
- Reading / writing only column values ( api docs link )
- Reading / writing any spreadsheet ( api document link )
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
1 2 3 4 | from googleapiclient.discovery import build from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request |
Next you will need to initialize the variables needed to get the data
1 2 3 4 | SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'] SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms' RANGE_NAME = 'Class Data!A2:E' |
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.
1 2 3 4 5 | creds = None if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: creds = pickle.load(token) |
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
1 2 3 4 5 6 7 8 9 10 | if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( 'credentials.json', SCOPES) creds = flow.run_local_server(port=0) with open('token.pickle', 'wb') as token: pickle.dump(creds, token) |
Once the credentials have been obtained, it will be a step to initiate the service to be able to call the api
1 2 | service = build('sheets', 'v4', credentials=creds) |
At this point we can call the Sheets API
1 2 3 | sheet = service.spreadsheets() result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute() |
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
Python | Google Sheet |
---|---|