Download Google Sheets file and read data using SheetJS on Node

Question

I have a spreadsheet on Google Sheets that contains some data. I have published it publicly, so that I can download the data as an .xlsx file using a URL. What I want to do is the following in Node:

• Download the file from the URL
• Prepare the contents as ArrayBuffer
• Read it using SheetJS

The following is the URL for the file: Google Sheets link.

Going by SheetJS library, I know I need to use XLSX.read(data, opts), but I can't seem to figure out how to do it exactly. I have the following code so far:

var https = require('https');
var fs = require('fs');
var XLSX = require("xlsx");

var fileURL = "https://docs.google.com/spreadsheets/d/e/2PACX-1vR9jv7gj6o0dtL0QoeHxW7Ux2fk0Br6slV3mg-uzZY6hc17Zw-_cXRz0-pilCeZx_lDzaRAo0tNfueT/pub?output=xlsx"

// Somehow use the url to read the file using XLSX.read(data, opts) and store it in a variable called workbook

var sheet1 = workbook.Sheets[workbook.SheetNames[0]]
var sheet2 = workbook.Sheets[workbook.SheetNames[1]]
        
console.log(XLSX.utils.sheet_to_json(sheet1));
console.log(XLSX.utils.sheet_to_json(sheet2));

How do I actually do this? I can work with a file locally perfectly fine, but with the URL approach, I'm pretty lost. Any help is appreciated!

Answer 1

In your situation, how about the following modification?

Modified script:

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

const fileURL =
  "https://docs.google.com/spreadsheets/d/e/2PACX-1vR9jv7gj6o0dtL0QoeHxW7Ux2fk0Br6slV3mg-uzZY6hc17Zw-_cXRz0-pilCeZx_lDzaRAo0tNfueT/pub?output=xlsx";

request.get(fileURL, { encoding: null }, function (err, res, data) {
  if (err || res.statusCode != 200) {
    console.log(res.statusCode);
    return;
  }
  const buf = Buffer.from(data);
  const workbook = XLSX.read(buf);

  var sheet1 = workbook.Sheets[workbook.SheetNames[0]];
  var sheet2 = workbook.Sheets[workbook.SheetNames[1]];
  console.log(XLSX.utils.sheet_to_json(sheet1));
  console.log(XLSX.utils.sheet_to_json(sheet2));
});

• In this modification, the module of request is used.

Result:

When this script is run, the following result is obtained.

[
  { Team: 'Manchester United' },
  { Team: 'PSG' },
  { Team: 'Barcelona' },
  { Team: 'Real Madrid' },
  { Team: 'Juventus' }
]
[
  { Name: 'Ronaldo', Age: 37 },
  { Name: 'Messi', Age: 34 },
  { Name: 'Neymar', Age: 30 }
]