Menu
Using Google-Sheets-API for Node.js, how would I programmatically add/create a new Sheet into an existing Spreadsheet?
376
A Google Cloud Platform project with the API enabled. To create a project and enable an API, refer to Create a project and enable the API Note: For this quickstart, you are enabling the "Google Sheets API". Authorization credentials for a desktop application. To learn how to create credentials for a desktop application, refer to Create credentials.
This is the most basic thing to do in Excel, but none of the documentation seems to say a thing about doing it in Google Docs. Show activity on this post. The bottom of the window will list your tabs on the left and have a couple options to the left of those. Clicking the + will add a new sheet. Show activity on this post.
Run the following commands to install the libraries using npm: Create a file named index.js in your working directory and copy in the following code: // If modifying these scopes, delete token.json. // time. // Load client secrets from a local file. // Authorize a client with credentials, then call the Google Sheets API.
Google Sheets is a great tool for collaborating and working in the cloud, but many people and services still use other spreadsheet formats like Microsoft Excel files or CSV files (comma separated value files), so often you will find you need to transfer data from one of these spreadsheets into Google Sheets.
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);
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
