How to Update Google Spreadsheet using Python

https://www.youtube.com/watch?v=3OnT1PfDrfE&feature=youtu.be

In one of our previous tutorial we learnt how to save data in a google spreadsheet using Google API and Python. In this tutorial we will learn to Update Google Spreadsheet using python. We will be working on the same sheet that we created in our previous tutorial so if you haven’t read it please that before proceeding with this Section.

Once a spreadsheet is created it is important to access the sheet and make changes as required, or take out all data in a pandas data frame and do some analysis. In this tutorial we will learn to update data in an existing sheet or read all the data in a pandas data frame.

We will be using two libraries gspread (https://gspread.readthedocs.io/en/latest/), oauth2client (https://pypi.org/project/oauth2client/) to work with spread sheet. Make sure to read the documents to find out more about these libraries.

See complete code below or watch video for detailed explanation:

#Read json file
import json
with open('mdrive.json') as f:
 data = json.load(f)
#Install Library
pip install gspread oauth2client
import gspread
from oauth2client.service_account import ServiceAccountCredentials


# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive','https://www.googleapis.com/auth/drive.file','https://www.googleapis.com/auth/spreadsheets']
creds = ServiceAccountCredentials.from_json_keyfile_name('mdrive.json', scope)
client = gspread.authorize(creds)

# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open("webscrap").sheet1

# Extract and print all of the values
data = sheet.get_all_records()

Insert, Update, and Delete from a Spreadsheet with Python

We’ve just scratched the surface of gspreads’ well documented and comprehensive functionality.

For instance, we extracted the data into a list of dictionary, but you can get a list of lists if you’d prefer:

sheet.get_all_values()

Or you could just pull the data from a single row, column, or cell

sheet.row_values(3)
sheet.col_values(2)
sheet.cell(2, 2).value

You can write to the spreadsheet by changing a specific cell

sheet.update_cell(3, 1, "I just wrote to a spreadsheet using Python!")
sheet.update('B12','Mexico')

Or you can insert a row in the spreadsheet:

row = ["I'm","inserting","a","row","into","a,","Spreadsheet","with","Python"]
index = 2
sheet.insert_row(row, index)

You can also delete a row from the spreadsheet

sheet.delete_row(20)

Format header:

sheet.format('A1:G1', {'textFormat': {'bold': True}})

Finding cells matching a value:

cell_list = sheet.findall("50%")

The simplest way to get data from a sheet to a pandas DataFrame is with get_all_records():

import pandas as pd

df = pd.DataFrame(sheet.get_all_records())

df

Output:

Read our previous tutorial for learning How to save scraped data in a google spreadsheet using Google API. For any doubt regarding data scraping services feel free to contact us we are ready to serve you.