Google Spreadsheet JSON Formatter

google-spreadsheet-json-formater.ts

export class SpreadSheetFormater {
  table: {};
  constructor(jsonpResponse: string) {
    if (!jsonpResponse) {
      throw Error(`SpreadSheet JSON data is invalid. Got "${jsonpResponse}".`);
    }
    this.table = this.format(this.convertToJson(jsonpResponse).table);
  }
  private convertToJson(text: string) {
    const value = text.replace('google.visualization.Query.setResponse', 'return this.google');
    const fn = new Function(value);
    const ctx = {
      google(val) {
        return val;
      }
    };
    return fn.call(ctx);
  }
  
  private format(table: { cols; rows }) {
    const mat = {};
    table.cols.forEach(col => {
      mat[col.id] = [];
    });
    // tslint:disable-next-line:prefer-for-of
    for (let row = 0; row < table.rows.length; row++) {
      Object.keys(mat).forEach((v, i) => {
        const c = table.rows[row].c[i];
        mat[v].push(c && c.v);
      });
    }
    return mat;
  }

  cell(cellAndRow: string) {
    const [c, r] = cellAndRow.split(':');
    const row = parseInt(r, 10) + 1;
    return this.table[c] && this.table[c][row] ? this.table[c][row] : null;
  }
}

const jsonp = `/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1375483220","table":{"cols":[{"id":"A","label":"","type":"string"},{"id":"B","label":"","type":"string"},{"id":"C","label":"","type":"string"}],"rows":[{"c":[null,{"v":"foobar"},{"v":"this is a value"}]}]}});
`;

const sheet = new SpreadSheetFormater(jsonp);
sheet.cell('A:1');