Google sheets automation using python for free

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.

Requirements

• 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 

CODE

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

Happy Programming!!