CODEWITHSUNDEEP
pythongoogle-sheets-apigoogle-api-python-client
Akshay Maldhure
Akshay Maldhure

Reputation: 857

Adding a new spreadsheet in an existing workbook raises HttpError "Unknown name requests"

I'm done building the Google Sheets service with the code snippet shown below and I would like to know how to create a new spreadsheet (aka tab) inside an already existing Google Sheet (workbook) with Python.

credentials = get_credentials(client_secret_file_path)
service = build('sheets', 'v4', credentials=credentials)
add_tab_request = {"requests": {"addSheet": {"properties": {"title": "New Tab"}}}}
request = service.spreadsheets().values().batchUpdate(spreadsheetId=<google_sheet_id>, body=add_tab_request)

Executing this code gives an HttpError:

Invalid JSON payload received. Unknown name "requests": Cannot find field.

The full error:

googleapiclient.errors.HttpError:
https://sheets.googleapis.com/v4/spreadsheets//values:batchUpdate?alt=json
returned "Invalid JSON payload received. Unknown name "requests": Cannot find field.">`

I did a lot of Google searching, but could not find a solution to this problem. I did not find this in the Google Sheets API v4 official documentation either. Why does not my request work?

Upvotes: 1

Views: 1810

Answers (3)

nnay84
nnay84

Reputation: 177

Here is what worked from me. I leveraged Google docs (https://developers.google.com/sheets/api/guides/batchupdate#example) & updated for the adding sheet use case

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

    # Create new sheet

    requests = []
    # Create a new spreadsheet.
    requests.append({
        'addSheet': {
            'properties': {
                'title': 'Findings',
                'tabColor': {
                    'red': 0.44,
                    'green': 0.99,
                    'blue': 0.50
                }
            }
        }
    })
    # Add additional requests (operations) ...

    body = {
        'requests': requests
    }
    response = service.spreadsheets().batchUpdate(
        spreadsheetId=SAMPLE_SPREADSHEET_ID,
        body=body).execute()

Upvotes: 0

Lorenzo P
Lorenzo P

Reputation: 1540

You can add sheets in two ways:

Below you have a snippet demonstrating both and working on my PC (python 3.6)

import os
import pickle

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', ]

if not os.path.exists('credentials.dat'):

    flow = InstalledAppFlow.from_client_secrets_file('client_id.json', SCOPES)
    credentials = flow.run_local_server()

    with open('credentials.dat', 'wb') as credentials_dat:
        pickle.dump(credentials, credentials_dat)
else:
    with open('credentials.dat', 'rb') as credentials_dat:
        credentials = pickle.load(credentials_dat)

if credentials.expired:
    credentials.refresh(Request())

spreadsheet_sdk = build('sheets', 'v4', credentials=credentials)

# we create a new sheet at creation time of the spreadsheet
create_body = {
    'properties': {
        'title': 'Newly created sheet',
        'locale': 'en_US',
    },
    'sheets': [
        {
            'properties': {
                'sheetId': 0,
                'title': "Created at creation time",
                'index': 0,
                'sheetType': 'GRID',
                'gridProperties': {
                    'rowCount': 2,
                    'columnCount': 2,
                },
            },
        }
    ]
}

new_google_spreadsheet = spreadsheet_sdk.spreadsheets().create(body=create_body).execute()

# we add a sheet to an existing spreadsheet
add_sheet_params = {
    'spreadsheetId': new_google_spreadsheet['spreadsheetId'],
    'body': {
        'requests': [
            {
                'addSheet': {
                    'properties': {
                        'sheetId': 1,
                        'title': "Added later",
                        'index': 0,
                        'sheetType': 'GRID',
                        'gridProperties': {
                            'rowCount': 2,
                            'columnCount': 2,
                        },
                        }
                }
            }
        ],
        'includeSpreadsheetInResponse': False
    }
}

new_sheet_add = spreadsheet_sdk.spreadsheets().batchUpdate(**add_sheet_params).execute()


print(new_sheet_add)

Upvotes: 0

Akshay Maldhure
Akshay Maldhure

Reputation: 857

After @SiHa's suggestion, this code worked for me. Hope this helps someone in future.

credentials = get_credentials(client_secret_file_path)
service = build('sheets', 'v4', credentials=credentials)
add_tab_request = {"requests": {"addSheet": {"properties": {"title": "New Tab"}}}}
request = service.spreadsheets().batchUpdate(spreadsheetId=<google_sheet_id>, body=add_tab_request)

Note that I had to remove the values() part from the API call.

Upvotes: 2

Related Questions