gsheet-to-json.js

/* * * *

parse published google sheet json feed
return array of row objects with column header keys 

requirements:
- first row must be of column headers
- publish google sheet to the web prior to running script
  - File > Publish to the web
  - Select Entire document
  - Select CSV as format

* * * */

const gsheetID = 'sample-id-string'; // google sheet id from edit url
const gsheetSheetNum = '1'; // spreadsheet sheet number
const gSheetURL = `https://spreadsheets.google.com/feeds/cells/${gsheetID}/${gsheetSheetNum}/public/full?alt=json`

function parseGoogleSheetsJSONFeed(data) {
  const sheet = {};
  let rows = [];

  sheet.rows = data.feed.entry;
  sheet.cols = sheet.rows // array of column headers
      .filter( d => d.gs$cell.row == 1)
      .map( d => d.gs$cell.$t );
  // sheet extent, integer number of rows
  sheet.numRows = Number(sheet.rows[sheet.rows.length - 1].gs$cell.row);

  for (let i = 2; i <= sheet.numRows; i++) {
    let row = {};
    let cells = sheet.rows
        .filter(d => d.gs$cell.row == i)
        .map(d => d.gs$cell);
    
    sheet.cols.forEach( (d,i) => {
      row[d] = cells
          .filter(d => Number(d.col) == i + 1)
          .map(d => d.$t)[0]
    })

    rows.push(row);
  }

  return rows;
}