How to create a new sheet within a spreadsheet using Google Sheets API

Question

The official documentation shows how to create a spreadsheet, but I can't find how to create a sheet. How do I do it in Python?

Answer 1

@PCDSandwichMan's answer uses gspread, which is a very useful third-party library to simplify the Sheets API in Python. Not all of Google's APIs have libraries like this, though, so you may want to learn the regular way as well.

As an alternative in case that you want to use Google's API you can check out the documentation for Google's Python API libraries. Most direct changes to a spreadsheet's properties are done with spreadsheets().batchUpdate(). Here's a sample based on Google's Python Quickstart that adds a new sheet.

import os.path

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

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

# The ID of the spreadsheet
YOUR_SPREADSHEET = 'some-id'

def main():
    creds = None
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)

        with open('token.json', 'w') as token:
            token.write(creds.to_json())
    try:
        sheetservice = build('sheets', 'v4', credentials=creds)
        
        body = {
            "requests":{
                "addSheet":{
                    "properties":{
                        "title":"New Sheet"
                    }
                }
            }
        }

        sheetservice.spreadsheets().batchUpdate(spreadsheetId=YOUR_SPREADSHEET, body=body).execute()
        
    except HttpError as err:
        print(err)

Most of it is the authorization. The relevant part is within the try block. You pretty much just call the batchUpdate() method with the spreadsheet's ID and a body object with all the requests you want to make.

sheetservice = build('sheets', 'v4', credentials=creds)
        
body = {
    "requests":[{
        "addSheet":{
            "properties":{
                "title":"New Sheet"
            }
        }
    }]
}

sheetservice.spreadsheets().batchUpdate(spreadsheetId=YOUR_SPREADSHEET, body=body).execute()

Sources:

• Google Sheets API Python docs
• General info on how to use batchUpdate
• Google Python Quickstart

Answer 2

Could you do something like this?

import gspread
from oauth2client.service_account import ServiceAccountCredentials


scope = [
    "https://spreadsheets.google.com/feeds",
    "https://www.googleapis.com/auth/drive",
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    "client_secret.json", scope
)
gc = gspread.authorize(credentials)

def add_sheet(sheet_name):
    wks = gc.add_worksheet(sheet_name, rows="1", cols="1")
    return wks