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 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 __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([])
import gzip import tempfile from configparser import NoOptionError import pandas as pd from googleads import ad_manager, errors import sroka.config.config as config KEY_FILE = config.get_file_path('google_ad_manager') # GAM API information. try: APPLICATION_NAME = config.get_value('google_ad_manager', 'application_name') except (KeyError, NoOptionError): APPLICATION_NAME = 'Application name' def get_data_from_admanager(query, dimensions, columns, start_date, end_date, custom_field_id=None, network_code=None): if not custom_field_id: custom_field_id = [] if not network_code: try: network_code = config.get_value('google_ad_manager', 'network_code') except (KeyError, NoOptionError): print('No network code was provided') return pd.DataFrame([]) yaml_string = "ad_manager: " + "\n" + \
import pandas as pd from google.cloud import bigquery import sroka.config.config as config from google.api_core.exceptions import Forbidden, NotFound, BadRequest KEY_FILE = config.get_file_path('google_bigquery') def query_bigquery(input_query, filename=None): if filename: if type(filename) != str: print('filename needs to be a string') return None if type(input_query) != str: print('input_query needs to be a string') return None try: f = open(filename, 'w') f.close() except FileNotFoundError: print('file cannot be saved in selected directory') return None else: if type(input_query) != str: print('input_query needs to be a string') return pd.DataFrame([])