Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Node.js

submit.md

Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Node.js

This is a sample script for retrieving all values from all sheets from URL of 2PACX- of Web Published Google Spreadsheet using Node.js.

In this post, it supposes that the Google Spreadsheet has already been published for Web. Ref

Before you use this sample script, please install SheetJS js-xlsx.

Flow

The flow of this method is as follows.

1. Retrieve XLSX data from the URL of web published Google Spreadsheet as the buffer data.
   • The URL is like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml.
2. XLSX data is parsed with SheetJS js-xlsx.
3. Retrieve all values from all sheets.

Sample script

Please set spreadsheetUrl.

const request = require("request");
const xlsx = require("xlsx");

const spreadsheetUrl =
  "https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml";

if (
  /https:\/\/docs\.google\.com\/spreadsheets\/d\/e\/2PACX-.+\/pub(html)?/.test(
    spreadsheetUrl
  )
) {
  const temp = spreadsheetUrl.split("/");
  temp.pop();
  const url = temp.join("/") + "/pub?output=xlsx";
  request({ url: url, encoding: null }, (err, res, buf) => {
    if (err) {
      console.error(err);
      return;
    }
    const workbook = xlsx.read(buf, { type: "buffer" });
    const r = Object.entries(workbook.Sheets).map(([k, v]) => ({
      sheetName: k,
      values: xlsx.utils.sheet_to_json(v, { header: 1 }),
    }));
    console.log(r);
  });
}

Result

When above script is run, the following sample value is returned.

[
  {
    "sheetName": "Sheet1",
    "values": [["a1", "b1", "c1"], ["a2", "b2", "c2"], , ,]
  },
  {
    "sheetName": "Sheet2",
    "values": [["a1", "b1", "c1"], ["a2", "b2", "c2"], , ,]
  },
  ,
  ,
]

Reference

SheetJS js-xlsx