Accessing Google Spreadsheet Data using PythonDilan JayasekaraBlockedUnblockFollowFollowingApr 14If you’re building a simple internal app and you might probably be thinking that ‘I’m going to need a database now right!’.
Well, Not so fast.
As you all are familiar with importing, exporting and manipulating comma separate files (CSV) using Python, Hereby in this article I’m going to show you the step by step guide to access Google Spreadsheets on the cloud using Python.
As the very first thing, go to Google API Manager by simply googling it and go to https://console.
developers.
google.
com/https://console.
developers.
google.
com/To kick things off first create a new project.
Figure 1.
0: Creating a new ProjectI’ll name my project as ‘Telemedicine’ since we will be working with a spreadsheet which includes all the tweets related to Telemedicine hashtags which I extracted earlier (Click here to see how I extracted tweets using hashtags).
Define a suitable project name according to your dataset on the cloud then click CREATE to initiate the project.
(You don’t have to worry about the Location* below the project name)Figure 1.
0: Define a suitable project name according to your datasetOKAY.
The first part is done, Now go to API Library and search for Google Drive.
Figure 2.
0: Search for Google Drive in API LibraryThen add Google Drive API to our project which will allow us to access spreadsheet inside of Google Sheets for our account.
Figure 2.
1: Click on EnableOnce that’s added, we need to create some credentials to access the API so click on Add Credentials on the next screen you see after enabling the API.
Figure 3.
0: Click on Create Credentials to initialize credentials to access the APISince we’ll be accessing the API using a web server, We’ll add the Web Server option on this page and give access to Application Data and tell them that you’re not running your application on either GCE or GAE by selecting the option ‘No, I’m not using them’ then click on the button below.
Figure 3.
1: Creating credentialsNext, we will create a service account named Employees and assigned it the role Project Editor which will allow it to access and edit all the data within the API.
Clicking continue will generate a JSON file that I will rename and add it to the project as Telemedicine_secret.
json.
Figure 3.
2: Creating service account credentialsThen open the JSON file in a text editor (I prefer Atom) :)Inside the file, you can locate an email address property called “client_email”, if we copy that and take it over to our spreadsheet on the cloud, we can share that particular spreadsheet with the email address we provide to give us access to it from the API.
Figure 4.
0: Copy the email address property client_emailFigure 4.
1: Paste that copied email as a shared email addressReading spreadsheet data with PythonLet’s move into the terminal to install gspread and oauth2client packages and wait till all components get installed.
$ pip install gspread oauth2clientThen I’m going to create a new python file called spreadsheet.
py in my favorite editor ATOM and write the following code to import gspread and ServiceAccountCredentials from oauth2client .
import gspreadfrom oauth2client.
service_account import ServiceAccountCredentialsThen, we have to define the scope and create credentials using that scope and the content of employees_secret.
json file.
Then I’ll create a gspread client authorizing it using those credentials.
scope = ['https://spreadsheets.
google.
com/feeds','https://www.
googleapis.
com/auth/drive']creds = ServiceAccountCredentials.
from_json_keyfile_name('employees_secret.
json', scope)client = gspread.
authorize(creds)3.
NOW, We can access our google sheets so we’ll call a client.
open and pass it the spreadsheet name and getting access to sheet1sheet = client.
open('telemedicine_data').
sheet14.
Now we can set our employees equal to all of the records inside that sheet and print them out to the terminal.
telemedicine = sheet.
get_all_records()print(telemedicine)Let’s go the terminal try to run our program and we’ll get a glorious list of perfectly formatted content like in the image below (See the highlighted text and you can find all the columns in our dataset).
python spreadsheet.
pyFigure 5.
0: Display results as a listWhoa!.It wasn’t what you’re expecting, wasn’t it?.Well, trust me I got the perfect solution for that!We can clean up the result by using pprint module, using that we can create a prettyprinter that we can use to display the result and its a much nicer way to display the output.
import gspreadfrom oauth2client.
service_account import ServiceAccountCredentialsimport pprintscope = ['https://spreadsheets.
google.
com/feeds','https://www.
googleapis.
com/auth/drive']creds = ServiceAccountCredentials.
from_json_keyfile_name('employees_secret.
json', scope)client = gspread.
authorize(creds)sheet = client.
open('employee_reviews').
sheet1pp = pprint.
PrettyPrinter()employees = sheet.
get_all_records()pp.
pprint(employees)Figure 5.
1: Formatted results using pprintIf you want to go through the full documentation of gspread click here and in the meantime I’ll follow you up with these cool tricks.
Filtering Data#to get all the values inside the filesheet.
get_all_values()#to get exact row values in a second row (Since 1st row is the header)sheet.
row_values(2)#to get all the column values in the column 'place'sheet.
col_values(16)#to extract a particular cell valuesheet.
cell(1, 1).
valueFigure 6.
0: sheet.
col_values(16) to get all the row values in column 16 which is ‘place’Figure 6.
1: sheet.
row_values(2) to get all the first row valuesOK!.Reading is all done.
YES!.But, Can we do more?.Oh Yes!Insert / Update and Delete from SpreadsheetAnd also, you can enter a random row and can give values to each cell by separating each word using double quotations.
row = ["I'm","inserting","a","new","row","into","a,","Spreadsheet","using","Python"]index = 3sheet.
insert_row(row, index)Figure 6.
2: Spreadsheet after inserting our new rowFurthermore, It’s also possible to alter data inside a specific cell.
Look at how it is done, I’ll change the header column name ‘id’ to ‘telemedicine_id’:sheet.
update_cell(1, 1, "telemedicine_id")Figure 6.
3: After updating the header name which is cell(1,1)Finally, In conclusion to this article, I’m going to wrap up proving that it is not essential to create databases to simple internal apps.
Spreadsheets might be your best answer!You can access the full code here.
Photo by Paul Gilmore on UnsplashThank you!Follow me on Twitter: https://twitter.
com/dylankalpa.