google script - json into google sheet

Question

I'm new to scripting and I would appreciate your help. I do apologize for what might be a simple question.

What I have is an api that gives json:

[{"id":"xyz","name":"xy"}, {"id":"zyx","name":"yx"}]

The above continues lets say for another 100 ids.

What I want is to put that into the cells in google spreadsheet.

What I have currently is:

var ss = SpreadsheetApp.getActive();
var sh = ss.getActiveSheet();
var rr = sh.getRange(3,2,100,5);

var id = "www.xyz.com/api";
var jsonData = UrlFetchApp.fetch(id);
var jsonString = jsonData.getContentText();
var idsearch = JSON.parse(jsonString);

for (var i = 1; i < idsearch.count; i++)
  {
     var cellid = rr.getCell(i,1);
     eventid.setValue(idsearch.results[i].id);
     var cellname = rr.getCell(i,2);
     eventname.setValue(idsearch.results[i].name);
  }

When I run the script, nothing happens. It doesnt return anything.

Did I make a mistake in the script? Is there an easier way to put all the json result into google sheet? Is there an example where I can look at to learn about what I'm trying to do?

Thank you very much.

wf9a5m75 · Accepted Answer · 2013-07-31 23:48:30Z

14

I hope this simple code might help you.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var dataSet = [
    {"id":"xyz","name":"xy"},
    {"id":"zyx","name":"yx"}
  ]; 
  var rows = [],
      data;

  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([data.id, data.name]);
  }

  dataRange = sheet.getRange(1, 1, rows.length, 2);
  dataRange.setValues(rows);

}

enter image description here

answered Jul 31, 2013 at 23:48

wf9a5m75

6,1883 gold badges27 silver badges61 bronze badges

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

2 Comments

Az37 Over a year ago

This works, thank you. Now to figure out it all out :D Quick question, how does var rows = [], data; work? I tried searching.. then realized have no idea what I'm searching for. Thanks again for the help.

Parag Jadhav Over a year ago

Is this solution feasible for JSON which can have 12000-18000 ids, because as we know Google App Script have maximum execution time of 5 minutes.

Ian Weatherhogg · Accepted Answer · 2013-09-24 12:42:34Z

0

You might also want to inspect the example here (it's based on ScriptDb but will work with any JSON array). https://developers.google.com/apps-script/scriptdb#copy_a_database_to_a_new_sheet_in_a_spreadsheet

answered Sep 24, 2013 at 12:42

Ian Weatherhogg

1361 silver badge7 bronze badges

Collectives™ on Stack Overflow

google script - json into google sheet

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related