Profile picture

Co-founder @ RMOTR

Interact With Google Sheets From Python

Quick demonstration of how easy it is to interact with Google Spreadsheets using Python

Last updated: November 28th, 20182018-11-28Project preview

1) Connecting to Google Sheets

In [36]:
import json
import gspread
from oauth2client.service_account import ServiceAccountCredentials

Replace the following dictionary with your own Google credentials.

Follow instructions at Twilio's blog post for further details about how to get your own credentials JSON file.

Once you have the JSON file, replace the values below with your own data. 👇

In [37]:
credentials_json = {
    'type': '...',
    'project_id': '...',
    'private_key_id': '...',
    'private_key': ...',
    'client_email': '...',
    'client_id': '...',
    'auth_uri': '...',
    'token_uri': '...',
    'auth_provider_x509_cert_url': '...',
    'client_x509_cert_url': '...'
}
In [43]:
scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_dict(credentials_json, scope)
gc = gspread.authorize(credentials)
sheet = gc.open("Legislators 2017").get_worksheet(0)
In [45]:
sheet.spreadsheet.title
Out[45]:
'Legislators 2017'

2) Reading rows and columns

In [46]:
sheet.get_all_records()[:2]
Out[46]:
[{'last_name': 'Brown',
  'first_name': 'Sherrod',
  'birthday': '11/9/1952',
  'gender': 'M',
  'type': 'sen',
  'state': 'OH',
  'district': '',
  'party': 'Democrat',
  'url': 'https://www.brown.senate.gov',
  'address': '713 Hart Senate Office Building Washington DC 20510',
  'phone': '202-224-2315',
  'contact_form': 'http://www.brown.senate.gov/contact',
  'rss_url': 'http://www.brown.senate.gov/rss/feeds/?type=all&',
  'twitter': 'SenSherrodBrown',
  'facebook': '',
  'facebook_id': '',
  'youtube': 'SherrodBrownOhio',
  'youtube_id': 'UCgy8jfERh-t_ixkKKoCmglQ',
  'bioguide_id': 'B000944',
  'thomas_id': 136,
  'opensecrets_id': 'N00003535',
  'lis_id': 'S307',
  'cspan_id': 5051,
  'govtrack_id': 400050,
  'votesmart_id': 27018,
  'ballotpedia_id': 'Sherrod Brown',
  'washington_post_id': '',
  'icpsr_id': 29389,
  'wikipedia_id': 'Sherrod Brown'},
 {'last_name': 'Cantwell',
  'first_name': 'Maria',
  'birthday': '10/13/1958',
  'gender': 'F',
  'type': 'sen',
  'state': 'WA',
  'district': '',
  'party': 'Democrat',
  'url': 'https://www.cantwell.senate.gov',
  'address': '511 Hart Senate Office Building Washington DC 20510',
  'phone': '202-224-3441',
  'contact_form': 'http://www.cantwell.senate.gov/public/index.cfm/email-maria',
  'rss_url': 'http://www.cantwell.senate.gov/public/index.cfm/rss/feed',
  'twitter': 'SenatorCantwell',
  'facebook': '',
  'facebook_id': '',
  'youtube': 'SenatorCantwell',
  'youtube_id': 'UCN52UDqKgvHRk39ncySrIMw',
  'bioguide_id': 'C000127',
  'thomas_id': 172,
  'opensecrets_id': 'N00007836',
  'lis_id': 'S275',
  'cspan_id': 26137,
  'govtrack_id': 300018,
  'votesmart_id': 27122,
  'ballotpedia_id': 'Maria Cantwell',
  'washington_post_id': '',
  'icpsr_id': 39310,
  'wikipedia_id': 'Maria Cantwell'}]
In [47]:
sheet.cell(2, 1).value
Out[47]:
'Brown'
In [48]:
sheet.acell('B2').value
Out[48]:
'Sherrod'
In [49]:
sheet.row_values(2)
Out[49]:
['Brown',
 'Sherrod',
 '11/9/1952',
 'M',
 'sen',
 'OH',
 '',
 'Democrat',
 'https://www.brown.senate.gov',
 '713 Hart Senate Office Building Washington DC 20510',
 '202-224-2315',
 'http://www.brown.senate.gov/contact',
 'http://www.brown.senate.gov/rss/feeds/?type=all&',
 'SenSherrodBrown',
 '',
 '',
 'SherrodBrownOhio',
 'UCgy8jfERh-t_ixkKKoCmglQ',
 'B000944',
 '136',
 'N00003535',
 'S307',
 '5051',
 '400050',
 '27018',
 'Sherrod Brown',
 '',
 '29389',
 'Sherrod Brown']
In [50]:
sheet.col_values(2)[:10]
Out[50]:
['first_name',
 'Sherrod',
 'Maria',
 'Benjamin',
 'Thomas',
 'Robert',
 'Bob',
 'Dianne',
 'Orrin',
 'Amy']
In [70]:
sheet.row_count
Out[70]:
544
In [55]:
cell = sheet.find("Sheldon")
cell.row, cell.col
Out[55]:
(17, 2)

3) Doing some plots

In [56]:
import pandas
party = sheet.col_values(8)[1:]  # skip header
df = pandas.DataFrame(party)
In [69]:
df.groupby(0).size().plot.pie(figsize=(8, 8))
Out[69]:
<matplotlib.axes._subplots.AxesSubplot at 0x7fdea86d6320>

4) Editing, inserting, deleting rows

In [85]:
sheet.insert_row(['This', 'is', 'a', 'new', 'row'], index=544)
Out[85]:
{'spreadsheetId': '141uq-co2pv5bzkC1JHIZAECIno1jWipW3gJjRaDA6w0',
 'updatedRange': 'Sheet1!A544:E544',
 'updatedRows': 1,
 'updatedColumns': 5,
 'updatedCells': 5}
In [86]:
sheet.row_values(544)
Out[86]:
['This', 'is', 'a', 'new', 'row']
In [87]:
sheet.update_cell(544, 4, 'simple')
Out[87]:
{'spreadsheetId': '141uq-co2pv5bzkC1JHIZAECIno1jWipW3gJjRaDA6w0',
 'updatedRange': 'Sheet1!D544',
 'updatedRows': 1,
 'updatedColumns': 1,
 'updatedCells': 1}
In [88]:
sheet.row_values(544)
Out[88]:
['This', 'is', 'a', 'simple', 'row']
In [89]:
sheet.update_acell('D544', 'simple')
Out[89]:
{'spreadsheetId': '141uq-co2pv5bzkC1JHIZAECIno1jWipW3gJjRaDA6w0',
 'updatedRange': 'Sheet1!D544',
 'updatedRows': 1,
 'updatedColumns': 1,
 'updatedCells': 1}
In [90]:
sheet.delete_row(544)
Out[90]:
{'spreadsheetId': '141uq-co2pv5bzkC1JHIZAECIno1jWipW3gJjRaDA6w0',
 'replies': [{}]}
In [91]:
sheet.row_values(544)
Out[91]:
[]
Notebooks AI
Notebooks AI Profile20060