Create, Read and Write to Google Sheet with Python

 

 

In this article we will give some examples of how to create a workbook on Google sheets using the credentials we showed how to create here. The first step is to create a workbook, we have created a workbook called 'Global Temperatures'. In order to interact with this workbook through Python we must let Google Sheets know the client email from the credentials we created previously 

 

{
  "type": "service_account",
  "project_id": "",
  "private_key_id": "",
  "private_key": "",
  "client_email": "",
  "client_id": "",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": ""
}

 

 

Copy the "client_email" field from the json file you have created then navigate back to the workbook , notice in the top right of your screen there is a share button beside your gmail icon. Click on this Share button and paste the client_email in to the 'Add people and groups' dialogue box. 

 

 

To follow along with this guide you can copy the data in the table below in your workbook. 

 

 

	A	B
1	Location	Temperature
2	London	15
3	New York	18
4	Sydney	22
5	Madrid	21

 

 

#import packages
import gspread 
from oauth2client.service_account import ServiceAccountCredentials

#service account through credentials saved in working directory
gp = gspread.service_account(filename='credentials.json')

# pass in name of workbook we created
workbook = gp.open('Global Temperatures')

#get the sheet
sheet = workbook.worksheet('Sheet1')

 

First let's verify that we do indeed have the correct sheet by pulling all the data using the get_all_records method

 

print(sheet.get_all_records())
'''
[{'Location': 'London', 'Temperature': 15}, 
 {'Location': 'New York', 'Temperature': 18},
 {'Location': 'Sydney', 'Temperature': 22},
 {'Location': 'Madrid ', 'Temperature': 21}]

'''

 

You will notice that this method returns a list of dictionaries. with the records we have entered in the workbook. 

 

Getting a Cell Value

 

We can extract a cell value by noting that the column A and B from the table above, lets say we wanted to select the value in A2 which we expect to be a string 'London' 

 

a2 = sheet.acell('A2').value

print(a2)

'''
London
'''

 

 

We can also retrieve the same value by row and column indexing. Below we give an example for retrieving the location in the third row and second column (3,1) and the respective temperature at row 3 column 2 from the table above. 

 

temp3 =  sheet.cell(3, 2).value
loc3 = sheet.cell(3, 1).value
print(f'Location: {loc3} is in row 3, colum 1, temperature for {loc3} is {temp3} at row 3, col 2')


'''
Location: New York is in row 3, colum 1, temperature for New York is 18 at row 3, col 2
'''

 

Getting All Values From Row or Column

 

Retrieve all the data from the 4th row

 

row1 = sheet.row_values(4)
print(row1)
'''
Out[24]: 
['Sydney', '22']
'''

 

Get all values from the first column representing the locations

 

col1 = sheet.col_values(1)
print(col1)
'''
['Location', 'London', 'New York', 'Sydney', 'Madrid ']
'''

 

 

 

Assigning New Values to Cells

 

Update the column value for the temperature relating to London. We will use A1 notation for this example. 

 

sheet.update('B2', 20)

 

We can then see the updated sheet looks as follows

 

	A	B
1	Location	Temperature
2	London	20
3	New York	18
4	Sydney	22
5	Madrid	21

 

 

 

Add New Row to Sheet

 

Lets say we want to update the worksheet with a new row representing Paris and a temperature of 19. 

 

sheet.append_row(['Paris', 19])

 

Very easy to add a new row! You will now see a new value in the workbook. 

 

	A	B
1	Location	Temperature
2	London	20
3	New York	18
4	Sydney	22
5	Madrid	21
6	Paris	19

 

 

How about if we want to add multiple rows? Well, that is very easy also, we simply need to pass in the values for each new row as a list, so the we will send a list of lists as shown below

 

many_rows = [
    ['Rome', 24],
    ['Moscow', 13]
    
    ]

sheet.append_rows(many_rows)

 

 

	A	B
1	Location	Temperature
2	London	20
3	New York	18
4	Sydney	22
5	Madrid	21
6	Paris	19
7	Rome	24
8	Moscow	13

 

 

Find a Cell Given a String Value

 

Lets say we want to find New York from our worksheet. Note that this method returns a cell object if the value is found , otherwise None. 

 

cell = sheet.find('New York')
print(f'New York was found at row:{cell.row}, col:{cell.col}')
'''
New York was found at row:3, col:1
'''