def sheets_write(auth,
sheet_url_or_name,
sheet_tab,
sheet_range,
data,
append=False,
valueInputOption='RAW'):
if project.verbose:
print('SHEETS WRITE', sheet_url_or_name, sheet_tab, sheet_range)
sheet_id = sheets_id(auth, sheet_url_or_name)
range = sheets_tab_range(sheet_tab, sheet_range)
body = {'values': list(data)}
if append:
API_Sheets(auth).spreadsheets().values().append(
spreadsheetId=sheet_id,
range=range,
body=body,
valueInputOption=valueInputOption,
insertDataOption='OVERWRITE').execute()
else:
API_Sheets(auth).spreadsheets().values().update(
spreadsheetId=sheet_id,
range=range,
body=body,
valueInputOption=valueInputOption).execute()
def sheets_tab_copy(config,
auth,
from_sheet_url_or_name,
from_sheet_tab,
to_sheet_url_or_name,
to_sheet_tab,
overwrite=False):
if config.verbose:
print('SHEETS COPY', from_sheet_url_or_name, from_sheet_tab,
to_sheet_url_or_name, to_sheet_tab)
# convert human readable to ids
from_sheet_id, from_tab_id = sheets_tab_id(config, auth,
from_sheet_url_or_name,
from_sheet_tab)
to_sheet_id, to_tab_id = sheets_tab_id(config, auth, to_sheet_url_or_name,
to_sheet_tab)
# overwrite only if does not exist
if overwrite or to_tab_id is None:
# copy tab between sheets, the name changes to be "Copy of [from_sheet_tab]"
copy_sheet = API_Sheets(config, auth).spreadsheets().sheets().copyTo(
spreadsheetId=from_sheet_id,
sheetId=from_tab_id,
body={
'destinationSpreadsheetId': to_sheet_id,
}).execute()
body = {'requests': []}
# if destination tab exists, delete it
if to_tab_id:
body['requests'].append({'deleteSheet': {'sheetId': to_tab_id}})
# change the copy name to the designated name, remove "Copy of "
body['requests'].append({
'updateSheetProperties': {
'properties': {
'sheetId': copy_sheet['sheetId'],
'title': to_sheet_tab
},
'fields': 'title'
}
})
API_Sheets(config,
auth).spreadsheets().batchUpdate(spreadsheetId=to_sheet_id,
body=body).execute()
def sheets_get(auth, sheet_url_or_name):
sheet_id = sheets_id(auth, sheet_url_or_name)
if sheet_id:
return API_Sheets(auth).spreadsheets().get(
spreadsheetId=sheet_id).execute()
else:
return None
def sheets_clear(auth, sheet_url_or_name, sheet_tab, sheet_range):
if project.verbose:
print('SHEETS CLEAR', sheet_url_or_name, sheet_tab, sheet_range)
sheet_id = sheets_id(auth, sheet_url_or_name)
API_Sheets(auth).spreadsheets().values().clear(spreadsheetId=sheet_id,
range=sheets_tab_range(
sheet_tab, sheet_range),
body={}).execute()
def sheets_clear(config, auth, sheet_url_or_name, sheet_tab, sheet_range):
if config.verbose:
print('SHEETS CLEAR', sheet_url_or_name, sheet_tab, sheet_range)
sheet_id = sheets_id(config, auth, sheet_url_or_name)
if sheet_id:
API_Sheets(config, auth).spreadsheets().values().clear(
spreadsheetId=sheet_id,
range=sheets_tab_range(sheet_tab, sheet_range),
body={}).execute()
else:
raise ValueError('Sheet does not exist for %s: %s' %
(config, auth, sheet_url_or_name))
def sheets_read(auth,
sheet_url_or_name,
sheet_tab,
sheet_range='',
retries=10):
if project.verbose:
print('SHEETS READ', sheet_url_or_name, sheet_tab, sheet_range)
sheet_id = sheets_id(auth, sheet_url_or_name)
if sheet_id is None:
raise (OSError('Sheet does not exist: %s' % sheet_url_or_name))
else:
return API_Sheets(auth).spreadsheets().values().get(
spreadsheetId=sheet_id,
range=sheets_tab_range(sheet_tab,
sheet_range)).execute().get('values')
def sheets_read(config,
auth,
sheet_url_or_name,
sheet_tab,
sheet_range='',
retries=10):
if config.verbose:
print('SHEETS READ', sheet_url_or_name, sheet_tab, sheet_range)
sheet_id = sheets_id(config, auth, sheet_url_or_name)
if sheet_id:
return API_Sheets(config, auth).spreadsheets().values().get(
spreadsheetId=sheet_id,
range=sheets_tab_range(sheet_tab,
sheet_range)).execute().get('values')
else:
raise ValueError('Sheet does not exist for %s: %s' %
(config, auth, sheet_url_or_name))
def sheets_create(auth,
sheet_name,
sheet_tab,
template_sheet=None,
template_tab=None):
""" Checks if sheet with name already exists ( outside of trash ) and
if not, creates the sheet. Both sheet and tab must be provided or both must be
omitted to create
a blank sheet and tab.
Args:
* auth: (string) Either user or service.
* sheet_name: (string) name of sheet to create, used as key to check if it
exists in the future.
* sheet_tab: (string) name of the tab to create.
* template_sheet: (string) optional sheet to copy tempalte from.
* template_tab: (string) optional tab to copy template from.
* parent: (string) the Google Drive to upload the file to.
Returns:
* JSON specification of the file created or existing.
"""
created = False
# check if sheet and tab exist
sheet_id, tab_id = sheets_tab_id(auth, sheet_name, sheet_tab)
# if no sheet create it and the tab
if sheet_id is None:
if project.verbose:
print('SHEET CREATE', sheet_name, sheet_tab)
body = {
'properties': {
'title': sheet_name,
},
'sheets': [{
'properties': {
'title': sheet_tab,
}
}]
}
spreadsheet = API_Sheets(auth).spreadsheets().create(
body=body).execute()
sheet_id = spreadsheet['spreadsheetId']
tab_id = spreadsheet['sheets'][0]['properties']['title']
created = True
# if creating tab from template
if (created or tab_id is None) and template_sheet and template_tab:
if project.verbose:
print('SHEET TAB COPY', sheet_tab)
sheets_tab_copy(auth, template_sheet, template_tab, sheet_id,
sheet_tab, True)
# if creating a blank tab
elif tab_id is None:
if project.verbose:
print('SHEET TAB CREATE', sheet_name, sheet_tab)
sheets_tab_create(auth, sheet_name, sheet_tab)
# if sheet and tab already exist
else:
if project.verbose:
print('SHEET EXISTS', sheet_name, sheet_tab)
return sheet_id, tab_id, created
def sheets_values_batch_update(auth, sheet_url_or_name, data):
sheet_id = sheets_id(auth, sheet_url_or_name)
API_Sheets(auth).spreadsheets().values().batchUpdate(
spreadsheetId=sheet_id, body=data).execute()