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.
- Go to Google API Manager and create a project. This is our spread sheet which we are going to use as our database.
- Add google drive api to the project which will allow us to access spread sheets inside google sheets account.
- 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".
- Next we will create a service account and allow the role "project editor" which we allow to access and edit data within the api.
- Click continue and it will generate a json file that i will rename and add to the project as "statup_funding.json".
- Open that file in a text editor than we will find a email address, in a property called "client_email".
- 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.
- Use pip to install gspread and oauth2client packages.
$ pip install gspread oauth2client
- Than we will create a file "tutorial.py" and write our code inside that and use google sheets as our database.
#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.