Skip to content

Instantly share code, notes, and snippets.

@msafadieh msafadieh/merge_sheets.py
Last active Jan 9, 2019

Embed
What would you like to do?
a small google sheets script written out of necessity
'''
A small script I wrote to manipulate Google Sheets.
What this script does:
1. Opens a spreadsheet with two worksheets
2. Lays out the data of the second worksheet in a dict (the keys are street addresses
and the items are arrays)
3. Fetches cell range to manipulate
4. Iterates through the rows in the first sheet and looks in the generated dict for a
match (same street address)
5. If found, inserts the array content into the row.
6. Pushes manipulated cell range to server
'''
import gspread
from oauth2client.service_account import ServiceAccountCredentials
def main():
'''
Authenticates with Google servers, fetches and manipulates data, then
pushes manipulated data back.
Things you need:
1. A Google API account with Google Drive and Google Sheets APIs enabled.
2. A JSON creds account by creating credentials and obtaining JSON file.
3. Share the GSheet with the email from the JSON.
'''
print("Authenticating... ", end='')
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
client = gspread.authorize(creds)
print("Done")
# Open spredsheet with name
print("Fetching spreadsheet... ", end='')
spreadsheet = client.open("MySheet")
print("Done")
# Fetches both worksheets
print("Fetching worksheets... ", end='')
first_sheet = spreadsheet.get_worksheet(0)
second_sheet = spreadsheet.get_worksheet(1)
print("Done")
# Fetches data from second sheet and creates dict with
# street addresses as the keys with ZIP and Land Use
all_rows_in_second_sheet = second_sheet.get_all_records()
print("Fetching data... ", end='')
address_dict = dict()
for row in all_rows_in_second_sheet:
address_dict[f'{row["Street Number"]} {row["Street"]}'] = [row['Zip'], row['Land Use ']]
# gets cells to insert data into
zip_list = first_sheet.range('X2:X611')
land_list = first_sheet.range('Y2:Y611')
print("Done")
# iterates through all rows from first sheet and inserts data into correct
# cells if the address is in the second sheet
print("Matching data... ", end='')
for index, house in enumerate(first_sheet.get_all_records()):
address = house['Parcel Address']
arr = address_dict.get(address, -1)
if arr != -1:
zip_list[index].value = arr[0]
land_list[index].value = arr[1]
print("Done")
# update cells
print("Pushing updates... ", end='')
first_sheet.update_cells(zip_list)
first_sheet.update_cells(land_list)
print("Done")
if __name__ == '__main__':
main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.