Updating multiple worksheets in google spreadsheet

Question

I have some code that look this:

from __future__ import print_function
import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

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

spreadsheet = {
    'properties': {
        'title': 'Data Integrity Report Completed on {}'.format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    }
}
spreadsheet = service.spreadsheets().create(body=spreadsheet,
                                            fields='spreadsheetId').execute()
gsheet_id = spreadsheet.get('spreadsheetId')

response_date = service.spreadsheets().values().append(
        spreadsheetId=gsheet_id,
        valueInputOption='RAW',
        range='A1:Z100',
        body=dict(
            majorDimension='ROWS',
            values=miss_occ_df.T.reset_index().T.values.tolist())
    ).execute()

This code basically creates a google spreadsheet and appends my dataframe to the first worksheet. What I want is to have a spreadsheet that has 3 worksheets. I also need to name each worksheet and upload 3 different dataframes to each worksheet. How can I do this?

Answer 1

• You want to achieve the following things.
  • Create new Spreadsheet.
  • You want 3 sheets in the created Spreadsheet.
    • You want to rename the sheets.
  • Put the values to each sheet.
• You want to achieve them using google-api-python-client with Python.

If my understanding is correct, how about this modification? I think that your goal can be achieved by one API call. But in your case, it seems that 2 dimensional array for the request body is required to be used. So in this answer, I would like to propose the method for achieving your goal by 2 API calls. So please think of this as just one of several answers.

The flow of this method is as follows.

Flow:

1. Create new Spreadsheet.
   • At that time, the 3 sheets (worksheets) are created by giving the names. In this case, the method of create() is used.
2. Put the values to 3 sheets using the method of values().batchUpdate().
   • In your case, the values are put to the new Spreadsheet. So the values can be put using this method.

Modified script:

Please modify the script below service = build('sheets', 'v4', credentials=creds) as follows. And please set variables of sheet names and values.

# Please set worksheet names.
sheetNameForWorksheet1 = "sample1"
sheetNameForWorksheet2 = "sample2"
sheetNameForWorksheet3 = "sample3"

# Please set values for each worksheet. Values are 2 dimensional array.
valuesForWorksheet1 = miss_occ_df.T.reset_index().T.values.tolist()
valuesForWorksheet2 = miss_occ_df.T.reset_index().T.values.tolist()
valuesForWorksheet3 = miss_occ_df.T.reset_index().T.values.tolist()

spreadsheet = {
    'properties': {
        'title': 'Data Integrity Report Completed on {}'.format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    },
    "sheets": [
        {
            "properties": {
                "title": sheetNameForWorksheet1
            }
        },
        {
            "properties": {
                "title": sheetNameForWorksheet2
            }
        },
        {
            "properties": {
                "title": sheetNameForWorksheet3
            }
        }
    ]
}
spreadsheet = service.spreadsheets().create(body=spreadsheet, fields='spreadsheetId').execute()
gsheet_id = spreadsheet.get('spreadsheetId')

batch_update_values_request_body = {
    "data": [
        {
            "values": valuesForWorksheet1,
            "range": sheetNameForWorksheet1,
        },
        {
            "values": valuesForWorksheet2,
            "range": sheetNameForWorksheet2,
        },
        {
            "values": valuesForWorksheet3,
            "range": sheetNameForWorksheet3,
        }
    ],
    "valueInputOption": "USER_ENTERED"
}

response = service.spreadsheets().values().batchUpdate(spreadsheetId=gsheet_id, body=batch_update_values_request_body).execute()

Note:

• This modified script supposes that you have already been able to put and get values to the Spreadsheet using Sheets API.

References:

• Method: spreadsheets.values.batchUpdate
• Method: spreadsheets.create

At first, please confirm whether my understanding for your question is correct. If I misunderstood your question and this was not the direction you want, I apologize.