I am trying to download the excel file on the fly by saving multiple data frames on multiple sheets one by one, the thing is that the return statement running only one time since it’s inside the for a loop. but when I make it outside loop it starts giving me an error. Can anyone please help?
@app.callback(Output('output_div-get-ga-email', 'data'),
[Input('submit-button-get-email', 'n_clicks')],
[State('start-date', 'date'),
State('end-date', 'date'),
State('report_name', 'value'),
State('get-report', 'value')],
)
def ga_output(clicks, start_date, end_date, report_name, email):
if clicks is not None:
file_name = 'ga_{}.xlsx'.format(datetime.datetime.now().strftime("%Y_%m_%d-%H_%M_%S"))
my_report_name = report_name
my_start_date = start_date
my_end_date = end_date
my_email = email
# xlsx_writer = pd.ExcelWriter(file_name, engine="xlsxwriter")
for i in my_report_name:
print(i)
df = pd.read_csv('query_ga.csv')
df_new = df.loc[df['Report Name'].isin(['{}'.format(i)]) & df['Email ID'].isin(['{}'.format(my_email)])]
my_client_id = df_new['Ad Account id'].iat[0]
my_client_secret = df_new['App secret'].iat[0]
my_refresh_token = df_new['Access token'].iat[0]
my_dim = df_new['Dimensions'].iat[0]
my_dimensions = ast.literal_eval(my_dim)
my_met = df_new['Metrics'].iat[0]
my_metrics = ast.literal_eval(my_met)
my_seg = df_new['Segment'].iat[0]
my_segment = ast.literal_eval(my_seg)
my_profile_id = df_new['App id'].iat[0]
credentials = client.OAuth2Credentials(
access_token=None, # set access_token to None since we use a refresh token
client_id=my_client_id,
client_secret=my_client_secret,
refresh_token=my_refresh_token,
token_expiry=None,
token_uri=GOOGLE_TOKEN_URI,
user_agent=None,
revoke_uri=GOOGLE_REVOKE_URI)
credentials.refresh(httplib2.Http()) # refresh the access token (optional)
http = credentials.authorize(httplib2.Http()) # apply the credentials
service = discovery.build('analyticsreporting', 'v4', http=http)
list_dim = my_dimensions
list_metrics = my_metrics
list_segment = my_segment
dimensions_name = []
for m in list_dim:
list_item_dim = {'name': 'ga:{}'.format(m)}
dimensions_name.append(list_item_dim)
metrics_name = []
for j in list_metrics:
list_item_metrics = {'expression': 'ga:{}'.format(j)}
metrics_name.append(list_item_metrics)
segment_id = []
for k in list_segment:
p = k.split(':')[-1]
list_item_segment = {'segmentId': 'gaid::{}'.format(p)}
segment_id.append(list_item_segment)
sample_request = {
'viewId': my_profile_id,
'dateRanges': {
'startDate': my_start_date,
'endDate': my_end_date
},
'dimensions': dimensions_name,
'metrics': metrics_name,
'segments': segment_id,
"samplingLevel": "LARGE",
}
response = service.reports().batchGet(
body={
'reportRequests': sample_request
}).execute()
data_tuples = []
lst1 = response['reports'][0]['columnHeader']['dimensions']
lst2 = response['reports'][0]['columnHeader']['metricHeader']['metricHeaderEntries']
for q in range(len(lst2)):
lst1.append(lst2[q]['name'])
lst3 = response['reports'][0]['data']['rows']
for r in range(len(lst3)):
data_tuples.append(lst3[r]['dimensions'] + lst3[r]['metrics'][0]['values'])
df = pd.DataFrame(data_tuples, columns=lst1)
df['Report Name'] = i
dff = df
# dff = dff.append(df, ignore_index=True)
print(dff)
html.Br()
def to_xlsx(bytes_io):
xlsx_writer = pd.ExcelWriter(bytes_io, engine="xlsxwriter")
dff.to_excel(xlsx_writer, index=False, sheet_name=i)
xlsx_writer.save()
return send_bytes(to_xlsx, file_name)