Use google spread sheets as your database

Today, we will see how we can use google spread sheets as our database.

Like, if you are creating some internal app and you think you might need a database than you are wrong.

All you need is Google Spread Sheet and Python.

Follow these steps and you will know how.

1. Go to Google API Manager and create a project. This is our spread sheet which we are going to use as our database.
2. Add google drive api to the project which will allow us to access spread sheets inside google sheets account.
3. Once thats added than we need to create some credential. Since we are doing this from a web server than we will add the "web server" option and give it access to application data. After filling the options click "What credential do I need".
4. Next we will create a service account and allow the role "project editor" which we allow to access and edit data within the api.
5. Click continue and it will generate a json file that i will rename and add to the project as "statup_funding.json".
6. Open that file in a text editor than we will find a email address, in a property called "client_email".
7. Copy that and take it over the spread sheet, we can share the spread sheet to that email address to give us the access to the spread sheet from the api.
8. Use pip to install gspread and oauth2client packages.

$ pip install gspread oauth2client

1. Than we will create a file "tutorial.py" and write our code inside that and use google sheets as our database.

Python Program

#import library
import gspread
#Service client credential from oauth2client
from oauth2client.service_account import ServiceAccountCredentials
# Print nicely
import pprint
#Create scope
scope = ['https://spreadsheets.google.com/feeds']
#create some credential using that scope and content of startup_funding.json
creds = ServiceAccountCredentials.from_json_keyfile_name('startup_funding.json',scope)
#create gspread authorize using that credential
client = gspread.authorize(creds)
#Now will can access our google sheets we call client.open on StartupName
sheet = client.open('StartupName').sheet1
pp = pprint.PrettyPrinter()
#Access all of the record inside that
result = sheet.get_all_record()

We can do a lot more like access data in a particular row/column/cell.

result = sheet.row_values(5) #See individual row
# result = sheet.col.values(5) #See individual column
#result = sheet.cell(5,2) # See particular cell
pp = pprint.PrettyPrinter()

Update a particular cell and lot more

#update values
sheet.update_cell(2,9,'500000')  #Change value at cell(2,9) in the sheet
result = sheet.cell(2,9)
pp.pprint(result)

Hope you guys find this article helpful.

Comments

[Ben Lovy]

I think this might have saved me quite a bit of time recently over rolling my own JSON solution. Good to know going forward!