How to Automate Google Sheets with Python?

In this article, we will discuss how to Automate Google Sheets with Python.

Pygsheets is a simple python library that can be used to automate Google Sheets through the Google Sheets API. An example use of this library would be to automate the plotting of graphs based on some data in CSV files that we can export to Google Sheets. So, let’s proceed further.

How do pygsheets work with Google Sheets?

• The Client creates and accesses spreadsheets.
• The spreadsheet is the class used to represent a Google Sheet.
• Each spreadsheet has multiple ‘Worksheets’.
• The data inside the worksheets can be accessed through ‘Cell’ objects which supports formatting, formulas, etc.

Step 1: Enable APIs for Google Sheets and Google Drive

A.  Go to Google Developers Console and create a new project. 

B. Enter a name for the project. You may leave ‘Location’ as ‘No Organization’. Press Create.

C. Now, we will enable the Google Sheets and Google Drive APIs. To do so, click on 'ENABLE APIS AND SERVICES'. 

D. Now enter ‘Google Sheets API’ in the search bar, click on the ‘Google Sheets API’ option, and press ‘ENABLE’.

E. Similarly, search for and enable the ‘Google Drive API’.

Step 2: Create a Service Account and fetch credentials

The Service Account is what we will be using to make our API calls to edit the spreadsheets. It is associated with the email id of the Google Developers Account that we used to create our project with.

A. To create one, click on ‘CREATE CREDENTIALS’.

B. Select ‘Google Sheets API’ in the ‘Select an API’ section, ‘Application Data’ and ‘No, I’m not using them’ in the following questions. Click 'NEXT'.

C. Enter the display name and name for the service account.

D. Now we will enter the access level for the service account. Click on ‘Select a Role’ and select ‘Editor’ under the ‘basic’ section. Press ‘CONTINUE’.

E. You may leave the ‘Grant users access to this service account’ section as blank and now, press ‘DONE’.

F. Now click on the service account that was just created.

G. Go to the ‘KEYS’ section and click on 'ADD KEY'. Select 'Create new key'.

H. Select 'JSON' to download the keys in the JSON file format. Click on 'CREATE'.

I. The above file will be downloaded into your system. Do not share the keys with anyone and keep them safe. Place the file in the folder in which you want to create your python project.

Step 3: Add Service Account as an editor

Create a new Google Sheet in your Google account, or you can even use an existing one. Simply add the service account as one of the editors in that sheet. You will find the Service Account email in your keys file as "client_email".

Step 4: Authorize pygsheets 

A. Now create a main.py file and place it in the same folder as your Keys file.

B. Install Required Library.

pip install pygsheets

C. Now, write the following code in the main.py file to verify the successful authorization of pygsheets.

# Importing required library
import pygsheets

# Create the Client
# Enter the name of the downloaded KEYS 
# file in service_account_file
client = pygsheets.authorize(service_account_file="gfg-pygsheets-demo-e3d0c0e482af.json")

# Sample command to verify successful
# authorization of pygsheets
# Prints the names of the spreadsheet
# shared with or owned by the service 
# account
print(client.spreadsheet_titles())

The above code should print the name of the spreadsheet that we shared with the service account in step 3. Hence, we have successfully linked our Google Sheet with the Service Account and now, we will use the Client to edit the spreadsheet.

Step 5: Editing the spreadsheet

A. Now, we will look at some commands to edit our spreadsheet through Python code.

# Importing required library
import pygsheets

# Create the Client
client = pygsheets.authorize(service_account_file="gfg-pygsheets-demo-e3d0c0e482af.json")

# opens a spreadsheet by its name/title
spreadsht = client.open("gfg-demo-sheet")

# opens a worksheet by its name/title
worksht = spreadsht.worksheet("title", "Sheet1")

# Now, let's add data to our worksheet

# Creating the first column
worksht.cell("A1").set_text_format("bold", True).value = "Item"

# if updating multiple values, the data
# should be in a matrix format
worksht.update_values("A2:A6", [["Pencil"], ["Eraser"], 
                                ["Sharpener"], ["Ruler"], 
                                ["Pen"]])  # Adding row values

# Similarly, creating the second column
worksht.cell("B1").set_text_format("bold", True).value = "Price"
worksht.update_values("B2:B6", [[5], [3], [5], [15], [10]])

# Creating a basic bar chart
worksht.add_chart(("A2", "A6"), [("B2", "B6")], "Shop")

Output: