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. 😉
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.
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
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 A7wk1.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 rowwk1.insert_rows(row =1, number = 1, values =['AA', 40]) # insert 1 new row and insert values in same rowwk1.insert_cols(col =6, number = 2) # inserts 2 new columns after 6th columnwk1.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 columnExample: >>> wk1.update_row(9, ['H', 45, 178, 81])wk1.update_col(col_index, values, row_offset=0) # Updates values in a column from 1st rowExample: >>> 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 D10wks.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
Add chart to worksheet
>>>wk1.add_chart(('A1', 'A6'), [('B1', 'B6')], 'Age Chart')
<Chart COLUMN 'Age Chart'>
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