Download Google Sheets file and read data using SheetJS on Node

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!

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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 }
]

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating