Learn how to use Dash Bio for next-gen sequencing & quality control. 🧬 Register for the Oct 27 webinar.

Download excel file with multiple sheets with dataframes

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)

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)