Tired of manual data entry in Google Spreadsheets? Discover a simple and efficient way to automate your data handling using Python and Sheety API. In this blog, we’ll demonstrate step-by-step the process of reading and writing data in Google Sheets, empowering you to effortlessly manage your data with the power of Python. Say goodbye to time-consuming tasks and hello to streamlined data integration – let’s get started!
In this blog, we will cover:
- What Is Sheety API?
- Why Sheety?
- Hands-On
- Conclusion
What Is Sheety API?
Sheety is a web application that allows you to turn Google Sheets into RESTful APIs without the need for writing backend code. It acts as an intermediary between your Google Spreadsheet and the applications you build, enabling you to interact with your Google Sheets data programmatically. With Sheety API, you can perform CRUD (Create, Read, Update, Delete) operations on the data in your Google Sheets using simple HTTP requests. We can get started with Sheety for free, and upgrade when you need it.
Why Sheety?
- Sheety is in charge of server management and uptime.
- It is a plug-and-play service so that you can spend time on what makes your application unique, not worrying about APIs and the backend.
Hands-On
Required installations:
- To perform the same demo, you require the following installations:
- Python: A versatile and user-friendly high-level programming language known for its simplicity and readability.
- Requests: Npm package for making HTTP/HTTPS requests in Node.js, providing an easy-to-use API for handling various HTTP methods and data formats.
- Python-dotenv: Python-dotenv is a Python library that loads environment variables from a .env file, making it easier to manage configurations in development environments.
In this hands-on, we will demonstrate the process of communicating with Google Spreadsheets using Python and the Sheety API. By leveraging the powerful capabilities of the Sheety API, we can perform a wide range of operations on Google Spreadsheets, including reading, writing, updating, and deleting data. This tutorial aims to provide you with a comprehensive understanding of how to interact with Google Spreadsheets programmatically, enabling efficient data manipulation and management.
Throughout the tutorial, we will explore various functionalities step by step. We will start by setting up the necessary prerequisites, such as installing the required libraries and obtaining API credentials. Once the setup is complete, we will establish a connection to the Google Spreadsheet using the Sheety API.
To demonstrate the capabilities, we will learn how to retrieve data from the spreadsheet, allowing us to fetch specific rows or columns. We will also cover the process of writing new data to the spreadsheet, which enables us to add records or update existing entries. Additionally, we will delve into updating and deleting data, enabling us to modify and remove information as needed.
By following this hands-on tutorial, you will gain valuable insights into integrating Python with Google Spreadsheets, empowering you to automate data-related tasks, build data-driven applications, and streamline your workflow. The Sheety API serves as a powerful tool for seamless communication with Google Spreadsheets, providing endless possibilities for data management and manipulation.
Go to this link and create a blank spreadsheet.
You should be able to see a blank spreadsheet like this.
Give your spreadsheet any name you like.
Create headers for the student’s data and make them bold for a better view.
I have inserted some dummy data manually for our understanding.
Log into Sheety with your Google Account (the same account that owns the Google Sheet you created). Click on Connect Google Sheet.
Make sure the email matches between your Google Sheets and Sheety Account. Under your Google Account Security settings, you should see that Sheety has access. Double-check that you see Sheety listed as an authorized app. Otherwise, your Python code can’t access your spreadsheet.
In your project page on Sheety, click on “New Project” and create a new project in Sheety ‘from google sheet’ and paste in the URL of your own “Students” Google Sheet.
Now, enable the GET, POST, PUT, and DELETE endpoints of the API.
Click on Authentication and set a bearer token of your choice and click on Save Changes.
Create a new Python project using any IDE (We’re using PyCharm CE).
Remove any auto-generated code from the main.py file.
Open your IDE’s terminal and run the command as shown in the image below.
pip install requests.
On successful installation, you will see the screen as shown in the image below.
In your main.py file write this line of code to import the requests package.
Create a new file (.env) alongside your main.py file to store the secret key of Sheety API.
Enter the name for the file .env.
Store your Sheety token and API endpoint in the .env file as shown in the image.
For accessing the environment variables from the .env file, we need a package called python-dotenv. Install the package using the following command.
pip install python-dotenv
On successful installation, you will see the screen as shown in the image below.
Import the required packages.
The `load_dotenv()` function initially searches for a .env file. Upon finding one, it proceeds to load the environment variables from the file, thereby enabling your project to access them similarly to any other environment variable.
Store both of the environment variables as shown in the image.
Store the name of your Google sheet in a variable as shown in the image.
Add these two lines of code for creating the session object for making the HTTP requests and creating a header for our HTTP requests.
Note that we’ve added our Sheety bearer token in the Authorization header.
Now we will see how we can perform CRUD operations on our Google sheet using Python code.
- Getting all the rows of the Google sheet
To make a get request we will use the get function of the sessions object and pass the Sheety API endpoint, headers, and a flag to disable the security certificate check.
Print the response returned by the Sheety API to see all the rows of the Google sheet.
As you can see we got the data of all the rows in the Google sheet.
- Insert a record in the Google sheet
We’ve prepared dummy data to insert into the Google sheet.
Note that in the input dictionary, it is necessary to nest our data within the ‘GOOGLE_SHEET_NAME‘ key which is nothing but our Google sheet’s name.
Make a post request passing the URL, input data in JSON, headers, and verify flag to insert the required data in the Google sheet.
After clicking on run and printing the response, we will see the data that is inserted into the Google sheet.
The inserted data should be visible in your Google sheet.
- Update a row in the Google sheet
To update any row in the Google sheet, we have to target that row with its ‘id’ which is nothing but the row number of that row data.
For instance, we will change the name and age of ‘Charles’ which is in the 4th row so its id will be 4.
Make a put request passing the required parameters.
Note that in the first parameter, we have to append the id of the row that we want to change.
After clicking on run and printing the response, we will see the data that we have updated in the google sheet.
The updated data should be visible in your Google sheet.
- Delete a row from the Google sheet
To delete any row from the Google sheet we just need to append the id of the row that needs to be deleted to the URL.
For instance, we will try to delete the 4th row from the Google sheet.
Make a delete request and pass the required parameters including the URL with the row id appended.
Currently, there is data present at the 4th row in the google sheet.
After clicking on run and printing the response, we will see the response status code 204 which means that our request has succeeded.
The required row has been deleted from the Google spreadsheet.
Conclusion
In this hands-on, we have delved into the process of communicating with Google Spreadsheets using Python and the Sheety API. By leveraging the powerful capabilities of the Sheety API, we have successfully performed a wide range of operations on Google Spreadsheets, including reading, writing, updating, and deleting data. Throughout the tutorial, we explored various functionalities step by step, starting from setting up the necessary prerequisites, such as installing the required libraries and obtaining API credentials.
Once the setup was complete, we established a connection to the Google Spreadsheet using the Sheety API. We learned how to retrieve data from the spreadsheet, allowing us to fetch specific rows or columns. We also covered the process of writing new data to the spreadsheet, enabling us to add records or update existing entries. Additionally, we delved into updating and deleting data, enabling us to modify and remove information as needed.
By following this hands-on tutorial, you have gained valuable insights into integrating Python with Google Spreadsheets, empowering you to automate data-related tasks, build data-driven applications, and streamline your workflow. The Sheety API serves as a powerful tool for seamless communication with Google Spreadsheets, providing endless possibilities for data management and manipulation. We will come up with more such use cases in our upcoming blogs.
Meanwhile…
If you are an aspiring Python developer and want to explore more about the above topics, here are a few of our blogs for your reference:
- How To Execute Linux Commands In Python?
- Connect Snowflake With Python And Execute Queries
- Easily Build ETL Pipeline Using Python And Airflow
Stay tuned to get all the updates about our upcoming blogs on the cloud and the latest technologies.
Keep Exploring -> Keep Learning -> Keep Mastering
At Workfall, we strive to provide the best tech and pay opportunities to kickass coders around the world. If you’re looking to work with global clients, build cutting-edge products, and make big bucks doing so, give it a shot at workfall.com/partner today!
