Python Google Sheets API make values update and sheet properties update with a single batch update

Question

I am wondering can a range of values that I want to update to a spreadsheet can be sent in a single batch update together with the spreadsheet properties update? For example (code below) I want make a spreadsheet property update (add worksheet) and then add data to the same, newly added worksheet. How can this be achieved, if it can be done using a single request? (not making 1 request .values().batchUpdate() and 2nd .batchUpdate())

I have the following:

REQUESTS = []

# addSheet request
REQUESTS.append({"addSheet": {"properties": {"title": "MySheet",'sheetId': '0'}}})

# add value request
REQUESTS.append({'range':'MySheet!A1', 'values': list_of_lists, 'majorDimension':'COLUMNS'})

# create a request body
body = {"requests": REQUESTS}
# make update
sheet_service.spreadsheets().batchUpdate(spreadsheetId=sheet_id, body=body).execute()

The code above return the following error:

"Invalid JSON payload received. Unknown name "range" at 'requests[1]': Cannot find field.
Invalid JSON payload received. Unknown name "values" at 'requests[1]': Cannot find field.
Invalid JSON payload received. Unknown name "majorDimension" at 'requests[1]': Cannot find field.". Details: "[{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'field': 'requests[1]', 'description': 'Invalid JSON payload received. Unknown name "range" at \'requests[1]\': Cannot find field.'}, {'field': 'requests[1]', 'description': 'Invalid JSON payload received. Unknown name "values" at \'requests[1]\': Cannot find field.'}, {'field': 'requests[1]', 'description': 'Invalid JSON payload received. Unknown name "majorDimension" at \'requests[1]\': Cannot find field.'}]}]">

Thanks

Answer 1

I believe your goal as follows.

• You want to insert new sheet to the Google Spreadsheet.
• You want to add the values to the inserted sheet.
• You want to achieve above 2 processes with one API call using the method of batchUpdate in Sheets API.
• You want to achieve above using googleapis for python.

Modification points:

• In this case, in order to add the values to the inserted sheet, I would like to propose to manually add the sheet ID as the unique value.
• I think that your 1st request body can be used. But your 2nd request body cannot be used for the batchUpdate method.

When above points are reflected to your script, it becomes as follows.

Modified script:

spreadsheet_id = '###'  # Please set the Spreadsheet ID.
list_of_lists = [['sample value 1', 123, 456], ['sample value 2', 789, 123]]  ## Please set your values.

sheet_service = build('sheets', 'v4', credentials=creds)
rows = []
for r in list_of_lists:
    col = []
    for c in r:
        col.append({"userEnteredValue": ({"numberValue": c} if str(c).replace('.', '', 1).isdigit() else {"stringValue": c})})
    rows.append({"values": col})
print(rows)
new_sheet_id = 123456
body = {
    "requests": [
        {
            "addSheet": {
                "properties": {
                    "title": "MySheet",
                    "sheetId": new_sheet_id
                }
            }
        },
        {
            "updateCells": {
                "start": {
                    "sheetId": new_sheet_id,
                    "rowIndex": 0,
                    "columnIndex": 0
                },
                "rows": rows,
                "fields": "userEnteredValue"
            }
        }
    ]
}
res = sheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
print(res)

• In this sample script, 123456 is used as the sheet ID of the inserted sheet. If this value has already been used, please change it.
• In this sample script, the string and number types are used as the additional value to the inserted sheet. If you want to use other types, please modify above script for your actual situation.

References:

• Method: spreadsheets.batchUpdate
• AddSheetRequest
• UpdateCellsRequest