https://www.twilio.com/blog/2017/02/an- ... gle_sheets
https://github.com/burnash/gspread
Get credentials from:-
https://console.developers.google.com/a ... dance-2017
Reading Google Sheets with python
-
- Site Admin
- Posts: 2449
- Joined: Wed 25 Feb 25 2009 8:00 pm
-
- Site Admin
- Posts: 2449
- Joined: Wed 25 Feb 25 2009 8:00 pm
Re: Reading Google Sheets with python
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
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
-
- Site Admin
- Posts: 2449
- Joined: Wed 25 Feb 25 2009 8:00 pm
Re: Reading Google Sheets with python
sudo -H pip install oauth2client PyOpenSSL gspread --upgrade
-
- Site Admin
- Posts: 2449
- Joined: Wed 25 Feb 25 2009 8:00 pm
Re: Reading Google Sheets with python
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
-
- Site Admin
- Posts: 2449
- Joined: Wed 25 Feb 25 2009 8:00 pm
Re: Reading Google Sheets with python
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()