Google sheets are commonly used by many people. In this article, I am going to show you how I used python in order to automate the google sheets. Let’s get started!!
In my previous article, I shared how to use your Gmail to send emails programmatically.
Let’s imagine you have a google sheet named, “Daily log sheet” which contains 3 columns namely date, time spent, and description. You would like to automate it.
- Google service account and credential JSON file
- Gspread and oauth2client python library
Google service account and credential JSON file
- Go to console.cloud.google.com and create a new project if you do not have one already.
- Find “Api & Services” at the left navigation menu and find library.
- Search services Google drive, google sheets and enable it.
- Go to “IAM & Admin” and select “Service accounts” and click “create service account” at top.
- Enter a service account name and click done.
- Under action click Manage keys as shown below.
- Click “ADD KEY” dropdown and select “Create new key”.
- Select json and click create. Store the download JSON in your system.
- Copy the client_email from the JSON and go to the google sheet and click the share button on the right top and paste the email id.
Gspread and oauth2client python library
- Execute the following pip command before executing the code.
- pip install gspread
- pip install oauth2client
Find this code on my GitHub repository also.
Paste the JSON file that you downloaded and rename it as creds.json. Save it in the same directory where the python file is present.
Note: The following code is written in such a way it is compatible with AWS Lambda. You can modify according to your needs
import gspread from oauth2client.service_account import ServiceAccountCredentials from pprint import pprint from datetime import datetime scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"] creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json",scope) client = gspread.authorize(creds) slots_sheet = client.open("Daily log sheet").worksheet("logs") def lambda_handler(event,context): # get all records data = slots_sheet.get_all_records() print(data) # get row 2 record row = slots_sheet.row_values(2) print(row) # get column 2 records col = slots_sheet.col_values(2) print(col) # get 1st row 2nd column value cell = slots_sheet.cell(1,2).value print(cell) # insert a record at the 3rd row currentDate = str(datetime.utcnow().date()) # insert_row = [currentDate,1,"learnt to access google sheets using python"] # slots_sheet.insert_row(insert_row,3) # delete the record at the 3rd row # slots_sheet.delete_rows(3) return data pprint(lambda_handler("",""))
Check the following documentation for more API functionalities – https://developers.google.com/sheets/api/quickstart/python