This post will be flowing you through how to use python with Google Spreadsheet. Reading, writing, deleting, updating and even finding a text in a Google Spreadsheet using the python programming language.
At the end of the post, We will build a sample application of this.
I may not have mention these before, but i’m a Fiverr seller. So even the sample application is from a past project done on the platform.
If you have any question, shoot it in the comment section .
Without further ado, Let’s get to it.
Why Use Google Spreadsheet ?
Starting with the question – Why would i use google spreadsheet to store information, why can’t i just store it in a local spreadsheet.
The thing is any one who has used google spreadsheet before would probably never ask the above question.
There’s a lot of additional benefit in using Google spreadsheet.
Now doing it programatically, That’s usage on steroid.
The advantage is almost infinite.
Nevertheless, I will still give the newbies reasons why you should write your data to the Google cloud spreadsheet rather than your old junkie machine.
- Your data always stays up to date
- This will help keep your data secure as it’s all on the cloud
- Google’s artificial intelligence is available to speed up analysis.
- You can automate processes
- You can access your data from anywhere as it’s stored in the cloud
There are 2 Python modules required for this to work
- oauth2client – To authenticate a Google user with a Google account.
- gspread – To communicate, read and write to the google spreadsheet.
The Oauth2Client will be use to authorize Google and gspread will use this authorization instance to carry out the spreadsheet operation.
Both these module can be install with :
pip install oauth2client gspread
Setting up Authorization
Firstly, we need to setup authorization to the Google Spread sheet but how do we do this ?
we do that by authorizing our application, You just follow me as it’s pretty straight forward.
Create a New Project
Create a new project in the Google API Console by clicking Create Project and select “NEW PROJECT” from the modal
Enter project name and click CREATE – in my case, project name is GeekySpread
Activate the Required API
There are two API required to make all this work. The Google drive API and the Google spreadsheet API. They can be searched from the search box at the header box.
We need to select and Activate the Google Drive API
And also Activate the Google Spreadsheet API as well as this is also needed.
If these API are not activated, no work can be done.
So, Immediately after the activation,
Create a Google Service Account
Create a New credential – Service account to be precise.
You should select “Credential” from the side bar the hit the button on the top bar, then select Service Account.
In the next page, select Google Drive API in the “which API are you using ?“
You should choose Web Server in the Where will you be calling from Field.
Select Application Data
And, choose No, I’m not using them.
See the screenshot below on how it’s done.
On the next page, enter your service account name as whatever you like .
Select JSON for key type and click the Continue button
And we successfully created our service account
A file will be automatically downloaded to your computer. Mine was something like GeekySpread-9b2kdjalblah.json
Keep the file safe, it will be used later for authorization.
Connecting to Google Spreadsheet
Now we have our file for authorization. I will rename our file to spready_tut.json to make things easy.
The Oauth2Client module will be use to authenticate
import gspread # import the gspread module to work with spreadsheet from oauth2client.service_account import ServiceAccountCredentials # module for google authorization scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] google_cred_spread_file = 'spready_tut.json' credential = ServiceAccountCredentials.from_json_keyfile_name(google_cred_spread_file, scope) google_sheet = gspread.authorize(credential) # the google_sheet variable is what is needed from here to talk with the spreadsheet
One final thing before we start moving data in and out of our spreadsheet to make this whole thing work.
I will assume your spreadsheet has been created.
So, you need to share the spreadsheet with our service account email. The service account email/ID can be found in our authority json file spready_tut.json in my case under the client_email key
So, that’s it we can now connect to the Google Spreadsheet provided everything is done as it should.
Now we move on to reading from a spreadsheet.
Reading from Google Spreadsheet
Firstly, we start by acquiring handle to the workbook and the first sheet
sheet_name = 'geekytut' # this is the title of the SS we working with workbook = gc.open(sheet_name) sheet = workbook.sheet1 # we only working with the first sheet for now sheet.get_all_values() # these returns all values in the spreadsheet as a lsit of list name = sheet.acell('B2').value # this gets the value in the B2 cell age = sheet.acell('E1').value # and this gets the value in the E1 cell
We can as well open our sheet by it’s key which can be extracted from the spreadsheet URL
sheet = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')
As well as the sheet URL which you can just copy from the browser address bar.
sheet = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')
You should study the code snippet above, They are pretty straight forward.
And we have more
sheet.row_values(1) # this returns list of values in the first row only sheet.col_values(1) # and this returns list of values in the first column only sheet.cell(1, 1).value # returns value of cell at index row 1 and col 1
As you might have guess, there’s a method to fetch a range of cells.
cell_list = wks.range('A1:B7')
The above code snippet fetches from cell A1 to cell B7
You know what ? We can even find a value in the spreadsheet.
Finding a particular text in the spreadsheet
cell = worksheet.find("Foo and bar") print("Found something at R%sC%s" % (cell.row, cell.col))
The above snippet only searches for a single occurrence of the text. All occurrence can be found with:
list_of_cells = worksheet.findall("Foo and bar")
Moreover, There’s more to be explored in the official documentation.
Now, Let’s move on to writing into the Google spreadsheet using python.
Writing into Google Spreadsheet
Now, let’s write some data into the cloud. Like say for example a stock market scrape result.
row_data = ['This is for column 1', 'column 2', 'column 3'] row_index = 1 # we are using the second row in the spreadsheet, first is 0 sheet.insert_row(row_data, row_index)
Additionally, we can update a specific cell instead of inserting.
sheet.update_cell(1, 1, "The description has been altered")
Or maybe perhaps we decided to delete the whole of the second row
Let’s move on , guys.
A Sample Usage
In this section, we will create a sample application to use with this.
Thinking out loud, what do we create to communicate with Google spreadsheet using Python?
Then, I decided to create a project on Github and link to it from here instead of treating it in this section.
Storing data in the cloud on Google Spreadsheet especially during Scraping operation seems like
Additionally, It can also serve as a database in the cloud. Just think outside the box.
Furthering more, You can read more on gspread from their documentation page.
Lastly, If you think i’m missing on somethings or you have contribution to this, Please drop a message in the comment section below and let me know what you think .