How to Automate Google Sheets with Python

How to use pygsheets python package to play around google sheets and to automate.

Google spreadsheets are easy to maintain, edit, and share with people with python package pygsheets. I have been using pygsheets for long time to automate my daily work in google spreadsheets.

pygsheets is a simple intuitive python library to access google spreadsheets through the Google Sheets API v4.

Automating Google Sheets with python is not as hard as climbing Mount Fuji. 😉

A picture from my Mount Fuji trekking.

Everyone knows what google spreadsheets are and how to use them. In this article , we will learn how to play around google spreadsheets with python. So, without further ado, let’s start.

Installation

pip install pygsheets

Get client secret

Obtain OAuth2 credentials from Google Developers Console for google spreadsheet api and drive api and save the file as client_secret.json in same directory as project. See complete guide here.

Authorization

import pygsheets
gc = pygsheets.authorize() 
# Use customized credentials 
gc = pygsheets.authorize(custom_credentials=my_credentials)
# For the first time, it will may produce as a link to authorize

Open spreadsheets and worksheets

Google spreadsheets can be opened by name, id , and link. Worksheets can be accessed by name or index.

How to open a spreadsheet and worksheet with pygsheets.

Playing around spreadsheet

Authorize and open a spreadsheet

import pygsheets
gc = pygsheets.authorize()
sh = gc.open('medium') # Open a spreadsheet with name 'medium'.

Get spreadsheet title

sh.id     # Returns id of spreadsheet

Get spreadsheet id

sh.title      # Returns title of spreadsheet

Get spreadsheet url

sh.url     # Returns url of spreadsheet

Check last update

sh.updated  # Returns date and time of last update

Delete spreadsheet

sh.delete() # Delete spreadsheet

Get worksheets info

sh.worksheets() # Return information of worksheets

Share spreadsheet

sh.share('example@gmail.com', role='commenter', type='user', emailMessage='Here is the spreadsheet we talked about!')
sh.share('', role='reader', type='anyone') # Make public

Remove permissions

sh.remove_permission('example@gmail.com', permission_id=None)
# You can specify permission id

Add new worksheet

sh.add_worksheet('sheet3',rows=250, cols=20) 

Delete worksheet

sh.del_worksheet('sheet3')

Playing around Worksheet

Open a worksheet

wk1 = sh.sheet1 or wk1 =sh[0] # Open first worksheet

Get title, id, and url of worksheet

wk1.title       # Returns title of worksheet
wk1.id          # Returns id of worksheet
wk1.url         # Returns url of worksheet

Get rows and cols count

wk1.rows # returns number of rows
wk1.cols # returns number of columns 

Get cell object and cell value

wk1.cell((row_number,col_number))       # Returns cell object
wk1.cell((row_number,col_number)).value # Returns cell value as string

Get value/values/records

wk1.get_value('A1')        # Returns A1’s value
wk1.get_value('A1', 'B2')  # Returns list of values 
wk1.get_all_values()       # Returns list of all values in worksheet
wk1.get_all_records()      # Returns a list of dictionaries

Example of get_all_records

Update value/values

wk1.update_value('A8', '40')  # Updates A8 with 40
or 
wk1.update_value('A8','=A6+A7',True)#Updates A8 with sum of A6 and A7
wk1.update_values('A8', [['G',40]]) # Updates values in starting from A8

Get rows or columns

wk1.get_row(row_number)   # Returns a list of all values in a row 
wk1.get_col(col_number)   # Returns a list of all values in a column

Add/delete rows and columns

wk1.add_rows(n)     # Add  n rows to worksheet at end
wk1.add_cols(n)     # Add n columns to worksheet at end 
wk1.delete_rows(n)  # Delete last n rows of worksheet
wk1.delete_rows(n)  # Delete last n columns of worksheet

Insert rows and columns

wk1.insert_rows(row =1, number = 2) # inserts 2 new rows after 1st row
wk1.insert_rows(row =1, number = 1, values =['AA', 40]) # insert 1 new row and insert values in same row
wk1.insert_cols(col =6, number = 2) # inserts 2 new columns after 6th column
wk1.insert_rows(col=6, number = 1, values =['AA', 40]) # insert a new column and insert values in same column

Update row and column

wk1.update_row(row_index, values, col_offset =0) # Updates values in a row from 1st column
Example: >>> wk1.update_row(9, ['H', 45, 178, 81])
wk1.update_col(col_index, values, row_offset=0)  # Updates values in a column from 1st row
Example: >>> wk1.update_col(9, [78, 45, 178, 81])

Adjust width of column and height of row

wk1.adjust_column_width(start=0, end=3, pixel_size=50) # Updates column size to 50 pixel 
wk1.adjust_row_height(1,10, pixel_size=50) # Updates row height to 50 pixel

Resize and clear worksheet

wk1.clear('A9')     # Clear all values starting from A9
wk1.clear('A9:D10') # Clear values in grid range A9 to D10
wks.resize(num_rows, num_cols)  # Resize to given dimension 

Add pandas dataframe to worksheet

wk1.set_dataframe(df, 'A9')#Inserts df in worksheet starting from A9
# Note: set copy_head =False  if you don't want to add first row of df

Get worksheet values as pandas dataframe

wk1.get_as_df() # Returns a pandas dataframe of worksheet
# Note: You can specify start and end to get specific range data

Example of get_of_df()

Add chart to worksheet

>>>wk1.add_chart(('A1', 'A6'), [('B1', 'B6')], 'Age Chart') 
<Chart COLUMN 'Age Chart'>

Chart added to worksheet.

I guess, you have learned enough to play around google spreadsheets with python. Many more operations can be performed with pygsheets package. Please, see documentation of pygsheets to learn more operations.

Thank you for reading this article. Read my other Medium Articles here.

Reach out to me on LinkedIn, if you have query.

Reference: https://pygsheets.readthedocs.io/en/latest/index.html