Read Data from Google sheet

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

Google Service account
Google spreadsheet with data
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!!!

Read Data from Google sheet - Python