CODEWITHSUNDEEP
google-sheets-api
Oded Badt
Oded Badt

Reputation: 323

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

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?

Upvotes: 15

Views: 12277

Answers (4)

Varun Kumar
Varun Kumar

Reputation: 2751

Updated method in Node.js which also check if tab alraedy exists-

export const addNewTabToSheet = async ({
  spreadsheetId = BE_MASTER_GOOGLE_SHEET_ID,
  sheetName
}: {
  spreadsheetId?: string;
  sheetName: string;
}): Promise<string> => {
  const auth = await getAuthClient();
  // retrieve spreadsheet and check if this tab already exists
  const spreadsheet = await getSpreadSheet({
    spreadsheetId
  })
  const sheetExists = spreadsheet.sheets?.some(sheet => sheet.properties?.title === sheetName);
  if (sheetExists) {
    return spreadsheetId; // Return existing spreadsheet ID
  }
  const response = await sheets.spreadsheets.batchUpdate({
    auth,
    spreadsheetId,
    requestBody: {
      requests: [
        {
          addSheet: {
            properties: {
              title: sheetName,
              // Additional properties can be set here, such as gridProperties, tabColor, etc.
            },
          },
        },
      ],
    }
  });
  return response.data.spreadsheetId as string;
};

Upvotes: 0

cgivre
cgivre

Reputation: 576

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 sheetId The GoogleSheet id 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 sheetId, 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(sheetId, body).execute();
  }

Upvotes: 5

Nestor
Nestor

Reputation: 91

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.

Upvotes: 9

Oded Badt
Oded Badt

Reputation: 323

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);
});

Upvotes: 12

Related Questions