DEV Community

loading...

Use google spread sheets as your database

sahilrajput profile image Sahil Rajput Updated on ・3 min read

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. google_api_manager name_project This is our spread sheet which we are going to use as our database. spread_sheet
  2. Add google drive api to the project which will allow us to access spread sheets inside google sheets account. add_api1 add_api2 add_api3
  3. Once thats added than we need to create some credential. create_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. choose_options 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". cmd
  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. share_spreadsheet
  8. Use pip to install gspread and oauth2client packages.
$ pip install gspread oauth2client
Enter fullscreen mode Exit fullscreen mode

install

  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()
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Hope you guys find this article helpful.

Discussion (2)

pic
Editor guide
Collapse
ankitpandey2708 profile image
Ankit Pandey

Couple of additions

  1. When creating scope,
    scope = ['spreadsheets.google.com/feeds','ht...]

  2. Also Enable Gsheet API

  3. sheet.get_all_values() works instead of sheet.get_all_record()

  4. sheet.col_values(5) works instead of sheet.col.values(5)

Collapse
deciduously profile image
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!