Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Apps Script to read JSON and write to sheet
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;
}
@valhuber

This comment has been minimized.

Copy link

valhuber commented Sep 30, 2013

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?

@mhawksey

This comment has been minimized.

Copy link
Owner Author

mhawksey commented Oct 1, 2013

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

@hhietanen

This comment has been minimized.

Copy link

hhietanen commented Jan 17, 2014

Google has depreciated the jsonParse-method. It has been replaced by JSON.parse. I replaced teh

I am having problems running the code. I get errors for both getRange (rows 18 and 61) "The coordinates or dimensions are incorrect."

@varun-raj

This comment has been minimized.

Copy link

varun-raj commented May 12, 2014

I've coded a simple snippet to achieve the same ! Check this
https://gist.github.com/varun-raj/5350595a730a62ca1954

@andrewgl504

This comment has been minimized.

Copy link

andrewgl504 commented Jun 18, 2015

The error reported by hhietanen appears to be due to not having headers in the sheet. The code, as written, requires some type of row header.

Now I'm getting the error: TypeError: Cannot read property "items" from undefined.
It appears that it's this line causing the issue: var data = dataAll.value.items;

Any ideas on how to fix that?

@samvignoli

This comment has been minimized.

Copy link

samvignoli commented Jul 15, 2015

This is not working.

@wladan

This comment has been minimized.

Copy link

wladan commented Aug 29, 2016

What if access to .json file needs credentials?

Thank you

@mohan-mishra

This comment has been minimized.

Copy link

mohan-mishra commented Sep 6, 2016

This is not working. Getting invalid coordinates in getRange function

@esaruoho

This comment has been minimized.

Copy link

esaruoho commented Jul 24, 2017

Query - I already do UrlFetchApp.fetch(url) with a function but i'm not entirely sure dump the whole code into. Can I just replace the aUrl with my own url and hope for the best?

@marcelo-tibau

This comment has been minimized.

Copy link

marcelo-tibau commented Nov 9, 2017

I would fetch the data, put it into a json, create a list and push to the spreadsheet. Something like this:

var response = UrlFetchApp.fetch('https://YOURSITEHERE.com');
var json = response.getContentText();
var rawdata = JSON.parse(json);
var rawdata = JSON.parse(response.getContentText());
Logger.log(rawdata);
var data = [];
data.push(rawdata);
SpreadsheetApp.getActiveSheet().appendRow(data)

@mlb6

This comment has been minimized.

Copy link

mlb6 commented Apr 19, 2018

The workaround to avoid the error "The coordinates or dimensions are incorrect." is to create the sheet manually and to insert the headers. If your column is "postalCode", the header should be set as "Postal Code"

@SIFAR786

This comment has been minimized.

Copy link

SIFAR786 commented Jun 24, 2018

@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"
}
]
}

@rbettendorf

This comment has been minimized.

Copy link

rbettendorf commented Feb 15, 2019

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.

@hi0001234d

This comment has been minimized.

Copy link

hi0001234d commented Aug 25, 2019

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.