Python for Google Sheets: Create new sheet (in the same workbook) automatically and write a new dataframe into it

user_6293211 0
Viewed 275

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 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:

#Code to write one dataframe to Google Sheets:

cell_range_insert= 'B7'
values = df1.to_json()
body = {'values': values}
response_date= service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    valueInputOption='RAW',
    range=cell_range_insert,
    body=dict(
        majorDimension= 'ROWS',
        values=df1.T.reset_index().T.values.tolist()
    )
).execute()

#Code to generate one dataframe 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 = pd.DataFrame(data, columns=columns)
pandas google-apps-script google-sheets-api google-sheets python
1 Answers

In your situation, how about the following flow.

  1. Insert new sheets and put the formula to the cell "B7" to each new sheet using the batchUpdate method.
  2. Retrieve values from each new sheet using the values.batchGet method.

When this flow is reflected in your script, it becomes as follows.

Modified script:

spreadsheet_id = "###" # Please set your Spreadsheet ID.
requests = []
Ticker_List = ["AAPL", "GOOG", "AMZN"]
start_sheet_id = 123456
for i, Ticker in enumerate(Ticker_List):
    sheet_id = start_sheet_id + i
    formula = '=GOOGLEFINANCE("' + str(Ticker) + '", "ALL",  "1/1/2014", "2/6/2018" ,"DAILY")'
    requests.extend(
        [
            {"addSheet": {"properties": {"title": Ticker, "sheetId": sheet_id}}},
            {
                "updateCells": {
                    "start": {"sheetId": sheet_id, "rowIndex": 6, "columnIndex": 1},
                    "rows": [
                        {
                            "values": [
                                {"userEnteredValue": {"formulaValue": formula}}
                            ]
                        }
                    ],
                    "fields": "userEnteredValue",
                }
            },
        ]
    )

service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body={"requests": requests}).execute()

time.sleep(5) # This is required to be used.

res = service.spreadsheets().values().batchGet(spreadsheetId=spreadsheet_id, ranges=[f"'{e}'!B7:G" for e in Ticker_List]).execute()

# Put the values to dataframe.
for e in res["valueRanges"]:
    values = e["values"]
    columns = values[0]
    data = values[1:]
    df = pd.DataFrame(data, columns=columns)
    print(df)
  • When this script is run, 3 new sheets are inserted as the sheet names of "AAPL", "GOOG", "AMZN". And the formula is put to the cell "B7" of each inserted sheet. This is run with the batchUpdate method.
  • After the batchUpdate method was finished, 5 seconds are waiting. In this case, please adjust for your actual situation. 10 seconds might be suitable.
  • After the wait, the values are retrieved from each inserted sheet. And, the values are put into the dataframe.

Note:

  • In this script, 2 quotas of Sheets API are used.
  • About the dataframe, this script is from your showing script. So, please modify this for your actual situation.

References:

user_71086530
Related