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!!!