A node.js script to convert a Google Sheet into a JSON object

google-sheet-to-json.js

/**
 * Simple Node.js script to turn a specific page on a Google Sheet
 * into a JSON object for the main purpose of HTML Templating.
 * 
 * @author jonobr1 / http://jonobr1.com
 * 
 */

var https = require('https');
var path = require('path');
var fs = require('fs');

var format = 'tsv';         // Format you'd like to parse. `tsv` or `csv`
var id = 'GOOGLE_SHEET_ID'; // The Google Sheet ID found in the URL of your Google Sheet.
var sheetId = 0;            // The Page ID of the Sheet you'd like to export. Found as `gid` in the URL.

https.get('https://docs.google.com/spreadsheets/d/' + id + '/export?format=' + format + '&id=' + id + '&gid=' + sheetId, function(resp) {

  var body = '';

  resp
    .on('data', function(data) {

      body += ab2str(data);

    })
    .on('end', function() {

      var json = [];
      var rows = body.split(/\r\n/i);

      for (var i = 0; i < rows.length; i++) {
        json.push(rows[i].split(/\t/i));
      }

      fs.writeFileSync(path.resolve(__dirname, './sheet.json'), JSON.stringify(json));
      console.log('Generated sheet.json');

    });

});

function ab2str(buf) {
  return String.fromCharCode.apply(null, new Uint16Array(buf));
}

Hey,

This is working perfect earlier, now google changed their policy I guess, I am getting below error now with this code. I had taken public sheet to test.

{ 'content-type': 'text/html; charset=UTF-8',
  'cache-control': 'no-cache, no-store, max-age=0, must-revalidate',
  pragma: 'no-cache',
  expires: 'Mon, 01 Jan 1990 00:00:00 GMT',
  date: 'Sat, 27 Jun 2020 02:10:47 GMT',
  location:
   'https://doc-10-9o-sheets.googleusercontent.com/export/l5l039s6ni5uumqbsj9o11lmdc/9j0sjrnc4gcrhl72rbalgisj14/1593223845000/106181076377569944173/*/1LtpGuZdUivXEA4TqUvK9T3qRr1HER6TKzdSxTYPEAQ8?format=tsv&id=1LtpGuZdUivXEA4TqUvK9T3qRr1HER6TKzdSxTYPEAQ8&gid=0',
  p3p:
   **'CP="This is not a P3P policy! See g.co/p3phelp for more info."',**
  'content-security-policy':
   "base-uri 'self';object-src 'self';report-uri https://docs.google.com/spreadsheets/cspreport;script-src 'report-sample' 'nonce-M+v96suJ/VTj8ZyOUDbq7w' 'unsafe-inline' 'strict-dynamic' https: http: 'unsafe-eval';worker-src 'self'",
  'x-content-type-options': 'nosniff',
  'x-frame-options': 'SAMEORIGIN',
  'x-xss-protection': '1; mode=block',
  server: 'GSE',
  'set-cookie':
   [ 'NID=204=DB-_d9LiAayfBryQEPgWpcIj0JvS4gSYu6jcLc3sU37K9qG9zv7aRI3HisjqhxNs4dnduI404NT0W4qTBvpaf04tn9mQgCTWLlhB1t-6dI9aBRzovzisfzWsJRm12BarSxFfPOrgd3qBYzY3W2UChmNFEHtag4Bd0dJ3veffSqI; expires=Sun, 27-Dec-2020 02:10:47 GMT; path=/; domain=.google.com; HttpOnly',
     'S=apps-spreadsheets=1mVHb3SoeVMOiDonKRIky8l_xu70D76SK7JKK4Vjw5A; Domain=.docs.google.com; Expires=Sat, 27-Jun-2020 03:10:47 GMT; Path=/spreadsheets/d/1LtpGuZdUivXEA4TqUvK9T3qRr1HER6TKzdSxTYPEAQ8; Secure; HttpOnly; SameSite=none' ],
  'alt-svc':
   'h3-27=":443"; ma=2592000,h3-25=":443"; ma=2592000,h3-T050=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"',
  'accept-ranges': 'none',
  vary: 'Accept-Encoding',
  connection: 'close',
  'transfer-encoding': 'chunked' }

I'm not totally sure of this, but I believe the sheets API has changed and you need to oauth in now? (link)

So I don't think this code will work anymore.

I slightly change the implementation with google api key (now required for public sheet as well )
Google developer console - > library find sheets enable it.

  let url = "https://sheets.googleapis.com/v4/spreadsheets/" + id + "/values/" + sheetId + "?key=" + apiKey;
  https.get(url, (resp) => {
    let body = "";

    resp.on("data", (chunk) => {
      body += chunk;
    });

    resp.on("end", () => {
      try {
        let json = JSON.parse(body);
        // do something with JSON
      } catch (error) {
        console.error(error.message);
      };
    });

  }).on("error", (error) => {
    console.error(error.message);
  });

Nice!

getting this [["{\n "error": {\n "code": 400,\n "message": "Unable to parse range: 0",\n "status": "INVALID_ARGUMENT"\n }\n}\n"]]
when using this url
let url = "https://sheets.googleapis.com/v4/spreadsheets/" + id + "/values/" + sheetId + "?key=" + apiKey;

I could be wrong, but I believe the latest version of Google Sheets does not allow you to access spreadsheet content in this way anymore.