def create_test_sheet(self, test_col_titles, num_books=10):
"""
Creates a test sheet
"""
gc = pygsheets.authorize(service_account_file=secretsecret.authfile())
num_titles = len(test_col_titles)
test_books = []
for idx in range(1, num_books + 1):
book_info = []
for val in test_col_titles[:len(test_col_titles) - 1]:
book_info.append(val + " " + str(idx))
book_info.append("False")
test_books.append(book_info)
gc.sheet.create('testSheet')
sh = gc.open('testSheet')
wks = sh.sheet1
# Writes Column Names
col_titles = pygsheets.DataRange(start=(1, 1),
end=(1, num_titles),
worksheet=wks)
col_titles.update_values([test_col_titles])
# Writes Book Information
books = pygsheets.DataRange(start=(2, 1),
end=(num_books + 1, num_titles),
worksheet=wks)
books.update_values(test_books)
def create_test_sheet(num_books=10):
'''
Creates a test sheet
'''
AUTHFILE = secretsecret.authfile()
MAINGOOGLEACCOUNT = secretsecret.email()
gc = pygsheets.authorize(service_account_file=AUTHFILE)
test_col_titles = ['Title', 'Author', 'SUID', 'SUNet', 'Cell Phone', 'Address', 'Date Out', 'Date Due', 'Is Checked Out']
num_titles = len(test_col_titles)
test_books = []
for idx in range(1, num_books + 1):
book_info = []
for val in test_col_titles[:len(test_col_titles) - 1]:
book_info.append(val + " " + str(idx))
book_info.append("False")
test_books.append(book_info)
sh = gc.sheet.create('testSheet')
sh = gc.open('testSheet')
wks = sh.sheet1
# Writes Column Names
col_titles = pygsheets.DataRange(start=(1, 1), end=(1, num_titles), worksheet=wks)
col_titles.update_values([test_col_titles])
# Writes Book Information
books = pygsheets.DataRange(start=(2, 1), end=(num_books + 1, num_titles), worksheet=wks)
books.update_values(test_books)
# Shares sheet to main google account
sh.share(MAINGOOGLEACCOUNT, role='writer', type='user')
def download_notes_from_gsheet(
config: GoogleSheetConfig,
vendor: models.Vendor,
url: str,
):
# connect to spreadsheet
gsheet_client = pygsheets.authorize(service_file=config.credentials_file, )
spreadsheet = gsheet_client.open_by_url(url)
worksheet = spreadsheet[0]
# NOTE: can't be more than 31 days
notes_range = pygsheets.DataRange(start="B18",
end="C100",
worksheet=worksheet)
notes_range.fetch(only_data=True)
notes = []
for row in notes_range.cells:
try:
work_date = parse_iso_datestring(row[0].value)
except ValueError:
continue
else:
notes.append(
models.WorkNote(work_date=work_date,
note=row[1].value,
vendor=vendor))
if notes:
LOGGER.info("Inserting %d notes...", len(notes))
models.WorkNote.objects.bulk_create(notes)
def wm_gsheet_formatter(wk):
wm_columns = {
'Engagement': ['U', (0.83529411765, 0.65098039216, 0.741176470590)],
'Total conv': ['V', (0.83529411765, 0.65098039216, 0.74117647059)],
'Repeat': ['W', (0.83529411765, 0.65098039216, 0.74117647059)],
'Immediate': ['X', (0.83529411765, 0.65098039216, 0.74117647059)],
'A%': ['AC', (0.83529411765, 0.65098039216, 0.74117647059)],
'B%': ['AD', (0.83529411765, 0.65098039216, 0.74117647059)],
'C%': ['AE', (0.83529411765, 0.65098039216, 0.74117647059)],
'A+B%': ['AF', (0.83529411765, 0.65098039216, 0.74117647059)],
'Control lift': ['AI', (0.91764705882, 0.81960784314, 0.86274509804)],
'Freeosk lift': ['AJ', (0.91764705882, 0.81960784314, 0.86274509804)],
'Lift delta': ['AK', (0.91764705882, 0.81960784314, 0.86274509804)]
}
n_rows = wk.rows
for col_name, col_attr in wm_columns.items():
model_cell = pygsheets.Cell(f'{col_attr[0]}2')
model_cell.color = (col_attr[1])
# model_cell.color = (0.70588235294, 0.65490196078, 0.83921568627)
model_cell.set_text_format('fontFamily', 'Calibri')
model_cell.set_text_format('bold', True)
model_cell.format = (pygsheets.FormatType.PERCENT, '0.00%')
pygsheets.DataRange(f'{col_attr[0]}2',
f'{col_attr[0]}{n_rows}',
worksheet=wk).apply_format(model_cell)
dollar_col = ['AG', 'AH']
for col in dollar_col:
model_cell = pygsheets.Cell(f'{col}2')
model_cell.color = (0.91764705882, 0.81960784314, 0.86274509804)
model_cell.set_text_format('fontFamily', 'Calibri')
model_cell.set_text_format('bold', True)
model_cell.format = (
pygsheets.FormatType.CURRENCY, '$ 0.00'
) # https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/custom_types.html#FormatType
pygsheets.DataRange(f'{col}2', f'{col}{n_rows}',
worksheet=wk).apply_format(model_cell)
def sc_gsheetformatter(wk):
sc_columns = {
'Engagement': ['T', (0.83529411765, 0.65098039216, 0.741176470590)],
'Conversion': ['U', (0.83529411765, 0.65098039216, 0.74117647059)],
'Repeat': ['V', (0.83529411765, 0.65098039216, 0.74117647059)],
'Immediate': ['W', (0.83529411765, 0.65098039216, 0.74117647059)],
'A%': ['AA', (0.83529411765, 0.65098039216, 0.74117647059)],
'B%': ['AB', (0.83529411765, 0.65098039216, 0.74117647059)],
'C%': ['AC', (0.83529411765, 0.65098039216, 0.74117647059)],
'A+B%': ['AD', (0.83529411765, 0.65098039216, 0.74117647059)],
'Freeosk lift': ['AF', (0.91764705882, 0.81960784314, 0.86274509804)],
'Control lift': ['AG', (0.91764705882, 0.81960784314, 0.86274509804)],
'Lift delta': ['AH', (0.91764705882, 0.81960784314, 0.86274509804)]
}
n_rows = wk.rows
for col_name, col_attr in sc_columns.items():
model_cell = pygsheets.Cell(f'{col_attr[0]}2')
model_cell.color = (col_attr[1])
# model_cell.color = (0.70588235294, 0.65490196078, 0.83921568627)
model_cell.set_text_format('fontFamily', 'Calibri')
model_cell.set_text_format('bold', True)
model_cell.format = (pygsheets.FormatType.PERCENT, '0.00%')
pygsheets.DataRange(f'{col_attr[0]}2',
f'{col_attr[0]}{n_rows}',
worksheet=wk).apply_format(model_cell)
dollar_col = 'AE'
model_cell = pygsheets.Cell(f'{dollar_col}2')
model_cell.color = (0.91764705882, 0.81960784314, 0.86274509804)
model_cell.set_text_format('fontFamily', 'Calibri')
model_cell.set_text_format('bold', True)
model_cell.format = (pygsheets.FormatType.CURRENCY, '$ 0.00')
pygsheets.DataRange(f'{dollar_col}2',
f'{dollar_col}{n_rows}',
worksheet=wk).apply_format(model_cell)
df.date = pd.to_datetime(df.date).dt.date # removing timestamp
# Replacing single anomaly in Gender col (missing gender)
df.loc[(df.id == 2956) & (df.name == 'Scout Schultz'), 'gender'] = 'Male'
# Uploading df to Google Sheets (pygsheets)
client = pyg.authorize(service_account_file='creds.json')
url = 'https://docs.google.com/spreadsheets/d/1xn4Wori5gD8j5U51c1OUeiHaDuRqRIq0Php66hUh2d0/edit#gid=1344755995'
sheet = client.open_by_url(url)
wks = sheet.worksheet_by_title('Sheet1')
wks.set_dataframe(df, start=(1, 1))
# Changing format of Month_Num col to Number ("0") so that it's treated as an integer by Google Data Studio
mo_num_cell = pyg.Cell("S1")
mo_num_cell.set_number_format(format_type=pyg.FormatType.NUMBER, pattern="0")
pyg.DataRange(start='S1', worksheet=wks).apply_format(mo_num_cell)
"""# Uploading df to Google Sheets (df2g)
# Resources: https://techwithtim.net/tutorials/google-sheets-python-api-tutorial/ & https://stackoverflow.com/questions/59117810/changing-column-format-in-google-sheets-by-gspread-and-google-sheets-api
#import gspread as gspread
#from df2gspread import df2gspread as df2g
#from oauth2client.service_account import ServiceAccountCredentials
scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet_key = "1xn4Wori5gD8j5U51c1OUeiHaDuRqRIq0Php66hUh2d0"
sheet_name = "Sheet1"
df2g.upload(df, sheet_key, sheet_name, credentials=creds, row_names=False)
# Editing column data format (from text to number)
spreadsheet = client.open("Police Shootings").sheet1