9

Using Google-Sheets-API for Node.js, how would I programmatically add/create a new Sheet into an existing Spreadsheet?

enter image description here

UFC Insider
  • 838
  • 1
  • 7
  • 19

4 Answers4

11

It is actually possible and documented under Spreadsheets/batchUpdate/Requests#AddSheetRequest

authorize(JSON.parse(clientSecretContent), (auth) => {
    const sheets = google.sheets({ version: 'v4', auth });
    const request = {
        // The ID of the spreadsheet
        "spreadsheetId": spreadsheetId,
        "resource": {
            "requests": [{
               "addSheet": {
                    // Add properties for the new sheet
                    "properties": {
                        // "sheetId": number,
                        // "title": sheetTitleSting,
                        // "index": number,
                        // "sheetType": enum(SheetType),
                        // "gridProperties": {
                        //     object(GridProperties)
                        // },
                        // "hidden": boolean,
                        // "tabColor": {
                        //     object(Color)
                        // },
                        // "rightToLeft": boolean
                    }
                }
            }]
        }
    };

    sheets.spreadsheets.batchUpdate(request, (err, response) => {
        if (err) {
            // TODO: Handle error
        } else {
            // TODO: Handle success
        }
    });
});
Rehan
  • 408
  • 1
  • 6
  • 17
Sahar Avr
  • 1,168
  • 9
  • 10
5

For posterity stake, here's a promise based (async/await) Sheets API example of how to add a sheet (tab) to a spreadsheet.

await api.spreadsheets.batchUpdate ({ 
          spreadsheetId: spreadsheetId, 
          resource: {requests: [ {addSheet: {properties: {title: tabName }}}]}});

A full example

async function addSheet (auth, spreadsheetId, tabName) {
    const api = google.sheets({version: 'v4', auth: auth});
    try {
      // Only add sheet if it doesn't already exist
      if ((await api.spreadsheets.get({spreadsheetId: spreadsheetId})).data.sheets
        .filter(sheet => sheet.properties.title === tabName).length === 0) {
        await api.spreadsheets.batchUpdate ({ 
          spreadsheetId: spreadsheetId, 
          resource: {requests: [ {addSheet: {properties: {title: tabName }}}]}});
      }
    } catch (err) {
      console.log('Sheets API Error: ' + err);
    }
}
hashlock
  • 590
  • 1
  • 6
  • 15
0

It's possible with batchUpdate and addSheet https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate

Example:

const sheetname = 'my sheetname'
const client = new google.auth.JWT(
    client_email,
    null,
    private_key,
    ['https://www.googleapis.com/auth/spreadsheets'],
    null
)

const jwt = await new Promise((resolve, reject) => {
    client.authorize((err, tokens) => {
        if (err) {
            reject(err);
        } else {
            google.options({
                auth: client
            });
            resolve();
        }
    });
});

const sheets = await google.sheets('v4');

try {
    const request = {
        spreadsheetId: spreadsheetId,  
        resource: {
            requests: [{
                addSheet: {
                    properties: {
                        title: sheetname
                    }
                }
            }],  
        },
        auth: client,
    };

    const resp = await sheets.spreadsheets.batchUpdate(request)
} catch (error) {
    console.log(error)
}
Tobi
  • 1,702
  • 2
  • 23
  • 42
-1

It looks like Google's Sheets API docs don't specify a method for creating a new sheet. The only operation you can perform on a Sheet is the copyTo method, which copies one Spreadsheet's Sheet into another Spreadsheet.

If it did exist, it would probably be done via a POST request to /v4/spreadsheets/{spreadsheetId}/sheets, but again, it's not documented and probably won't work if you tried it.