How To Use Google Sheets API v4 To Create New Sheet or Tab in Spreadsheet with PHP

Question

With PHP, it is unclear from the Google Sheets API v4 documentation on how to create a new sheet (aka "tab") in an existing spreadsheet.

I can do it with batchUpdate, oddly, via the API Explorer, but they don't explain from that how to do this in PHP.

Volomike · Accepted Answer · 2019-05-31T03:01:04.813

30

It looks like the documentation is saying that we must use batchUpdate from $service->spreadsheets_values collection. But that's incorrect. It should be the $service->spreadsheets collection. The proper answer after a lot of trial and error is:

try {
    $body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
        'requests' => array(
            'addSheet' => array(
                'properties' => array(
                    'title' => 'New Sheet'
                )
            )
        )
    ));
    $result1 = $service->spreadsheets->batchUpdate($sSpreadsheetID,$body);
} catch(Exception $ignore) {}

Given that you have already authenticated your API to get a $client object, and then used that to get a $service object of class Google_Service_Sheets, and given that you have assigned $sSpreadsheetID to the ID of your spreadsheet, then the above routine will attempt to add a new tab to your spreadsheet named 'New Sheet' without destroying any existing one, and will not show errors if this tab already exists. At that point, you can do something new with that new sheet by addressing it with the A1 Notation.

edited May 31 '19 at 03:01

answered Feb 19 '17 at 10:26

Volomike

23,743
21
113
209

Here: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet – Volomike Feb 19 '17 at 15:45
Can you quote the part of that page that says to use the values collections to add a sheet? I don't see that, but if it does say it I'd like to fix it. – Sam Berlin Feb 21 '17 at 12:25
1

@SamBerlin I was incorrect. I saw something where someone mentioned I need to use batchUpdate to add a sheet, but at the time I only saw batchUpdate() class method on the spreadsheets_values() class object, not realizing that there was also a batchUpdate() on the spreadsheets() class object. In general, adding sheets is poorly documented on the v4 API. – Volomike Feb 24 '17 at 07:11
1

OK thanks for the reply. I'll make a note to add more 'add sheet' docs. – Sam Berlin Feb 24 '17 at 14:26
1

I would be great if someone could help me achieve this with Python. – Akshay Maldhure Aug 29 '18 at 06:57
This solution worked for me, but causes an exception when the tab already exists. – cloudxix Jun 13 '20 at 19:39
This solution worked for me but how can I get the sheetID of the sheet I just created? I can get a list of all the sheetIDs using the GET method, but how do I get the id of the one I just created? – Joey Martin Jul 29 '22 at 12:44
@JoeyMartin I'm not certain, but look at the [response](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate#response-body), so, `$result1`. Also, [this](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/response#addsheetresponse). – Volomike Jul 29 '22 at 13:30

score 6 · Answer 2 · answered Apr 18 '21 at 15:50

Take a look this

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = 'credentials.json'
SPREADSHEET_ID = spreadsheetId

creds = None
creds = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = discovery.build(
    'sheets', 'v4', credentials=creds, cache_discovery=False)

batch_update_values_request_body = {
        'requests': [
            {
                'addSheet': {
                    'properties': {
                        'title': worksheetName
                    }
                }
            }
        ]
    }
    request = service.spreadsheets().batchUpdate(
        spreadsheetId=SPREADSHEET_ID, body=batch_update_values_request_body)
    response = request.execute()

score 2 · Answer 3 · edited May 13 '20 at 16:20

This is how to do it with Node.js client:

    const gsapi = google.sheets({version: 'v4', auth: client})
    const options = {
      spreadsheetId: 'YOUR ID IS EVERYTHING BETWEEN /d/ and /edit on your spreadsheets URL'
    }

    const res = await gsapi.spreadsheets.batchUpdate({
      spreadsheetId: options.spreadsheetId,
      requestBody: {
        requests: [{
          addSheet: {
            properties: {
              title: table,
            }
          }
        }]
      }
    })

    console.log(res)

How To Use Google Sheets API v4 To Create New Sheet or Tab in Spreadsheet with PHP

3 Answers3