""" Main Code from here: https://www.dataiku.com/learn/guide/code/python/export-a-dataset-to-google-spreadsheets.html
Read about OAuth credentials: http://gspread.readthedocs.io/en/latest/oauth2.html
Go to Google Sheets and share your spreadsheet with an email you have in your json_key['client_email']. Otherwise you’ll get a SpreadsheetNotFound exception when trying to open it.
Install oauth2client and PyOpenSSL
pip install --upgrade oauth2client (In case of this error: ImportError: cannot import name SignedJwtAssertionCredentials do pip install oauth2client==1.5.2 )
pip install PyOpenSSL
"""
In [30]:
import pandas as pd
import numpy as np
import json
import gspread
from oauth2client.client import SignedJwtAssertionCredentials
#json_key = json.load(open('/Users/.../xxx.json'))
json_key = json.load(open('/Users/charilaostsarouchas/PT_local/GoogleSheets/BI Project-d2d4efc312f2.json'))
url = 'https://docs.google.com/a/project-thor.ch/spreadsheets/d/1uOAyNPuGeXtGwdTZHRCnv1LpBU3Ws1UxYbMGvqwb-Q4/edit?usp=sharing'
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'],
json_key['private_key'].encode(),
scope)
gc = gspread.authorize(credentials)
In [31]:
# create sample dataframe
ts = pd.Series(np.random.randn(1000), index=pd.date_range('1/1/2000', periods=1000))
df = pd.DataFrame(np.random.randn(1000, 4), index=ts.index, columns=['A', 'B', 'C', 'D'])
df.head(4)
Out[31]:
In [32]:
def numberToLetters(q):
q = q - 1
result = ''
while q >= 0:
remain = q % 26
result = chr(remain+65) + result;
q = q//26 - 1
return result
def create_columns(worksheet, df):
# columns names
columns = df.columns.values.tolist()
# selection of the range that will be updated
print 'A1:'+numberToLetters(len(columns))+'1'
cell_list = worksheet.range('A1:'+numberToLetters(len(columns))+'1')
# modifying the values in the range
for cell in cell_list:
val = columns[cell.col-1]
if type(val) is str:
val = val.decode('utf-8')
cell.value = val
# update in batch
worksheet.update_cells(cell_list)
def create_rows(worksheet, df):
# number of lines and columns
num_lines, num_columns = df.shape
worksheet.resize(num_lines + 1, num_columns)
# selection of the range that will be updated
cell_list = worksheet.range('A2:'+numberToLetters(num_columns)+str(num_lines+1))
# modifying the values in the range
for cell in cell_list:
val = df.iloc[cell.row-2,cell.col-1]
if type(val) is str:
val = val.decode('utf-8')
elif isinstance(val, (int, long, float, complex)):
# note that we round all numbers
val = int(round(val))
cell.value = val
# update in batch
worksheet.update_cells(cell_list)
In [33]:
# open the document
#sh = gc.open_by_url('https://docs.google.com/spreadsheets/d/.../edit#gid=0')
sh = gc.open_by_url(url)
In [34]:
worksheet = sh.get_worksheet(0)
In [37]:
num_lines, num_columns = df.shape
worksheet.resize(num_lines + 1, num_columns)
worksheet.range('A1:C1')
Out[37]:
In [21]:
create_columns(worksheet, df)#df[['A', 'B']])
In [86]:
create_rows(worksheet, df)
In [ ]: