Using Google-Sheets-API for Node.js, how would I programmatically add/create a new Sheet into an existing Spreadsheet?
4 Answers
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
}
});
});
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);
}
}

- 590
- 1
- 6
- 15
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)
}

- 1,702
- 2
- 23
- 42
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.

- 35
- 4