fetchGoogleSheet

readme.md

Just run it in console

npx https://gist.github.com/Akiyamka/cb58b704ee1d162a67ac7a08e5027b19

googleSheetJSON.js

const https = require('https');
const { StringDecoder } = require('string_decoder');

/**
 * 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
 * @author akiyamka / [email protected]
 *
 */

class GoogleSheetJSON {
  constructor({ id, format, page }) {
    this.sheetId = id ;
    this.format = format || 'tsv';
    this.page = page || 0;
    this.data = null;
  }

  get url() {
    return `https://docs.google.com/spreadsheets/d/${
      this.sheetId
    }/export?format=${
      this.format
    }&id=${
      this.sheetId
    }&gid=${
      this.page
    }`;
  }

  _fixType(value) {
    if (typeof value !== 'string') return value;
    if (value.toLowerCase() === 'true') return true;
    if (value.toLowerCase() === 'false') return false;

    const maybeNumber = Number(value);
    if (!Number.isNaN(maybeNumber)) return maybeNumber;

    return value;
  }

  load() {
    return new Promise(res => {
      https.get(this.url, resp => {
        const decoder = new StringDecoder('utf8');
        let buff = '';
        resp
            .on('data', data => {
                buff += decoder.write(data);
            })
            .on('end', () => {
                const json = [];
                const rows = buff.split(/\r\n/i);
                for (let i = 0; i < rows.length; i++) {
                  json.push(rows[i].split(/\t/i));
                }

                this.data = json;
                res(json);
            });
      });
    });
  }

  convert() {
    const model = this.data[0];
    return this.data.slice(1).map(d => {
      const obj = {};
      model.forEach((field, i) => obj[field] = this._fixType(d[i]))
      return obj
    });
  }
}

module.exports = GoogleSheetJSON;

index.js

#!/usr/bin/env node

const GoogleSheetJSON = require('./googleSheetJSON');
const jsonfile = require('jsonfile');
const mkdirp = require('mkdirp');

async function dawnloadData() {
  const questionsSheet = new GoogleSheetJSON({ id: '1bBruVm9vsktmfsz_OxcBUdPyq110nCHykCaCa5K627M', page: 0 });
  const questions = questionsSheet.load().then(res => questionsSheet.convert(), err => console.error(err));

  const tasksSheet = new GoogleSheetJSON({ id: '1bBruVm9vsktmfsz_OxcBUdPyq110nCHykCaCa5K627M', page: 1379254750 });
  const tasks = tasksSheet.load().then(res => tasksSheet.convert(), err => console.error(err));

  const invariantsSheet = new GoogleSheetJSON({ id: '1bBruVm9vsktmfsz_OxcBUdPyq110nCHykCaCa5K627M', page: 254257973 });
  const invariants = invariantsSheet.load().then(res => invariantsSheet.convert(), err => console.error(err));
  const reducedInvariants = await invariants;

  return await {
    questions: await questions,
    tasks: await tasks,
    invariants: reducedInvariants.reduce((acc, inv) => {
      Object.entries(inv).forEach(([key, val]) => {
        if (!Array.isArray(acc[key])) acc[key] = [acc[key]];
        acc[key].push(val);
      });
      return acc;
    }),
  }
}

function saveData(data, dir) {
  mkdirp(dir, () => {
    jsonfile.writeFile(`${dir}/data.json`, data, { spaces: 2 }, err => {
      if (err) console.error(err);
    });
  });
}

dawnloadData().then(data => saveData(data, './src/'));

package.json

{
  "name": "fetch-google-sheet",
  "version": "0.0.1",
  "bin": "./index.js",
  "dependencies": {
    "jsonfile": "^5.0.0",
    "mkdirp": "^0.5.1"
  }
}