In this article, I will talk about how to automate Google Sheets with Python. This easy automation can be used to read, update or add values to Google Sheets using just a simple Python code.
Why Do You Need Auto Sync Google Sheets?
Google sheet is so much useful if you are dealing with data and want to share online. You can share a Google sheet to other person using the sheet link. Or you can make your sheet public to show anyone. Another way to share your data by embedding your sheet to somewhere.
So you can share your Google sheet easily. Now let’s say you want to show some data which you update frequently. May be that Google sheet data you are generating through a Python code which is running 24/7.
In this case you need a way to update or sync your Google sheet data using a Python code. I faced one situation where I had to automatically sync Google sheet data using a Python script. I thought of sharing my experience how I did it. This article is result of that.
Steps to Update Google Sheets from Python
Let me now break down entire steps to update or sync Google Sheets from Python code automatically (without manual intervention) into some steps:
Step1: Enable APIs for Google Sheets and Google Drive
As you’re dealing with Google Sheets (not just a regular local CSV file), the primary task is to set up the Google Developer Console. This step is crucial for security reasons, ensuring that only authorized individuals can access specific Google Sheets from Python and prevent unauthorized access.
Step 1.1: Create a Project
To do that first go to Google Developers Console from Google Cloud and create a new project there.
Step 1.2: Mention Project Details
Now on the pop up screen enter name of the project. This can be anything. In this example, I am giving my project name as Test Project. You may leave Location
as “No Organization”. Then press Create.
Step 1.3: Select a Project
Once you create a new project in Google Cloud Developer console, you need to select that project. You can do that using right side pop up notification section or from the select project section.
Step 1.4: Enable APIs and Services
So you selected a project. Now you need to enable Google Sheets and Google Drive APIs for that project. This is required to get permission to read and write google sheet from outside (in this example from a Python code). To do that click on ENABLE APIS AND SERVICES.
Step 1.5: Enable Google Sheets and Google Drive APIs
Now type “Google Sheets API” in the library search bar and hit enter.
Then select Google Sheets API option.
Finally click on ‘ENABLE‘ it.
Similarly, search for and enable the “Google Drive API“.
Step2: Create Service Account and get the credentials
We’ll use the Service Account to make API calls and edit the spreadsheets. It’s linked to the email ID (of the Google Developers Account) we used to create our project.
To do this, click on CREATE CREDENTIALS. Then select Service account from the list.
Now type the display name of the service account. You do not need to type in the account ID (it will auto-fill based on your service account name). You may keep description part blank. Then click CREATE AND CONTINUE.
Now you need to enter the access level for the service account. Click on ‘Select a Role‘, choose “Editor” under the “Basic” section, and then press CONTINUE.
You can leave the ‘Grant users access to this service account‘ section blank and then click on ‘DONE‘.
Now click on the newly created service account.
Navigate to the ‘KEYS‘ section and choose ‘ADD KEY‘. Then, select ‘Create new key‘.
Choose ‘JSON‘ to download the keys in the JSON file format. Click on ‘CREATE‘.
Once you click on CREATE, a JSON file will be downloaded to your system. Do not share the keys with anyone and keep it safe for your personal use only. Put the file inside the folder where you plan to create your Python project (your working directory).
Step3: Add Service Account as an Editor
As you know we can share any google sheet to anyone. If we give editor access to that person, he can modify that sheet. Same thing we need to do here. We need to add this service account as one of the editors in a sheet. So that using that service account we can connect that google sheet from Python to update or modify anything.
To do that, first let’s create a new Google Sheet in your Google account, or you can also use an existing one. Then just include this service account as one of the editors in that sheet. For this example, I am creating a new google sheet with name “New Test Sheet“
In the last step we created our service account. You can find it under PERMISSIONS section. Copy the service account from there.
Copy that service account from there and add it as an editor to your newly created Google Sheet (“New Test Sheet”). Click on the Share button (right top) in your Google sheet. Paste the service account. Select editor from the list at right. Finally click the Share button.
Step4: Python code to Update Google Sheet
At this point we are done with Google Cloud configuration part. Now comes the main part which is Python code to update our newly created Google sheet.
Step 4.1: Install pygsheets Library
To Sync Google sheet from Python code, I am going use pygsheets library. pygsheets is most useful Python package that helps you control Google Sheets without having to do everything manually.
You just need to provide the service key information of your Google Sheet to this pygsheets library. Once you do that, using this library, you can easily handle or manipulate your google sheet just like other local CSV or Excel files with Python.
To install this library run below command in your terminal. Before installing any library for a new project I will recommend you to create a new virtual environment.
!pip install pygsheets
Step 4.2: Authorize Service Account
In this first step of coding we need to import pygsheets library then authorize the service account file (which we have downloaded in step 2). I kept the service account JSON file inside “google sheet service key” folder of my working directory. Below is the code for the same.
# Import Library
import pygsheets
# Athorize service account file
client = pygsheets.authorize(service_account_file="google sheet service key/test-project-412605-fb3bdde63f11.json")
Step 4.3: Open Desired Google Sheet
In step 3 we created our new Google sheet (“New Test Sheet“) and shared editor access to the service account. Now using the below Python script we can open that sheet to modify something.
# open a spreadsheet by its name/title
spreadsht = client.open("New Test Sheet")
# open a worksheet by its name/title
worksht = spreadsht.worksheet("title", "Sheet1")
Here in this code, spreadsht
is the entire google sheet and variable worksht
is the specific sheet. For this example I am going to update”Sheet1” of “New Test Sheet” Google sheet.
Step 4.4: Update Google Sheet Values from Python Code
This is the final step. In this step, we will update or modify our newly created google sheet from below Python code.
# Creating the first column of google sheet from python code
worksht.cell("A1").set_text_format("bold", True).value = "Student Name"
# Adding row values to the sheet from python code
worksht.update_values("A2:A6", [["Arjun Sharma"], ["Aisha Patel"],
["Rohan Kapoor"], ["Ananya Singh"],
["Meera Gupta"]])
In the above Python code, at line 2, we are inserting a string value “Student Name“, to A1 cell of our Google Sheet. Making it bold to show it as header. You can try other formatting like changing text color, background color, etc. also. Then from A2 to A6 cells row-wise putting some random student name to that A column.
Similarly you can create second column to that google sheet and update it from same Python code.
# Creating the second column of google sheet from python code
worksht.cell("B1").set_text_format("bold", True).value = "Class"
# Adding row values to the sheet from python code
worksht.update_values("B2:B6", [["First Year"], ["Second Year"],
["Third Year"], ["Fourth Year"],
["Second Year"]])
What Next?
As you can see in the above output Google Sheet screenshot, we have successfully inserted values in a Google sheet from above Python code. Now not only insert, you can delete any value, or modify or update anything from this sheet with similar Python code using pygsheets library.
Now you can try to take to the next level. You can embedd this sheet to your application and you no need to work on the data part of your application. You can run this Google sheet updating Python code 24/7 on an online server to update your sheet continuously.
One thing I found while embedding a sheet is that Google sheet takes 5 minutes to show or sync your updated data. That means let’s say you updated a sheet at 10 am it will show that update at around 10:05 am. But in case of direct link share it will update instantly.
Okay now allow me to wind up this long article 😊. If you have any questions or suggestions regarding this article, please let me know in the comment section below.
Hi there, I’m Anindya Naskar, Data Science Engineer. I created this website to show you what I believe is the best possible way to get your start in the field of Data Science.