Chapter 15 - Google Sheets (JavaScript)

Here's a JavaScript-flavoured version of the same concepts, using the official googleapis npm package to interact with Google Sheets.

Why Google Sheets + googleapis

Google Sheets has an official REST API; in JavaScript you use the googleapis package (maintained by Google) to access it.
Unlike Python's EZSheets wrapper, you work more directly with the Sheets API, but the concepts (auth, spreadsheets, sheets, cells) are the same.

Installing and setting up googleapis

Install the package:

npm install googleapis

One-time Google Cloud setup

Same 5 steps as Python:

Create a Google Cloud project at https://console.cloud.google.com.
Enable APIs — Google Sheets API and Google Drive API.
Configure OAuth consent screen — External, add scopes for drive and spreadsheets, add test users.
Create credentials — OAuth client ID → Desktop App → Download JSON → rename to credentials.json.
Authorize — run your script, it opens a browser for consent, saves tokens for future use.

Example auth setup:

const { google } = require("googleapis");
const fs = require("fs");

const credentials = JSON.parse(fs.readFileSync("credentials.json", "utf-8"));
const { client_id, client_secret, redirect_uris } = credentials.installed;

const oAuth2Client = new google.auth.OAuth2(
  client_id,
  client_secret,
  redirect_uris[0]
);

// After first-time auth flow, load saved token:
const token = JSON.parse(fs.readFileSync("token.json", "utf-8"));
oAuth2Client.setCredentials(token);

const sheets = google.sheets({ version: "v4", auth: oAuth2Client });

For simpler setups (server-to-server), use a service account instead of OAuth.

Spreadsheet objects

Creating a new spreadsheet

const res = await sheets.spreadsheets.create({
  requestBody: { properties: { title: "My New Spreadsheet" } },
});
console.log(res.data.spreadsheetId);
console.log(res.data.spreadsheetUrl);

Opening an existing spreadsheet

const spreadsheetId = "1TzOJxhNKr15tzdZxTqtQ3EmDP6em_elnbtmZIcyu8vI";

const res = await sheets.spreadsheets.get({ spreadsheetId });
console.log(res.data.properties.title);
console.log(res.data.sheets.map((s) => s.properties.title));

Listing spreadsheets (via Drive API)

const drive = google.drive({ version: "v3", auth: oAuth2Client });

const res = await drive.files.list({
  q: "mimeType='application/vnd.google-apps.spreadsheet'",
  fields: "files(id, name)",
});
for (const file of res.data.files) {
  console.log(file.id, file.name);
}

Downloading as Excel

const res = await drive.files.export(
  { fileId: spreadsheetId, mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" },
  { responseType: "arraybuffer" }
);
fs.writeFileSync("export.xlsx", Buffer.from(res.data));

Deleting spreadsheets

// Move to trash
await drive.files.update({ fileId: spreadsheetId, requestBody: { trashed: true } });

// Permanently delete
await drive.files.delete({ fileId: spreadsheetId });

Sheet objects

Creating and deleting sheets

// Add a new sheet
await sheets.spreadsheets.batchUpdate({
  spreadsheetId,
  requestBody: {
    requests: [{ addSheet: { properties: { title: "Spam" } } }],
  },
});

// Delete a sheet (need sheetId, not title)
await sheets.spreadsheets.batchUpdate({
  spreadsheetId,
  requestBody: {
    requests: [{ deleteSheet: { sheetId: 0 } }],
  },
});

Clearing a sheet

await sheets.spreadsheets.values.clear({
  spreadsheetId,
  range: "Sheet1",
});

Reading and writing cell data

Reading cells

const res = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: "Sheet1!A1:C2",
});
console.log(res.data.values);
// [['Name', 'Age', 'Favorite Movie'], ['Alice', '30', 'RoboCop']]

All values come back as strings (same as EZSheets).

Writing cells

await sheets.spreadsheets.values.update({
  spreadsheetId,
  range: "Sheet1!A1",
  valueInputOption: "USER_ENTERED",
  requestBody: {
    values: [
      ["Name", "Age", "Favorite Movie"],
      ["Alice", 30, "RoboCop"],
    ],
  },
});

valueInputOption: "USER_ENTERED" lets Google Sheets parse numbers and formulas; "RAW" stores everything as literal strings.

Reading and writing whole rows/columns

Get all rows

const res = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: "Sheet1",  // entire sheet
});
const rows = res.data.values;
console.log(rows[0]);  // header row
console.log(rows[1]);  // first data row

Update a specific row

await sheets.spreadsheets.values.update({
  spreadsheetId,
  range: "Sheet1!A3:D3",
  valueInputOption: "USER_ENTERED",
  requestBody: { values: [["Pumpkin", "11.50", "20", "230"]] },
});

Append a row

await sheets.spreadsheets.values.append({
  spreadsheetId,
  range: "Sheet1",
  valueInputOption: "USER_ENTERED",
  requestBody: { values: [["NewItem", "5.00", "10", "50"]] },
});

Batch update all rows

await sheets.spreadsheets.values.update({
  spreadsheetId,
  range: "Sheet1!A1",
  valueInputOption: "USER_ENTERED",
  requestBody: { values: rows },  // 2D array of all rows
});

Google Forms

Same concept: Google Forms writes responses to a Sheets spreadsheet.
Your Node.js script reads that spreadsheet with the Sheets API and can automate responses (send notifications, trigger workflows, etc.).

Project: Fake Blockchain (Boringcoin)

Same toy "blockchain" concept: a spreadsheet with columns sender, recipient, amount.

Step 1: Audit balances

const { google } = require("googleapis");

// ... auth setup ...

const spreadsheetId = "BORINGCOIN_SPREADSHEET_ID";
const res = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: "Sheet1",
});

const accounts = {};

for (const row of res.data.values) {
  const [sender, recipient, amountStr] = row;
  const amount = parseInt(amountStr, 10);

  if (sender === "PRE-MINE") {
    accounts[recipient] = (accounts[recipient] ?? 0) + amount;
  } else {
    accounts[sender] = (accounts[sender] ?? 0) - amount;
    accounts[recipient] = (accounts[recipient] ?? 0) + amount;
  }
}

console.log(accounts);
const total = Object.values(accounts).reduce((sum, v) => sum + v, 0);
console.log("Total Boringcoins:", total);

Step 2: Add a transaction

const args = process.argv.slice(2);
if (args.length !== 3) {
  console.log("Usage: node addTransaction.js SENDER RECIPIENT AMOUNT");
  process.exit(1);
}

const [sender, recipient, amount] = args;

await sheets.spreadsheets.values.append({
  spreadsheetId,
  range: "Sheet1",
  valueInputOption: "USER_ENTERED",
  requestBody: { values: [[sender, recipient, amount]] },
});
console.log("Transaction added.");

values.append is cleaner than fetching all rows + re-uploading — it just adds to the end.

Overall idea of the chapter

Chapter 15 shows how to automate Google Sheets from JavaScript using the googleapis package: OAuth setup, creating/reading/writing spreadsheets, bulk row/column operations, and building projects on shared live data. The JS approach uses the Sheets API directly (no EZSheets wrapper), but the core patterns — auth once, read rows, write cells, append data — are the same.

Why Google Sheets + googleapis​

Installing and setting up googleapis​

One-time Google Cloud setup​

Spreadsheet objects​

Creating a new spreadsheet​

Opening an existing spreadsheet​

Listing spreadsheets (via Drive API)​

Downloading as Excel​

Deleting spreadsheets​

Sheet objects​

Creating and deleting sheets​

Clearing a sheet​

Reading and writing cell data​

Reading cells​

Writing cells​

Reading and writing whole rows/columns​

Get all rows​

Update a specific row​

Append a row​

Batch update all rows​

Google Forms​

Project: Fake Blockchain (Boringcoin)​

Step 1: Audit balances​

Step 2: Add a transaction​

Overall idea of the chapter​