Reading Google Sheets with python
Posted: Wed 11 Apr 11 2018 10:40 pm
A place to blog my projects and research
http://forum.tsebi.com/
Code: Select all
#!/usr/bin/env python
import gspread
from oauth2client.service_account import ServiceAccountCredentials
#This extended scope is needed for this to work
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('COOS-attendance-2017-old-secret.json', scope)
client = gspread.authorize(credentials)
wks = client.open("Copy of 2017 Attendance").sheet1
list_of_hashes = wks.get_all_records()
print(list_of_hashes)
print wks.cell(1,1).value
Code: Select all
import pandas as pd
import numpy as np
import gspread
from oauth2client.service_account import ServiceAccountCredentials
def normalize(ustr):
retval = 0
if ustr != "" and str(ustr).isdigit():
retval = int(str(ustr).replace("-","0"))
return retval
def parse_sheet(wks):
columns=['date','e1','e2','e3','ey1','ec1','c1','c2','cy1','cc1','f1','remarks']
lst = []
e1 = ey1 = e2 = e3 = c1 = c2 = cy1 = f1 = ec1 = cc1 = 0
rem = ""
records = wks.get_all_records()
for record in records:
if record['ADay'] != "": # skip blank lines
e1 += normalize(record['A330pm'])
ey1 += normalize(record['A6pm'])
e2 += normalize(record['A830am'])
e3 += normalize(record['A1030am'])
c1 += normalize(record['ADialect'])
c2 += normalize(record['AChinese'])
cy1 += normalize(record['AYouth'])
f1 += normalize(record['FF'])
ec1 += normalize(record['AEng_CM'])
cc1 += normalize(record['AChin_CM'])
if record['ADay'].upper() == "SUN":
odate = record['ADate'].replace("-","")
date = pd.Timestamp(odate[4:8] + odate[2:4] + odate[0:2])
if e1 == 0:
e1 = np.NaN
if e2 == 0:
e2 = np.NaN
if e3 == 0:
e3 = np.NaN
if ey1 == 0:
ey1 = np.NaN
if ec1 == 0:
ec1 = np.NaN
if c1 == 0:
c1 = np.NaN
if c2 == 0:
c2 = np.NaN
if cy1 == 0:
cy1 = np.NaN
if cc1 == 0:
cc1 = np.NaN
if f1 == 0:
f1 = np.NaN
lst.append([date, e1, e2, e3, ey1, ec1, c1, c2, cy1, cc1, f1, rem])
e1 = ey1 = e2 = e3 = c1 = c2 = cy1 = f1 = ec1 = cc1 = 0
#print(record['ADay'])
df = pd.DataFrame(lst, columns=columns)
df.set_index('date', inplace=True)
return df
def main():
#This extended scope is needed for this to work
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('COOS-attendance-2017-old-secret.json', scope)
client = gspread.authorize(credentials)
wks = client.open("Combined Attendance").sheet1
df = parse_sheet(wks)
df.to_csv('COOS-attendance.csv')
print df
"""
for row in range(1,122):
day = str(wks.cell(row,2).value)
if day != '': # skip blank lines
if day.upper() == 'SUN':
date = wks.cell(row,1).value
print wks.cell(row,1).value
print date
"""
if __name__ == "__main__":
main()