google apps script json parse error

Question

I've run into a small issue with my script.

I'm trying to fetch a json url and get the data from the json file into google spreadsheet. Previously I've used the =importData() function in Spreadsheet. However, I noticed the function is sometimes unreliable and returns #N/A. I want to create a script that I can schedule to run when I want it to.

There must be a mistake somewhere in the code? Can you guys help me? The Error I get is:

The coordinates or dimensions of the range are invalid. (line 25, file "Code")

Here's the link to the google spreadsheet file: https://docs.google.com/spreadsheets/d/15xQy8GDyHRMZXBPcnmSLYUl6GSHZLpTkfhvYR7-BjvQ/edit?usp=sharing

On Sheet3 is an example of what is looks like with the =importData() function. And also what I hope it would look like with this custom script.

function FetchUrl() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()

  var url = sheet[0].getRange(1, 2).getValue();
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var dataAll = JSON.parse(json);
  Logger.log(dataAll);
  var dataset = dataAll;

  var rows = [],
      data;

  for (i = 0; i < dataset.lenght; i++){
    data = dataset[i];
    rows.push([data.title, data.fields, data.values]);
  }
  dataRange = sheet[1].getRange(2, 1, rows.length, 3);
  dataRange.setValues(rows);

}

Logger.log output

[17-01-16 02:34:38:354 PST] {types=[1082, 20, 20, 20, 20, 20, 20, 20], type_names=[unknown, integer, integer, integer, integer, integer, integer, integer], values=[[2017-01-15, 3, 1, 3, 0, 2, 0, 0]], title=Course's all topics' starts yesterday: Get that job (en, -NG), fields=[date, findyourdreamjob, bethebestjobseeker, writethebestcv, findthebestjobs, getajobinterview, excelatjobinterviews, firstdaysonthejob]}

Thanks in advance!

Did you debug the script? Maybe it errors out for the same reason the builtin errors out (no data or similar) — Robin Gertenbach
couldn't find anything wrong with it... the data is available. example when builtin function returns #N/A, then I just download it manually from the same url and the data is perfectly fine. — Markus Häkli

David Tew David Tew · Accepted Answer · 2017-01-16T16:39:09

If you add the following line of code somewhere after you have declared 'var dataset =' Logger.log('dataset.length=' + dataset.length);

running and inspecting Logger.log shows that the dataset.length is undefined. (the output of the JSON.parse is an object, and so dataset is an object and not an array)

Thus furtherdown your script, the for loop will run zero times, since the length is undefined.

so your row output remains the empty array [] and hence your error message (I suppose, but I can't be sure this is the only reason).

BTW, you have spelt length incorrectly. But this does not give an error since the loop is skipped (again, I suppose).

The following script give the result as per sheet 3, for your comparison.

function FetchUrl2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()

  var url = sheet[0].getRange(1, 2).getValue();
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var dataAll = JSON.parse(json);
  Logger.log(dataAll);  // inspecting through Logger.log shows dataAll is an object not an array.  So, in the lines below, the headers and values are extracted from this object and recreated into an array.

  var headers = dataAll.fields; 
  var values = dataAll.values[0]; 
  var output = [headers,values];
  Logger.log(output)
  var dataRange = sheet[1].getRange(2,1,output.length, output[0].length);
  dataRange.setValues(output);
}

google apps script json parse error

1 Answers