Python for Google Sheets: write dataframes to different sheets in the same workbook

Question

Using the code below, I am able to write the dataframe df1 to the default first sheet (starting at cell ‘B7’) of the Google Sheet workbook. In the same file, how can I add a new sheet to write each of these data frames (df2, df3, …).

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()

Auto re-size Columns and Rows width:

request_body = {
        'requests': [
            {
                'autoResizeDimensions': {
                    'dimensions': {
                        'sheetId': sheet_id,
                        'dimension': 'COLUMNS',
                        'startIndex': 0,
                        'endIndex': 26
                    }
                }
            },
            {
                'autoResizeDimensions': {
                    'dimensions': {
                        'sheetId': sheet_id,
                        'dimension': 'ROWS',
                        'startIndex': 0,
                        'endIndex': 1000
                    }
                }
            }
        ]
    }
response = service.spreadsheets().batchUpdate(
        spreadsheetId=spreadsheet_id,
        body=request_body
    ).execute()

Answer 1

To achieve that you should specify the sheet you want in the range argument. For example, if your second sheet is called "Sheet2", for df2 you would need to change the cell_range_insert variable:

cell_range_insert= 'Sheet2!B7",'
values = df2.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=df2.T.reset_index().T.values.tolist()
    )
).execute()

This will insert the values in cell B7 in the second sheet.

Create and update second sheet

If you want to create a new sheet, you can add this to you requests_body variable:

sheet_id_2 = 2 # id that your second sheet will have

request_body = {
        'requests': [
            # creates a new Sheet with particular id and title
            {
                'addSheet': {
                    'properties': {
                        'sheetId': sheet_id_2,
                        'title': "Sheet2",
                    }
                }
            },
            # Resizing for first sheet (sheet_id)
            {
                'autoResizeDimensions': {
                    'dimensions': {
                        'sheetId': sheet_id,
                        'dimension': 'COLUMNS',
                        'startIndex': 0,
                        'endIndex': 26
                    }
                }
            },
            {
                'autoResizeDimensions': {
                    'dimensions': {
                        'sheetId': sheet_id,
                        'dimension': 'ROWS',
                        'startIndex': 0,
                        'endIndex': 1000
                    }
                }
            },
            # Resizing for second sheet (sheet_id_2)
            {
                'autoResizeDimensions': {
                    'dimensions': {
                        'sheetId': sheet_id_2,
                        'dimension': 'COLUMNS',
                        'startIndex': 0,
                        'endIndex': 26
                    }
                }
            },
            {
                'autoResizeDimensions': {
                    'dimensions': {
                        'sheetId': sheet_id_2,
                        'dimension': 'ROWS',
                        'startIndex': 0,
                        'endIndex': 1000
                    }
                }
            }
        ]
    }
response = service.spreadsheets().batchUpdate(
        spreadsheetId=spreadsheet_id,
        body=request_body
    ).execute()

Just make sure that this part of the code goes before the part where you insert the values.

If you want to add an arbitrary number of sheets you will have to go with a loop and use append instead of hardcoding the request_body dict.