Update your google spreadsheets with python.

Today I just finish my little project at company. it all about update google spreadsheet and my Geckoboard pull data from that spreadsheet. and one reason I prefer spreadsheet beyond dataset because I can view data in spreadsheet. but if you want to use dataset. here is my article about dataset. Manage Geckoboard Dataset with python

The origin way is use Sheet API that provide by google. but it have another easy way to get the job done. that is python lib call gspread. you can read document on that site. it very clear and easy to follow.

Installation

Before we start. we need to install dependency of this lib. you need to install oauth2client first.

pip install --upgrade oauth2client

install lib

pip install gspread

then install PyOpenSSL

pip install PyOpenSSL

after that you need to provide API Key for google spreadsheet.

API key for google spreadsheet

go to Google Developer Console and create new project

After create new project. you need to create service of this project. select your project that you created > click on menu service account > create service account > add your service name > select role project and select owner

after create service. you click that service and choose create key. system will ask you what type of file you want to store with API key. in this case I love dealing with json. then select json and click create.

system will make json file and download into your computer. you need to get that json file into your project directory. and now we are good to go.

Start Coding!!!

let's create our auth method first

def authen_spreadsheet(self):
    scope = ['https://spreadsheets.google.com/feeds']
    # get api key from json file and authen by ServiceAccountCredentials.
    credentials = ServiceAccountCredentials.from_json_keyfile_name('Python-Odoo-Gspreadsheet-c87eb3b75d55.json', scope)
    gc = gspread.authorize(credentials)
    # choose Working File of google spreadsheet
    geckosheet = gc.open("Geckoboard")
    # choose working spreadsheet
    worksheet = geckosheet.worksheet('Febuary 2018')
    return worksheet

if you want to find record is exist. use this

is_exist = worksheet.findall('data')

you can update cell by update_acell

worksheet.update_acell("G1"),"data updated") 

you can read all API reference here.

Conclusion.

you can use this lib with many project that involve with google spreadsheet. it very fast, simple to implement. and help you a lot when you need to keep tracking, automate things.