I'm trying to render json data to a google sheet using javascript.
I have tried searching through loads of documentation and I've been trying for hours. I do get one line of data come into my sheet but I need to render the whole json dataset in the correct rows and columns
function renderJSON() {
/* This function collects JSON data from a specified endpoint
and renders the data to a spreadsheet */
// Fetch JSON data from the endpoint
var url = 'http://dummy.restapiexample.com/api/v1/employees';
var response = UrlFetchApp.fetch(url); // Get the JSON data
var object = JSON.parse(response.getContentText());
//Logger.log(response);
// Define an array of all object keys
var rows = []; // Declare an empty array
rows.push(object);
var headerRow = Object.keys(rows);
// Define an array of all object values
var rows = headerRow.map(function(key){return rows [key]});
// Define the contents of the range
var contents = [
headerRow,
rows
];
// Select the range and set its values
var ss = SpreadsheetApp.getActive();
var rng = ss.getActiveSheet().getRange(1, 1, contents.length,
headerRow.length)
var i;
for (i = contents[0]; i < contents.length; i++) {
rng.setValues([i]);
}
}
I expect the whole of the json data to be in perfect format for a google sheet with the correct heading and everything aligned in the correct columns. Currently there is no output to the sheet.
How about this modification? In this modification, at first, the header values are retrieved from the 1st element. And the values are retrieved from the headers. Then, the values which added the headers are put to the active sheet.
function renderJSON() {
var url = 'http://dummy.restapiexample.com/api/v1/employees';
var response = UrlFetchApp.fetch(url);
var object = JSON.parse(response.getContentText());
var headers = Object.keys(object[0]);
var values = object.map(function(e) {return headers.map(function(f) {return e[f]})});
values.unshift(headers);
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
If I misunderstood your question and this was not the result you want, I apologize.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments