The most popular Google Sheets API wrapper for javascript
Docs site - Full docs available at https://theoephraim.github.io/node-google-spreadsheet
Regardless, please upgrade to the latest version of this module (v3) which uses the newer sheets v4 API
Examplesthe following examples are meant to give you an idea of just some of the things you can do
(async function() { await someAsyncFunction(); }()); The Basicsconst { GoogleSpreadsheet } = require('google-spreadsheet'); // Initialize the sheet - doc ID is the long id in the sheets URL const doc = new GoogleSpreadsheet('<the sheet ID from the url>'); // Initialize Auth - see https://theoephraim.github.io/node-google-spreadsheet/#/getting-started/authentication await doc.useServiceAccountAuth({ // env var values are copied from service account credentials generated by google // see "Authentication" section in docs for more info client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL, private_key: process.env.GOOGLE_PRIVATE_KEY, }); await doc.loadInfo(); // loads document properties and worksheets console.log(doc.title); await doc.updateProperties({ title: 'renamed doc' }); const sheet = doc.sheetsByIndex[0]; // or use doc.sheetsById[id] or doc.sheetsByTitle[title] console.log(sheet.title); console.log(sheet.rowCount); // adding / removing sheets const newSheet = await doc.addSheet({ title: 'hot new sheet!' }); await newSheet.delete(); More info:
Working with rows// create a sheet and set the header row const sheet = await doc.addSheet({ headerValues: ['name', 'email'] }); // append rows const larryRow = await sheet.addRow({ name: 'Larry Page', email: '' }); const moreRows = await sheet.addRows([ { name: 'Sergey Brin', email: '' }, { name: 'Eric Schmidt', email: '' }, ]); // read rows const rows = await sheet.getRows(); // can pass in { limit, offset } // read/write row values console.log(rows[0].name); // 'Larry Page' rows[1].email = ''; // update a value await rows[1].save(); // save updates await rows[1].delete(); // delete a row More info:
Working with cellsawait sheet.loadCells('A1:E10'); // loads range of cells into local cache - DOES NOT RETURN THE CELLS console.log(sheet.cellStats); // total cells, loaded, how many non-empty const a1 = sheet.getCell(0, 0); // access cells using a zero-based index const c6 = sheet.getCellByA1('C6'); // or A1 style notation // access everything about the cell console.log(a1.value); console.log(a1.formula); console.log(a1.formattedValue); // update the cell contents and formatting a1.value = 123.456; c6.formula = '=A1'; a1.textFormat = { bold: true }; c6.note = 'This is a note!'; await sheet.saveUpdatedCells(); // save all updates in one call More info:
Why?
While Google's v4 sheets api is much easier to use than v3 was, the official googleapis npm module is a giant meta-tool that handles every Google product. The module and the API itself are awkward and the docs are pretty terrible, at least to get started. In what situation should you use Google's API directly? Support & ContributionsThis module was written and is actively maintained by Theo Ephraim. Are you actively using this module for a commercial project? Want to help support it? SponsorsNone yet - get in touch! ContributingContributions are welcome, but please follow the existing conventions, use the linter, add relevant tests, add relevant documentation. The docs site is generated using docsify. To preview and run locally so you can make edits, run LicenseThis is free and unencumbered public domain software. For more info, see https://unlicense.org. How do I change the sheet name in Google Sheets?Touch a worksheet tab at the bottom of your screen to select the sheet so that you can get started changing it. On Android tablets and phones, a dialog box will ask you to enter the new sheet name. Press OK to confirm changes. On iOS devices, a cursor will appear letting you change the name directly on the tab.
How do I find the sheet ID in Google Sheets API?The spreadsheet ID can be discovered from the spreadsheet URL; the sheet ID can be obtained from the spreadsheet.
How do you title a sheet in Google Sheets?Make a title or heading. On your computer, open a document in Google Docs.. Select the text you want to change.. Click Format. Paragraph styles.. Click a text style: Normal text. Title. Subtitle. Heading 1-6.. Click Apply 'text style. '. |