Setup and Access Google Sheets API With Python

Google Cloud Platform Project and Enable API

You need to create a new or use existing Google Cloud Platform project.

Enable Sheets API in API Library.

Setup Credentials

Goto Google Cloud Console -> Credentials -> OAuth consent screen.

Go back to Credentials to create OAuth client ID:

  • Application type: Web application, Android, Chrome App, iOS, Other; I choose Others as I am running a local script.
  • Name: For you to identify the usage of this key

You will be shown client ID and secret. Click OK.

Click Download JSON and save the file as credentials.json.

Install Libraries

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

Code

Read all rows from existing Spreadsheet

  • Google Drive -> New -> Spreadsheets. Insert 2 rows of data into the Sheets.
  • From the URL (e.g. https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0), copy the SPREADSHEET_ID (e.g. 1hlTYOqy10Q-1q32ZEkdeBx_D9y_c283_ZHLvPOcJ6b1)
import pickleimport os.pathfrom googleapiclient.discovery import buildfrom google_auth_oauthlib.flow import InstalledAppFlowfrom google.auth.transport.requests import Request# https://developers.google.com/sheets/api/guides/authorizingSCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']# SCOPES = ['https://www.googleapis.com/auth/spreadsheets']SHEET_NAME = 'Sheet1'SPREADSHEET_ID = '1hlTYOqy10Q-1q32ZEkdeBx_D9y_c283_ZHLvPOcJ6b1'TOKEN_FILE = 'token.pickle'CREDENTIAL_FILE = 'credentials.json'credentials = Noneif os.path.exists(TOKEN_FILE):    with open(TOKEN_FILE, 'rb') as token:        credentials = pickle.load(token)if not credentials or not credentials.valid:    if credentials and credentials.expired and credentials.refresh_token:        credentials.refresh(Request())    else:        flow = InstalledAppFlow.from_client_secrets_file(            CREDENTIAL_FILE, SCOPES)        credentials = flow.run_local_server(port=10800)    # Save the credentials for the next run    with open(TOKEN_FILE, 'wb') as token:        pickle.dump(credentials, token)service = build('sheets', 'v4', credentials=credentials)sheet = service.spreadsheets()# read row 2# RANGE_NAME = f"'{SHEET_NAME}'!A2"# read all rowsrange_notation = SHEET_NAMEresult = sheet.values().get(spreadsheetId=SPREADSHEET_ID,                            range=range_notation).execute()rows = result.get('values', [])if not rows:    print('No data found.')else:    keys = dict((v,k) for k,v in enumerate(rows[0]))    for row in rows[1:]:        # print(row[keys['url']])        print(row)

NOTE: Refer Google Sheets Range A1 Notation.

References:

❤️ Is this article helpful?

Buy me a coffee ☕ or support my work via PayPal to keep this space 🖖 and ad-free.

Do send some 💖 to @d_luaz or share this article.

✨ By Desmond Lua

A dream boy who enjoys making apps, travelling and making youtube videos. Follow me on @d_luaz

👶 Apps I built

Travelopy - discover travel places in Malaysia, Singapore, Taiwan, Japan.