Supercharge Your Data Analytics with gspread and Pandas: A Hands-On Tutorial (Part 1)
Imagine you need to manipulate and analyze some data from google Sheets. You may have heard about pandas, a powerful Python library for data manipulation and analysis, but you have no idea how to get the data from Google Sheets into pandas. You keep copying and pasting the data from the Google Sheets spreadsheet into a new file and then importing it into pandas. Maybe you are looking to use the Google Sheets API, a powerful RESTful API provided by Google for interacting with Google Sheets which allows you to read, write and update data in Google Sheets programmatically using various programming languages. What about Pygsheets? Openpyxl? Xlsxwriter? Oh merrrnnnn!!!
While these are good alternatives, they may be difficult to use due to their complexity, steep learning curve, and lack of documentation. Additionally, some of these alternatives may require the use of APIs and authorization keys, which can add to the complexity of the implementation.
So Cliff, what do you want to introduce me to?
Meet gspread, a Python library that provides an easy way to interact with Google Sheets using the Google Sheets API. It allows users to read, write, and modify data in Google Sheets programmatically, which is useful for tasks such as data analysis, reporting, and automation. With gspread, you could programmatically extract data from your Google Sheets spreadsheet and convert it into a pandas dataframe with just a few lines of code and do all your fancy pandas tricks.
By combining gspread with Pandas, you can easily read data from Google Sheets and perform advanced data manipulation and analysis using Pandas. Additionally, gspread provides a simple and intuitive interface for working with Google Sheets, making it easy to read, write, and update data.
No more wahala!(wahala means stress)
This article will be in two parts. Part 1 will be about how to install gspread and complete the authentication process. Part 2 will be about how to read the data with python and perform some analysis.
Installing gspread
Open your terminal and run:
pip install gspreadOnce you are done installing gspread, it is time to do some authentication magic. Authentication is required to ensure that the user has proper access and permissions to the Google Sheets spreadsheets they are trying to access. Without authentication, we cannot verify that the app is authorized to access the spreadsheets, and as a result, the app will not be able to read, write or modify the data within them.
Authentication
1. Head over to console.cloud.google.com and sign in with your Google account.
2. Select an existing project or create a new one
3. In the search bar, type “Google Drive API”
4. Select the Google Drive API from the dropdown
5. Click on ENABLE. This step is important as it allows the project to access the necessary APIs for authentication and authorization.
6. In the search bar, type “Google Sheets API”
7. Select the Google Sheets API from the dropdown
8. Click on ENABLE. This step is important as it allows the project to access the necessary APIs for authentication and authorization.
9. Click on APIs & Services
10. Click on Credentials
11. Click on CREATE CREDENTIALS
12. Click on Service account. This step is important as it creates a service account that will be used to access the APIs and authenticate the Google Sheets.
13. Give your account a unique name.
14. Click on CREATE AND CONTINUE
15. Click on DONE
16. Click on Manage service accounts
17. Click “:” under Action
18. Click on Manage keys
19. Click on ADD KEY
20. Click on Create new key
21. Select JSON and Click on CREATE
22. Click on CLOSE
You will automatically download a JSON file with credentials. These credentials will allow gspread to access your Google Sheets account and retrieve or modify the spreadsheets as per the permissions granted to that account. This helps to protect the security of the data by preventing unauthorized access.
The JSON file should look something like this
We’re not done yet but I hope you enjoyed this read. Thanks for your time.
Click here to go to Part 2
