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

I am trying to copy a munication 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="/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.

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

I am trying to copy a munication 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.brandmeisterwork/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.

• javascript
• json
• parsing
• google-apps-script
• google-sheets

• 1 Error implies you are getting an HTML in response.getContentText() which cannot be JSON parsed and hence throws the error while trying to parse. – Boney Commented Jun 14, 2019 at 9:11
• So even if the url says it is a JSON query does not mean that it can be parsed? Trying to learn, not being sarcastic or funny. – Jae Nulton Commented Jun 14, 2019 at 9:21
• 1 No problem. Just try launching that URL in a browser. You can see the HTML page getting rendered which means response is in HTML. If at all JSON is to be returned for JSON query, it needs to be done at the server which is not happening now. – Boney Commented Jun 14, 2019 at 9:26
• The spreadsheet is blank, do I have to fill in all the headers maybe? – Jae Nulton Commented Jun 14, 2019 at 11:39

1 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.brandmeisterwork/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.brandmeisterwork/v1.0/repeater/?action=get&q=1148311.

When the values retrieved from the URL of https://api.brandmeisterwork/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.brandmeisterwork/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.brandmeisterwork/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 bees [].

  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.