In this example we will use python to read data from Google Sheets and update another one using the Google API.
Enable the Google API
- Navigate to https://console.developers.google.com/
- Log in with your Google account
- Click the Create Project link
- Enter a project name, you can have 12 free project.
- Click the API panel
- Click the ENABLE APIS AND SERVICES link
- In the G Suite section click theVIEW ALL link
- Click the Google Sheets API panel
- Click the Enable button
- Navigate back to the API panel list and click the Google Drive API panel
- Click the Enable button
Create API credentials
OAuth 2.0 Client credentials
Create the Consent Screen
First, we need to create a Consent Screen before we create our first OAuth 2.0 credential
- On the left side select Credentials, click the CREATE CREDENTIALS button and select OAuth client ID
- Click the CONFIGURE CONSENT SCREEN
- Select External and click the CREATE button
- Enter the application name, select an email address from the list, enter the developer’s email address and click the SAVE AND CONTINUE button
- Add a Space
- Select ../auth/drive.file
- Click SAVE AND CONTINUE
- Save again
Create the OAuth 2.0 credential
- Create the credential
- In our case, we build a desktop app
- Enter a Name and click the CREATE button
- Close the popup
Download the OAuth2 credentials
- On the Credentials page click the name of the OAuth 2.0 credential name
- Click the DOWNLOAD JSON button
Read data from Google Sheets with Python
We will use the Google tutorial at https://developers.google.com/sheets/api/quickstart/python
- Copy the downloaded OAuth 2.0 credentials file to the application directory.
- Add the file name to the .gitignore file to avoid committing it to GitHub
- Enter the downloaded credentials file name in the line
flow = InstalledAppFlow.from_client_secrets_file(
'MY_OAUTH_CRED_FILE.json', SCOPES)- Set the Google sheet ID and data range in
SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
SAMPLE_RANGE_NAME = 'Class Data!A2:E'
- The rows of your Google sheet are stored in rows of the values[] array. Adjust the row displayed column numbers in the line
print('%s, %s' % (row[0], row[4]))Run the application
- The app will open a page in the default web browser and will ask for a Google login
- Click Advanced
- Click the Go to … link
- Grant the permission
- Allow the access
- Confirmation is displayed
- Your application should display the data from the Google spreadsheet
To use a service account to read Google Sheets
Create a Google Service account
- Click the CREATE CREDENTIALS button
- Select the Google Drive API, Other non-UI, Appication data, No, I’m not using them options
- Click the What credentials do I need? button
- For the Role select Project, Owner
- Keep JSON as the private key format, click the Continue button
- The JSON private key downloads, save it to be able to access the API
Share the Google sheet with the Service Account
- Open the downloaded JSON key file and copy the client_email value
- Open the Google Sheet you want to read from
- Click the Share button
- Make the service account a Viewer to read the data, but if you later want to update the sheet with the same account make the service account an Editor
Reading Google sheets with a Service Account
Install the gspread, oauth2client, and pandas Python libraries on your workstation
- Open a terminal window and execute the commands
pip3 install gspread
pip3 install --upgrade google-api-python-client oauth2client
pip3 install pandas- Save the code in a .py file
# import the libraries
import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
# define the scope
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
# get the credentials from the JSON file
creds = ServiceAccountCredentials.from_json_keyfile_name('MY_CREDENTIALS_FILE.json', scope)
# authorize service account in the the clientsheet
client = gspread.authorize(creds)