Create new sheet (in the same workbook) automatically and, write a new dataframe into each in a loop

Question

I have a loop that runs 'n' times and generates 1 new dataframe in each iteration. How can I automatically create a new Google sheet (in the same workbook) every time a new dataframe df is created at the end of each iteration?

I use this code to write one dataframe to a Google Sheet which is already created manually by me. Note that, I create df after performing some mathematical calculations on the data received from GOOGLEFINANCE() in df_temp.

PS- How I get the df should be irrelevant to this discussion.

Simply rephrased, I would like to have help on the code for the function Publish_df_to_Gsheet(df, Ticker) which publishes df to a new sheet named with variable value of Ticker.

#Code to write one dataframe to Google Sheets:

cell_range_insert= 'B7'
v = df_Summary.T.reset_index().T.values.tolist()
response_date = service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    valueInputOption='RAW',
    range=cell_range_insert,
    body=dict(
        majorDimension='ROWS',
        values=v
    )
).execute()

#Code to generate one dataframe (df) in each iteration:

Ticker_List= ["AAPL", "GOOG", "AMZN"]
for Ticker in Ticker_List:
    values = [['=GOOGLEFINANCE("' + str(Ticker) + '", "ALL",  "1/1/2014", "2/6/2018" ,"DAILY")']]
    cell_range_insert = 'B7'
    body = {'values': values}
    
    #Send formula to Google Sheet
    service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id,
        valueInputOption='USER_ENTERED',
        range=cell_range_insert,
        body=body
    ).execute()
    
    # Readback stock data from Google Sheets:
    response = service.spreadsheets().values().get(
        spreadsheetId=spreadsheet_id,
        majorDimension='ROWS',
        range='Sheet1'
    ).execute()

    # Readback data from Google Sheets and assign it to dataframe df:
    columns = response['values'][1]
    data = response['values'][2:]

    df_temp = pd.DataFrame(data, columns=columns)
    
    df= <Some operations on df_temp>

    Publish_df_to_Gsheet(df, Ticker) #<--- This function prints df to a new sheet named with variable Ticker

Answer 1

I don't know what Publish_df_to_Gsheet(df, Ticker) is supposed to do, however here is an idea:

from __future__ import print_function

import os.path

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/drive']

# The ID and range of a sample spreadsheet.
SPREADSHEET_ID = '<DRIVE ID OF THE SHEET FILE HERE>'


def main():
    creds = None
    # The file token.json stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.json', 'w') as token:
            token.write(creds.to_json())

    try:
        service = build('sheets', 'v4', credentials=creds)


        ticker_List= ["AAPL", "GOOG", "AMZN"]

        # Loop through items in the ticker list
        for ticker in ticker_List:

            # the following request will create the Sheet tab
            # I'm using the ticker as its name in this example
            batch_update_values_request_body = {
                'requests': [
                    {
                        'addSheet': {
                            'properties': {
                                'title': ticker
                            }
                        },
                    }
                ]
            }
            request = service.spreadsheets().batchUpdate(
                spreadsheetId=SPREADSHEET_ID, body=batch_update_values_request_body)
            response = request.execute()

            # --------------------------------

            # the following request will update the cell B7 of the newly created Sheet tab with the formula below
            values = [
                [
                    ('=GOOGLEFINANCE("' + str(ticker) + '", "ALL",  "1/1/2014", "2/6/2018" ,"DAILY")')
                ],
            ]
            body = {
                'values': values
            }
            result = service.spreadsheets().values().update(
                spreadsheetId=SPREADSHEET_ID, range=(ticker + '!B7'),
                valueInputOption='USER_ENTERED', body=body).execute()
        print('DONE')

    except HttpError as err:
        print(err)

if __name__ == '__main__':
    main()

References:

• https://developers.google.com/sheets/api/quickstart/python

• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#addsheetrequest

• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update