How can I add a new tab to an existing sheet via the Google Sheets API?

Question

I tried reading through the api docs but failed to find directions to perform queries regarding the existence of tabs and to create a tab if one does not exist. Does anyone have a clue?

Answer 1

Apparently it is possible using a batchUpdate: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

With one of the requests being of the form of: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#AddSheetRequest

For example, in order to add a new empty sheet with the name "FOO", one can send a batchUpdate like this one:

sheets.spreadsheets.batchUpdate(
    {
        auth: authClient,
        spreadsheetId: spreadsheetId,
        resource: {
            requests: [
                {
                    'addSheet':{
                        'properties':{
                            'title': 'FOO'
                        }
                    } 
                }
            ],
        }
    },
    function(err, response) {
        if (err) return callback('The API returned an error: ' + err);
        console.log("success: ", response);
});

Answer 2

I got this to work using Python 3. I had been confused by the batchUpdate method used for values vs. spreadsheets, so once I corrected this, the sheet was created successfully. Here's the working snippet:

body = {
'requests': [{
    'addSheet': {
        'properties': {
            'title': write_sheet_name,
            'tabColor': {
                'red': 0.44,
                'green': 0.99,
                'blue': 0.50
            }
        }
    }
}]
}

result = service.spreadsheets().batchUpdate(
    spreadsheetId=SPREADSHEET_ID,
    body=body).execute()

See https://developers.google.com/sheets/api/guides/batchupdate

The above is not to be confused with batch update of values in sheet:

result = service.spreadsheets().values().batchUpdate(
spreadsheetId=SPREADSHEET_ID,
body=body).execute()

See https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values for batchUpdate of values.

Answer 3

If you are looking for how to do this in java, here's a function that does it.

  /**
   * Adds a new tab to an existing GoogleSheet document.
   * @param service An authenticated GoogleSheet service
   * @param sheetName The GoogleSheet name of the document
   * @param tabName The name of the tab you wish to add to the GoogleSheet document
   * @return The response from adding the sheet.
   * @throws IOException Throws an IOException if anything goes wrong.
   */
  public static BatchUpdateSpreadsheetResponse AddTabToGoogleSheet(Sheets service, String sheetName, String tabName)
    throws IOException {
    List<Request> requests = new ArrayList<>();
    requests.add(new Request().setAddSheet(new AddSheetRequest().setProperties(new SheetProperties()
      .setTitle(tabName))));
    BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);
    return service.spreadsheets().batchUpdate(sheetName, body).execute();
  }