Reading Google Sheets with python

Reviews and other little ventures that don't quite fit into the other forums
Post Reply

Daniel Wee
Site Admin
Posts: 2129
Joined: Wed 25 Feb 25 2009 8:00 pm

Re: Reading Google Sheets with python

Post by Daniel Wee » Wed 11 Apr 11 2018 11:07 pm

1. Go to the Google APIs Console
2. Create a new project
3. Click Enable API. Search for and enable the Google Drive API
4. Create credentials for a Web Server to access Application Data
5. Name the service account and grant it a Project Role of Editor
6. Download the JSON file
7. Copy the JSON file to your code directory and rename it to client_secret.json
8. Find the client_email inside client_secret.json. Back in your spreadsheet, click the Share button in the top right, and paste the client email into the People field to give it edit rights. Hit Send.

To re-get the JSON file a second time:-

1. Go to the Google APIs Console
2. Go to Credentials
3. Click "Create Credentials" and choose "Service Account key"
4. Select "JSON" and click "Create"
5. File will be downloaded - rename accordingly.

Enable Google Sheets API

https://console.developers.google.com/a ... dance-2017
Enable

Daniel Wee
Site Admin
Posts: 2129
Joined: Wed 25 Feb 25 2009 8:00 pm

Re: Reading Google Sheets with python

Post by Daniel Wee » Wed 11 Apr 11 2018 11:13 pm

sudo -H pip install oauth2client PyOpenSSL gspread --upgrade

Daniel Wee
Site Admin
Posts: 2129
Joined: Wed 25 Feb 25 2009 8:00 pm

Re: Reading Google Sheets with python

Post by Daniel Wee » Wed 11 Apr 11 2018 11:31 pm

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

Daniel Wee
Site Admin
Posts: 2129
Joined: Wed 25 Feb 25 2009 8:00 pm

Re: Reading Google Sheets with python

Post by Daniel Wee » Thu 12 Apr 12 2018 12:40 pm

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()

Post Reply