google-spreadsheet

The most popular Google Sheets API wrapper for javascript / typescript

• multiple auth options (via google-auth-library) - service account, OAuth, API key, ADC, etc
• cell-based API - read, write, bulk-updates, formatting
• row-based API - read, update, delete (based on the old v3 row-based calls)
• managing worksheets - add, remove, resize, update properties (ex: title), duplicate to same or other document
• managing docs - create new doc, delete doc, basic sharing/permissions
• export - download sheet/docs in various formats

Docs site - Full docs available at https://theoephraim.github.io/node-google-spreadsheet

---

🌈 Installation - pnpm i google-spreadsheet
(or npm i google-spreadsheet --save or yarn add google-spreadsheet)

Examples

The following examples are meant to give you an idea of just some of the things you can do

IMPORTANT NOTE - To keep the examples concise, I'm calling await at the top level which is not allowed in some older versions of node. If you need to call await in a script at the root level and your environment does not support it, you must instead wrap it in an async function like so:

1(async function () {
2  await someAsyncFunction();
3})();

The Basics

1import { GoogleSpreadsheet } from 'google-spreadsheet';
2import { JWT } from 'google-auth-library';
3
4// Initialize auth - see https://theoephraim.github.io/node-google-spreadsheet/#/guides/authentication
5const serviceAccountAuth = new JWT({
6  // env var values here are copied from service account credentials generated by google
7  // see "Authentication" section in docs for more info
8  email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
9  key: process.env.GOOGLE_PRIVATE_KEY,
10  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
11});
12
13const doc = new GoogleSpreadsheet('<the sheet ID from the url>', serviceAccountAuth);
14
15await doc.loadInfo(); // loads document properties and worksheets
16console.log(doc.title);
17await doc.updateProperties({ title: 'renamed doc' });
18
19const sheet = doc.sheetsByIndex[0]; // or use `doc.sheetsById[id]` or `doc.sheetsByTitle[title]`
20console.log(sheet.title);
21console.log(sheet.rowCount);
22
23// adding / removing sheets
24const newSheet = await doc.addSheet({ title: 'another sheet' });
25await newSheet.delete();

More info:

• GoogleSpreadsheet
• GoogleSpreadsheetWorksheet
• Authentication

Working with rows

1// if creating a new sheet, you can set the header row
2const sheet = await doc.addSheet({ headerValues: ['name', 'email'] });
3
4// append rows
5const larryRow = await sheet.addRow({ name: 'Larry Page', email: 'larry@google.com' });
6const moreRows = await sheet.addRows([
7  { name: 'Sergey Brin', email: 'sergey@google.com' },
8  { name: 'Eric Schmidt', email: 'eric@google.com' },
9]);
10
11// read rows
12const rows = await sheet.getRows(); // can pass in { limit, offset }
13
14// read/write row values
15console.log(rows[0].get('name')); // 'Larry Page'
16rows[1].set('email', 'sergey@abc.xyz'); // update a value
17rows[2].assign({ name: 'Sundar Pichai', email: 'sundar@google.com' }); // set multiple values
18await rows[2].save(); // save updates on a row
19await rows[2].delete(); // delete a row

Row methods support explicit TypeScript types for shape of the data

1type UsersRowData = {
2  name: string;
3  email: string;
4  type?: 'admin' | 'user';
5};
6const userRows = await sheet.getRows<UsersRowData>();
7
8userRows[0].get('name'); // <- TS is happy, knows it will be a string
9userRows[0].get('badColumn'); // <- will throw a type error

More info:

• GoogleSpreadsheetWorksheet > Working With Rows
• GoogleSpreadsheetRow

Working with cells

1await sheet.loadCells('A1:E10'); // loads range of cells into local cache - DOES NOT RETURN THE CELLS
2console.log(sheet.cellStats); // total cells, loaded, how many non-empty
3const a1 = sheet.getCell(0, 0); // access cells using a zero-based index
4const c6 = sheet.getCellByA1('C6'); // or A1 style notation
5// access everything about the cell
6console.log(a1.value);
7console.log(a1.formula);
8console.log(a1.formattedValue);
9// update the cell contents and formatting
10a1.value = 123.456;
11c6.formula = '=A1';
12a1.textFormat = { bold: true };
13c6.note = 'This is a note!';
14await sheet.saveUpdatedCells(); // save all updates in one call

More info:

• GoogleSpreadsheetWorksheet > Working With Cells
• GoogleSpreadsheetCell

Managing docs and sharing

1const auth = new JWT({
2  email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
3  key: process.env.GOOGLE_PRIVATE_KEY,
4  scopes: [
5    'https://www.googleapis.com/auth/spreadsheets',
6    // note that sharing-related calls require the google drive scope
7    'https://www.googleapis.com/auth/drive.file',
8  ],
9});
10
11// create a new doc
12const newDoc = await GoogleSpreadsheet.createNewSpreadsheetDocument(auth, { title: 'new fancy doc' });
13
14// share with specific users, domains, or make public
15await newDoc.share('someone.else@example.com');
16await newDoc.share('mycorp.com');
17await newDoc.setPublicAccessLevel('reader');
18
19// delete doc
20await newDoc.delete();

Why?

This module provides an intuitive wrapper around Google's API to simplify common interactions

While Google's v4 sheets API is much easier to use than v3 was, the official googleapis npm module is a giant autogenerated 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?
This module makes trade-offs for simplicity of the interface. Google's API provides a mechanism to make many requests in parallel, so if speed and efficiency are extremely important to your use case, you may want to use their API directly. There are also many lesser-used features of their API that are not implemented here yet.

Support & Contributions

This 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?
Buy Theo a beer

Sponsors

None yet - get in touch!

Contributing

Contributions are welcome, but please follow the existing conventions, use the linter, add relevant tests, and add relevant documentation.

The docs site is generated using docsify. To preview and run locally so you can make edits, run npm run docs:preview and head to http://localhost:3000 The content lives in markdown files in the docs folder.

License

This is free and unencumbered public domain software. For more info, see https://unlicense.org.