How to write json data to a google sheet using javascript

Question

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.

Have you verified that contents contains what you expect it to? — Ann L.
– Ann L., Commented Sep 27, 2019 at 22:30
Are you getting error messages? Or is it completing without error, but it doesn't look like you expect? — Ann L.
– Ann L., Commented Sep 27, 2019 at 22:30
The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 2. — j86h
– j86h, Commented Sep 27, 2019 at 22:35
I think I may see the problem. contents has only 2 items in it: the header, and the rows, with the rows being an object in their own right. So by iterating through the contents of contents, you're putting in the header row -- and then a blob of JSON representing all the other rows. — Ann L.
– Ann L., Commented Sep 28, 2019 at 0:30

Tanaike · Accepted Answer · 2019-09-28 00:27:45Z

6

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.

Modified script:

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.

answered Sep 28, 2019 at 0:27

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

j86h Over a year ago

Thank you, that worked with no further modifications :)

Tanaike Over a year ago

@j86h Thank you for replying. I'm glad your issue was resolved.

Liquidice Over a year ago

great code chunk - works like a charm

Tanaike Over a year ago

@Liquidice Thank you for checking it. I'm glad about it.

Collectives™ on Stack Overflow

How to write json data to a google sheet using javascript

1 Answer 1

Modified script:

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related