Skip to content

Instantly share code, notes, and snippets.

@jonobr1
Last active April 26, 2024 15:41
Show Gist options
  • Save jonobr1/45fc5f41a219153aaa18 to your computer and use it in GitHub Desktop.
Save jonobr1/45fc5f41a219153aaa18 to your computer and use it in GitHub Desktop.
A node.js script to convert a Google Sheet into a JSON object
/**
* 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));
}
@sinumohammed
Copy link

sinumohammed commented Jun 27, 2020

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' }

@jonobr1
Copy link
Author

jonobr1 commented Jun 27, 2020

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.

@sinumohammed
Copy link

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);
  });

@jonobr1
Copy link
Author

jonobr1 commented Jul 5, 2020

Nice!

@SujataTechPro
Copy link

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;

@jonobr1
Copy link
Author

jonobr1 commented May 2, 2022

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment