repository·main·Indexed 25 days ago
https://github.com/theoephraim/node-google-spreadsheetA Node.js wrapper for the Google Sheets API (version 5.3.0) that provides a simple interface to read, write, and manage spreadsheets, worksheets, rows, and cells. It supports Service Account authentication via google-auth-library, TypeScript types for row data, and fine-grained cell control including A1 notation and formatting.
The cell-based interface allows for fine-grained control over individual cells, including formulas and formatting. It is more feature-rich than the row-based API but more complex.
Workflow:
loadCells().getCell() or getCellByA1().cell.value = 'new value').saveUpdatedCells() or saveCells(cells).Basic document properties and child worksheets are loaded only after calling doc.loadInfo(). Once loaded, these properties are read-only; to update properties like title or locale, use the doc.updateProperties() method.
spreadsheetId (String): The document ID (set during initialization, not editable).title (String): Document title.locale (String): Document locale/language (e.g., "en", "en_US").timeZone (String): Document timezone (CLDR format, e.g., "America/New_York").autoRecalc (Enum): Recalculation interval.defaultFormat (Object): Default cell formatting.spreadsheetTheme (Object): Spreadsheet theme.iterativeCalculationSettings (Object): Settings for iterative calculations.Worksheets are instances of GoogleSpreadsheetWorksheet and can be accessed via:
sheetsById: Keyed by sheetId (number).sheetsByTitle: Keyed by title (string). Warning: Beware of title conflicts.sheetsByIndex: An array of sheets ordered by their index in the Google Sheets UI.sheetCount: The number of child worksheets.Worksheet object. When a row is deleted, the cache automatically updates the row numbers of all other loaded rows to keep them in sync. Calling sheet.clearRows() will also clear the row values within this cache.The GoogleSpreadsheetRow class represents an individual row in a spreadsheet and provides methods to read/write values, save updates, and delete the row.
Note on Initialization: You do not initialize rows directly. Instead, you load them from a sheet using sheet.getRows() or create new ones using sheet.addRow().
The node-google-spreadsheet module relies on google-auth-library. You can pass a JWT, OAuth2Client, or GoogleAuth object as the second argument when initializing your GoogleSpreadsheet instance.
Common authentication strategies include:
The row-based interface is a simplified way to treat a sheet like a database where the first row contains column headers.
Important Considerations:
loadHeaderRow or setHeaderRow.A Service Account is a 2-legged OAuth method designed for applications to act as a bot user rather than an individual end-user. This is the recommended method for apps that need to access specific documents shared with the service account's email address.
You can use the JWT class from google-auth-library to initialize the connection. It is best practice to load credentials from environment variables.
Note for Heroku/Platform users: Private keys containing newlines (\n) can sometimes be corrupted by environment variable managers. You may need to use .replace(/\n/g, "\n") when loading the key from an environment variable.
import { JWT } from 'google-auth-library'
const SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive.file',
];
// Using environment variables (Recommended)
const jwtFromEnv = new JWT({
email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, "\n"),
scopes: SCOPES,
});
const doc = new GoogleSpreadsheet('<YOUR-DOC-ID>', jwtFromEnv);You can manage entire documents using the GoogleSpreadsheet class.
Note: To perform sharing-related operations, your authentication scopes must include the Google Drive scope (e.g., https://www.googleapis.com/auth/drive.file).
const auth = new JWT({
email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
key: process.env.GOOGLE_PRIVATE_KEY,
scopes: [
'https://www.googleapis.com/auth/spreadsheets',
// note that sharing-related calls require the google drive scope
'https://www.googleapis.com/auth/drive.file',
],
});
// create a new doc
const newDoc = await GoogleSpreadsheet.createNewSpreadsheetDocument(auth, { title: 'new fancy doc' });
// share with specific users, domains, or make public
await newDoc.share('someone.else@example.com');
await newDoc.share('mycorp.com');
await newDoc.setPublicAccessLevel('reader');
// delete doc
await newDoc.delete();You cannot initialize a GoogleSpreadsheetWorksheet directly. Worksheets are accessed through a GoogleSpreadsheet instance after calling doc.loadInfo().
Common ways to access worksheets include:
doc.sheetsByIndex[n] to get a sheet based on its position in the UI.doc.sheetsById[id] if the specific sheet ID is known.await doc.addSheet() to create a new worksheet within the document.const doc = new GoogleSpreadsheet('<YOUR-DOC-ID>', auth);
await doc.loadInfo(); // loads sheets and other document metadata
const firstSheet = doc.sheetsByIndex[0]; // in the order they appear on the sheets UI
const sheet123 = doc.sheetsById[123]; // accessible via ID if you already know it
const newSheet = await doc.addSheet(); // adds a new sheetYou can manage rows using the row-based API. This includes appending new rows, reading existing rows, and updating or deleting specific rows.
If you are creating a new sheet, you can define the header row using headerValues.
// if creating a new sheet, you can set the header row
const sheet = await doc.addSheet({ headerValues: ['name', 'email'] });
// append rows
const larryRow = await sheet.addRow({ name: 'Larry Page', email: 'larry@google.com' });
const moreRows = await sheet.addRows([
{ name: 'Sergey Brin', email: 'sergey@google.com' },
{ name: 'Eric Schmidt', email: 'eric@google.com' },
]);
// read rows
const rows = await sheet.getRows(); // can pass in { limit, offset }
// read/write row values
console.log(rows[0].get('name')); // 'Larry Page'
rows[1].set('email', 'sergey@abc.xyz'); // update a value
rows[2].assign({ name: 'Sundar Pichai', email: 'sundar@google.com' }); // set multiple values
await rows[2].save(); // save updates on a row
await rows[2].delete(); // delete a rowUse Application Default Credentials when your code is running within Google infrastructure (like Google Cloud Functions, App Engine, or Compute Engine). This method automatically detects the environment's credentials using the GoogleAuth class from google-auth-library.
const { GoogleAuth } = require('google-auth-library');
const adcAuth = new GoogleAuth({
scopes: ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive.file'],
});
const doc = new GoogleSpreadsheet('<YOUR-DOC-ID>', adcAuth);You can set a formula by passing it as a string to a property. However, the row-based interface only returns the resolved value.
Warning: If you update other values in the row without re-setting the formula, the formula will be overwritten by its last resolved value. It is recommended to only use formulas if you are primarily reading data or inserting new rows, rather than performing frequent updates to existing rows.