Download excel file with multiple sheets with dataframes

Dash Python
2

You should loop the sheets inside the to_xlsx function, i.e. something like

def make_report(i, my_start_date, my_end_date):
    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
    return df


@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

        def to_xlsx(bytes_io):
            xlsx_writer = pd.ExcelWriter(bytes_io, engine="xlsxwriter")
            for i in my_report_name:
                df = make_report(i, my_start_date, my_end_date)
                df.to_excel(xlsx_writer, index=False, sheet_name=i)
            xlsx_writer.save()

        return send_bytes(to_xlsx, file_name)