Using Google Sheets as Database in Python
In this article we’ll be discussing how we can use Google Sheets to run like a database for any Python file.
Google Spreadsheets is free online web based application that resembles Microsoft Excel. You can use it to create and edit tables for various projects such as Contact List, Budget and almost everything you can imagine.
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course
Application Program Interface:
Connectivity is a very important thing, we are all used to it to the instant connectivity that puts the world at our fingertips from desktop or mobile. So how do data travel from Point A to Point B, how different devices and applications connect with each other. An API is the messenger that takes requests and tells a system what you want to do and returns response back to you.
Google Spreadsheet API:
We can use this Google Spreadsheet as a data store and you can access this data store via an API so what we can do is we can put in our data and from your application you can access the data as a regular JSON API.
- So our first step is to create a spreadsheet on any of your Google accounts and give it an appropriate name, like we can name Google Sheets API Tutorial as shown below with some random entries:
- For the next step visit Google Cloud Platform now a page opens like shown below :
- Now click on My First Project following dialog box appears, now click on New Project
- Now create your project. Now click on API & Services in the side menu bar then go to Library as shown below and search Google Drive and click on Google Drive API:
- Now we are going to download a JSON file now which will store our credentials, so once downloaded we will go back to Library and search for Google Sheets API, Enable it, once enabled we are all set to hooking up some stuff with our code so that’s it for our Google Cloud Platform, just make sure to keep track of where the JSON file is because we are actually going to have open that up now.
- Now we are going to copy the client email and then go to Google Sheets we made earlier, go to share options paste that email in it and click send. This allows access to the Google sheet from our API. No we will go back to Pycharm now, and create a python file sheets.py.
- Now we will go back to Pycharm now, and create a python file sheets.py. Now we are not writing any codes, yet we are going to install two packages or modules with pip so that we can actually use the API, so to do that in Pycharm we just open the inbuilt terminal, or we can open command prompt and type the below command:
pip install gspread oauth2client
- Once you’ve done, you need to create a python script using some modules. Below is the complete program: