How do I fetch and parse JSON data to Google Sheets Script?

Question

EDIT*** Modified the question now that I have a proper source...

I am trying to copy a communication log to Google Sheets and don't know enough about scripting ( or JSON for that matter) to pull this off. I just want to the spreadsheet to emulate/copy the entire log that is available.

The script run and there are no errors, but nothing goes onto the spreadsheet. The code snippet I am trying to use is here:

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


var url="https://api.brandmeister.network/v1.0/repeater/?action=get&q=1148311"; // Paste your JSON URL here

var response = UrlFetchApp.fetch(url); // get feed
var dataAll = JSON.parse(response.getContentText()); //
var dataSet = dataAll;


var rows = [],
  data;


for (i = 0; i < dataSet.length; i++) {
data = dataSet[i];
rows.push([data.repeaterid, data.callsign]); //your JSON entities here
}

 data = sheet.getRange(1,1,99,10).getValues(); 
 sheet.getRange(1,1,99,10).setValues(data);

}

I would like to know how to do this, as well as an educational explanation as to what this error is and why I am getting it, I am trying to learn not just get help.

The spreadsheet is a blank canvas, so if I need to do something special to it for the script to work, could that please be explained as well? Thank you all.

Answer 1

• You want to know the reason about The script run and there are no errors, but nothing goes onto the spreadsheet..
• From your script, you want to put the values of repeaterid and callsign from the fetched data from the URL of https://api.brandmeister.network/v1.0/repeater/?action=get&q=1148311.

If my understanding is correct, how about this answers?

Answer 1:

In this section, the reason about The script run and there are no errors, but nothing goes onto the spreadsheet. is explained.

When I see your script, the following script is seen at the bottom of your script.

data = sheet.getRange(1,1,99,10).getValues();
sheet.getRange(1,1,99,10).setValues(data);

This means that the data retrieved by getRange(1,1,99,10) is put the same range. Namely, the range is overwritten by the same values. From The spreadsheet is a blank canvas of your question, the empty values of the range are put to the same range. And the script of other part occurs no error. This is the reason of The script run and there are no errors, but nothing goes onto the spreadsheet..

And also, in your script, rows is not used. By this, even if rows has values you want to put, the values are not put to Spreadsheet.

Answer 2:

In this section, I modified your script to put the values of repeaterid and callsign from the fetched data from the URL of https://api.brandmeister.network/v1.0/repeater/?action=get&q=1148311.

When the values retrieved from the URL of https://api.brandmeister.network/v1.0/repeater/?action=get&q=1148311 is as follows.

{
  "repeaterid": "1148311",
  "callsign": "KD8YYA",
  "hardware": "Android:BlueDV",
  "firmware": "1.0.121-DVMEGA_HR3.07",
  "tx": "437.0000",
  "rx": "437.0000",
  "colorcode": "1",
  "status": "4",
  "lastKnownMaster": "3108",
  "lat": "0.000000",
  "lng": "0.000000",
  "city": "Somewhere",
  "website": "www.pa7lim.nl",
  "pep": null,
  "gain": null,
  "agl": "1",
  "priorityDescription": null,
  "description": null,
  "last_updated": "2019-06-14 15:46:09",
  "sysops": []
}

From your script of rows.push([data.repeaterid, data.callsign]), I could understand that you might want to retrieve the values of repeaterid and callsign, and want to put the values to the Spreadsheet. In order to retrieve them, please modify your script as follows.

Modified script 1:

function pullJSON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();
  var url="https://api.brandmeister.network/v1.0/repeater/?action=get&q=1148311"; // Paste your JSON URL here
  var response = UrlFetchApp.fetch(url); // get feed
  var dataAll = JSON.parse(response.getContentText());

  // I modified below.
  var row = [dataAll.repeaterid, dataAll.callsign]; // Retrieve values from JSON object of dataAll.
  sheet.appendRow(row); // Append the values to Spreadsheet.
}

• By this modified script, the retrieved of repeaterid and callsign are put to the active sheet of Spreadsheet.

Modified script 2:

function pullJSON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();
  var url="https://api.brandmeister.network/v1.0/repeater/?action=get&q=1148311"; // Paste your JSON URL here
  var response = UrlFetchApp.fetch(url); // get feed
  var dataAll = JSON.parse(response.getContentText());

  // I modified below.
  var rows = [Object.keys(dataAll)]; // Retrieve headers.
  var temp = [];
  for (var i = 0; i < rows[0].length; i++) {
    temp.push(dataAll[rows[0][i]]); // Retrieve values.
  }
  rows.push(temp);
  sheet.getRange(1,1,rows.length,rows[0].length).setValues(rows); // Put values to Spreadsheet.
}

• By this modified script, all keys and values are put to Spreadsheet.

Note:

• In this case, dataAll is not an array. So your below script, the for loop doesn't work. By this, rows becomes [].

  var dataSet = dataAll;
  var rows = [], data;
  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([data.repeaterid, data.callsign]); //your JSON entities here
  }
  

• From your question, I'm not sure about the situation, which the values are put to Spreadsheet, you want. If you want to modify the output format, please modify above script.

References:

• getValues()
• setValues(values)
• appendRow(rowContents)

If I misunderstood your question and this was not the direction you want, I apologize.