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:
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!
In your situation, how about the following modification?
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));
});
request is used.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 }
]