def google_drive_sheets_add_tab(spreadsheet_id, name):
"""
Function to add new tab to existing spreadsheet
"""
scope = 'https://www.googleapis.com/auth/drive'
key_file_location = config.get_file_path('google_drive')
authorized_user_file = os.path.expanduser('~/.cache/token_create.json')
credentials = config.set_google_credentials(authorized_user_file,
key_file_location, scope)
service = build('sheets', 'v4', credentials=credentials)
data = [{'addSheet': {'properties': {'title': name}}}]
try:
spreadsheet = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body={
'requests': data
},
fields='replies/addSheet').execute()
except HttpError as err:
print("HTTP error occurred. Error:")
print(err)
return spreadsheet_id
return spreadsheet.get('spreadsheetId')
def google_drive_sheets_read(sheetname_id,
sheet_range,
first_row_columns=False):
"""
Function to read from google sheets
"""
# Authenticate and construct service.
scope = 'https://www.googleapis.com/auth/spreadsheets.readonly'
key_file_location = config.get_file_path('google_drive')
authorized_user_file = os.path.expanduser('~/.cache/token_read.json')
credentials = config.set_google_credentials(authorized_user_file,
key_file_location, scope)
service = build('sheets', 'v4', credentials=credentials)
sheet = service.spreadsheets()
try:
result = sheet.values().get(spreadsheetId=sheetname_id,
range=sheet_range).execute()
except HttpError as err:
print("HTTP error occurred. Error:")
print(err)
return pd.DataFrame([])
values = result.get('values', [])
df = pd.DataFrame(values)
if first_row_columns:
df.columns = df.loc[0, :].values
df.drop(0, inplace=True)
df.reset_index(drop=True, inplace=True)
return df
def __ga_access(input_dict):
"""
Creates a GA client and handles the API errors.
In case of errors, it prints the error message and propagates the exception to the caller.
:param input_dict: request parameters - for validation
:return: service
"""
# Authenticate and construct service.
scope = 'https://www.googleapis.com/auth/analytics.readonly'
key_file_location = config.get_file_path('google_analytics')
authorized_user_file = os.path.expanduser('~/.cache/google_analytics.json')
credentials = config.set_google_credentials(authorized_user_file,
key_file_location,
scope)
service = discovery.build('analytics', 'v3', credentials=credentials)
try:
first_profile_id = get_first_profile_id(service)
if not first_profile_id:
print('Could not find a valid profile for this user.')
return pd.DataFrame([])
else:
yield service
except TypeError as error:
# Handle errors in constructing a query.
print(('There was an error in constructing your query : {}'.format(error)))
raise
except HttpError as error:
# Handle API errors.
print(('Arg, there was an API error : {} : {}'.format(error.resp.status, error._get_reason())))
raise
except RefreshError as error:
# Handle Auth errors.
print('The credentials have been revoked or expired, please re-run '
'the application to re-authorize' + str(error))
raise
except KeyError as error:
# Handle wrong or missing values in query.
if error.args[0] == 'rows':
print('Your query did not return any rows.')
else:
print('There is an error or missing value in your query: {}'.format(error))
raise
except AssertionError as error:
# Handle errors in constructing a query.
if not input_dict['dimensions']:
print('Your query is missing dimensions.')
else:
print(('There was an error in constructing your query : {}'.format(error)))
raise
except Exception as error:
print(('There was an error while handling the request: {}'.format(error)))
def google_drive_sheets_write(data,
spreadsheet_id,
sheet_range='Sheet1!A1',
with_columns=True,
with_index=False):
"""
Function to write to existing google sheet
"""
scope = 'https://www.googleapis.com/auth/drive'
key_file_location = config.get_file_path('google_drive')
authorized_user_file = os.path.expanduser('~/.cache/token_create.json')
credentials = config.set_google_credentials(authorized_user_file,
key_file_location, scope)
service = build('sheets', 'v4', credentials=credentials)
values = data.values.tolist()
columns = list(data.columns)
if with_index:
values = np.append(np.array([list(data.index)]).T, values,
axis=1).tolist()
columns = ['index'] + list(data.columns)
if with_columns:
values = np.append([columns], values, axis=0).tolist()
body = {'values': values}
try:
service.spreadsheets().values().update(
spreadsheetId=spreadsheet_id,
range=sheet_range,
body=body,
valueInputOption='RAW').execute()
except HttpError as err:
if err.resp.status == 400:
print(
"HTTP error occurred. Please verify if sheet_range is correctly set."
)
else:
print(
"HTTP error occurred. Please check spreadsheet id and your connection."
)
return pd.DataFrame([])
print(
'Successfully uploaded to google sheets: https://docs.google.com/spreadsheets/d/'
+ spreadsheet_id)
def google_drive_sheets_create(name):
"""
Function to create a google sheet file
"""
scope = 'https://www.googleapis.com/auth/drive'
key_file_location = config.get_file_path('google_drive')
authorized_user_file = os.path.expanduser('~/.cache/token_create.json')
credentials = config.set_google_credentials(authorized_user_file,
key_file_location, scope)
service = build('sheets', 'v4', credentials=credentials)
spreadsheet = {'properties': {'title': name}}
spreadsheet = service.spreadsheets().create(
body=spreadsheet, fields='spreadsheetId').execute()
return spreadsheet.get('spreadsheetId')
def ga_request(input_dict, print_sample_size=False, sampling_level='HIGHER_PRECISION'):
# Authenticate and construct service.
scope = 'https://www.googleapis.com/auth/analytics.readonly'
key_file_location = config.get_file_path('google_analytics')
authorized_user_file = os.path.expanduser('~/.cache/google_analytics.json')
credentials = config.set_google_credentials(authorized_user_file,
key_file_location,
scope)
service = discovery.build('analytics', 'v3', credentials=credentials)
try:
first_profile_id = get_first_profile_id(service)
if not first_profile_id:
print('Could not find a valid profile for this user.')
return pd.DataFrame([])
else:
input_dict['sampling_level'] = sampling_level
results = get_top_keywords(service, input_dict)
columns = results['query']['dimensions'].split(',') + results['query']['metrics']
df = pd.DataFrame(results['rows'], columns=columns)
for column in df.columns:
try:
df[column] = pd.to_numeric(df[column])
except ValueError:
pass
df.columns = [x[3:] for x in list(df.columns)]
if print_sample_size:
if not isinstance(print_sample_size, bool):
raise TypeError('print_sample_size must be boolean, not {}'.format(type(print_sample_size)))
elif results['containsSampledData']:
sample_size = round(int(results['sampleSize']) / int(results['sampleSpace']) * 100, 2)
else:
sample_size = 100
print('Results calculated based on sample size ', sample_size, '%')
return df
except TypeError as error:
# Handle errors in constructing a query.
print(('There was an error in constructing your query : {}'.format(error)))
return pd.DataFrame([])
except HttpError as error:
# Handle API errors.
print(('Arg, there was an API error : {} : {}'.format(error.resp.status, error._get_reason())))
return pd.DataFrame([])
except RefreshError as error:
# Handle Auth errors.
print('The credentials have been revoked or expired, please re-run '
'the application to re-authorize' + str(error))
return pd.DataFrame([])
except KeyError as error:
# Handle wrong or missing values in query.
if error.args[0] == 'rows':
print('Your query did not return any rows.')
else:
print('There is an error or missing value in your query: {}'.format(error))
return pd.DataFrame([])
except AssertionError as error:
# Handle errors in constructing a query.
if not input_dict['dimensions']:
print('Your query is missing dimensions.')
else:
print(('There was an error in constructing your query : {}'.format(error)))
return pd.DataFrame([])