Google Apps Script to read JSON and write to sheet

gistfile1.js

function getJSON(aUrl,sheetname) {
  //var sheetname = "test";
  //var aUrl = "http://pipes.yahoo.com/pipes/pipe.run?_id=286bbb1d8d30f65b54173b3b752fa4d9&_render=json";
  var response = UrlFetchApp.fetch(aUrl); // get feed
  var dataAll = JSON.parse(response.getContentText()); //
  var data = dataAll.value.items;
  for (i in data){
    data[i].pubDate = new Date(data[i].pubDate);
    data[i].start = data[i].pubDate;
  }

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var temp = doc.getSheetByName("TMP");  
  if (!doc.getSheetByName(sheetname)){
    var sheet = doc.insertSheet(sheetname, {template:temp});
  } else {
    var sheet = doc.getSheetByName(sheetname);
    sheet.getRange(2, 1, sheet.getLastRow(), sheet.getMaxColumns()).clear({contentsOnly:true});
  } 
  insertData(sheet,data);
}

function insertData(sheet, data){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (data.length>0){
    ss.toast("Inserting "+data.length+" rows");
    sheet.insertRowsAfter(1, data.length);
    setRowsData(sheet, data);
  } else {
    ss.toast("All done");
  }  
}



// Back to the stuff from Google -->

// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
//   - sheet: the Sheet Object where the data will be written
//   - objects: an Array of Objects, each of which contains data for a row
//   - optHeadersRange: a Range of cells where the column headers are defined. This
//     defaults to the entire first row in sheet.
//   - optFirstDataRowIndex: index of the first row where data should be written. This
//     defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
  var headers = normalizeHeaders(headersRange.getValues()[0]);

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
    }
    data.push(values);
  }
  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), 
                                        objects.length, headers.length);
  destinationRange.setValues(data);
}

// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}

// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

// Returns an Array of normalized Strings.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    //if (!isAlnum(letter)) {
    //  continue;
    //}
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}
// http://jsfromhell.com/array/chunk
function chunk(a, s){
    for(var x, i = 0, c = -1, l = a.length, n = []; i < l; i++)
        (x = i % s) ? n[c][x] = a[i] : n[++c] = [a[i]];
    return n;
}

@mhawksey, how do i parse a JSON like this? Your code doesn't work with this JSON. Can you help urgently?

{
"result":
[
{
"sg_event_id": "92-OndRfTs6fZjNdHWzLBw",
"timestamp": 1529618395,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:59:55.000Z",
"created_at": "2018-06-21T22:00:28.532Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 38,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "no.name@noname.com"
}, {
"sg_event_id": "bjMlfsSfRyuXEVy8LndsYA",
"timestamp": 1529618349,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:59:09.000Z",
"created_at": "2018-06-21T21:59:39.491Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 36,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "no.name@noname.com"
}, {
"sg_event_id": "fru_s2s1RtueuqBMNoIoTg",
"timestamp": 1529618255,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:57:35.000Z",
"created_at": "2018-06-21T21:58:20.374Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 29,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "no.name@noname.com"
}
]
}

Just like one of the above comments, also getting the error: TypeError: Cannot read property "items" from undefined.
Due to this line: var data = dataAll.value.items

Manually adding headers to my sheet did not solve the issue.

Very cool. I can run the script from the menu, but I get this error when I run from cell contents:

  error: You do not have permission to call insertRowsAfter (line 27, file "getJSON")

I researched this error here https://developers.google.com/apps-script/execution_custom_functions#permissions, and there does not appear to be a solution - anybody know of one?

hi - the script needs a bump to trigger authorisation to write to the sheet. In the Tools > Script Editor try to Run > getJSON This should trigger the Google internal authentication processes. Once it is done the custom formula should then work.

This gist was a bit of a hack job used here http://mashe.hawksey.info/2011/12/oer-visualisation-project-processing-a-resource-feed-to-find-frequency-using-google-spreadsheets-day-3-ukoer-ooher/ so could do with a tidy up

Awsome!

I got an error running this, found this working alternative however https://sheet2api.com/google-sheet-to-json-api/