JSON from report

Comments

2 comments

  • Avatar
    Bruce McClenahan

    Hey Chris,

    Let me share a script function I wrote a while back to address this. 

    You replace the IMPORTHTML in the spreadsheet cell with IMPORTJSON

    IMPORTJSON("url","ReportView.tableInit",1)

    Regards
    Bruce

    ---

    function IMPORTJSON (url,tag,offset) {
    var response = UrlFetchApp.fetch(url, {method : "GET"});
    var html = response.getContentText();
    var json = html.match(new RegExp(tag+'\\((.*)\\);'));
    var object = JSON.parse(json[1]);
    var data = [];
    data.push(object.columns.map(function(cell) { return cell.name }));
    object.results.forEach(function(row) {data.push(row.cols.map(function(cell,col) {return (object.columns[col].format_type==1 //date type
    ?(cell.data ==''?'':new Date(cell.data))
    :cell.data)}))})

    return data;

  • Avatar
    Chris Derksen

    After adding a closing "}" that works perfectly! Thanks so much!!

Please sign in to leave a comment.