Read Data from Google sheet - Python

This Article helps us to how to read the Data from Google Spreadsheet. Also, it make the dictionary in a few lines of code.

Prerequisites

  1. Google Service account
  2. Google spreadsheet with data
  3. Python environment

Download the Client secret json file from Google service and it looks like

  "type": "service_account",
  "project_id": "new_project",
  "private_key_id": "xxxxxxx",
  "private_key": "-----BEGIN PRIVATE KEY-----\xxxxx\n-----END PRIVATE KEY-----\n",
  "client_email": "xxx@developer.gserviceaccount.com",
  "client_id": "xxx",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/xxx%40developer.gserviceaccount.com"
}

Python libraries to be installed

pip install gspread
pip install google-auth

Once installed the library, here we go!!!

import gspread
from google.oauth2.service_account import Credentials

def read_data(request):
    scopes = [
        'https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive'
    ]

    credentials = Credentials.from_service_account_file(
        'secret.json', # Downloaded json from Google service
        scopes=scopes
    )

    gc = gspread.authorize(credentials)

    sht1 = gc.open_by_key("WORKSHEET_ID") # FIND IN THE URL OF YOUR GOOGLE SHEET
    worksheet = sht1.worksheet("WORKSHEET_NAME") 
    list_of_dicts = worksheet.get_all_records()
    item_list = []
    for single in list_of_dicts:
        print(single["Item Name"]) # "Item Name" is the column name from the Sheet
        item_list.append(single["Item Name"])

    return result.return_response(item_list)

Thanks for reading!!!