"The coordinates or dimensions of the range are invalid"

Question

I am working on a Google Apps Script that links with a REST API and puts the data into a Google Sheet.

I have successfully done this once, but upon accessing some different data I get the error message

"The coordinates or dimensions of the range are invalid"

when they work perfectly fine on my other script. All data accessed is JSON so I am bit confused and is from the same source. The code I am using is:

function stats () {
  var logIn = {
    "Authorization" : "Basic " + Utilities.base64Encode("XXXX" + ':' + "XXXX")
  };

  var url = "XXXXX";
  var params = {
    "method":"GET",
    "headers":logIn, };

  var response = UrlFetchApp.fetch(url, params);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("XXXX");              

  var dataAll = JSON.parse(response.getContentText()); //
  var dataSet = dataAll;

  var rows = [],
      data;

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

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

I have combined pieces of code from around the web and this works on my other script. The error appears on this line:

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

I believe the issue is with the data I am accessing but I do not know how to alter the script for it to work.

The JSON data that works is shown like:

{
  id: XXX,
  group: XX,
  text: "XXXX?",
  creation_date: XXXX,
  created_by: "XXXXX",
  tags: [
    "XXXX"
  ]
}

And the data that is causing the error is shown as:

    {
      2016-02-29: {
      XXX: 0,
      XXX: 0
    },

I have had to 'XXXX' out a lot of the private information - apologies. Any help would be appreciated.

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

Javascript's length property is for indexed arrays and does not apply to Objects so dataSet.length returns undefined and the loop never executes.

To get the length of the object you can use Object.keys(dataSet).length as outlined here.

edited May 23 '17 at 11:59

Community

1
1

answered Mar 07 '16 at 13:01

Robin Gertenbach

10,316
3
25
37

Thank you. I gave this a go and substituted it in. Now it is finishing the script but everything returned is 'undefined' - any ideas? – JGA Mar 07 '16 at 13:31
What are you pushing in `rows.push([XXXX])`? Are you pushing the whole stringfied JSON object or a part of it? ie e `data.something`? – Robin Gertenbach Mar 07 '16 at 14:33
I am looking to get the amount of 'Read', if that's possible but data.(anything here) is not picking up the string. { 2016-02-29: { Read: 5, unread: 0 }, – JGA Mar 07 '16 at 14:38
Did you try reading it with `data["2016-02-29"]["Read"]`? Are you sure it's `Read` not `read` or `READ`? – Robin Gertenbach Mar 07 '16 at 14:45
Just tried that and get the following: TypeError: Cannot read property "2016-02-29" from undefined. Sorry yes its all lowercase as 'read' but tried that with the above to no avail. – JGA Mar 07 '16 at 14:50
So `dataSet` cannot be subscripted using numerical indices. Try calling `dataSet[Object.keys(dataSet)[i]]` instead. That'll iterate through the keys array, pull out the actual key and use that as a subscript of the object. – Robin Gertenbach Mar 07 '16 at 14:53
Unfortunately this returns the first error of: "The coordinates or dimensions of the range are invalid" Really appreciate the help on this - thank you. – JGA Mar 07 '16 at 15:05
So just to be sure: `dataAll` is an Object with each element being an an Object of the structure `{"yyyy-mm-dd":{"Read":0, "XXX":0}` Or is `dataAll` itself that object? – Robin Gertenbach Mar 07 '16 at 17:43
Yes dataAll is an Object with each element being an object of the structure {"yyyy-mm-dd":{"Read":0, "XXX":0} {"yyyy-mm-dd":{"Read":5, "XXX":5} {"yyyy-mm-dd":{"Read":2, "XXX":3} for example. – JGA Mar 08 '16 at 11:01
That's weird then as `Object.keys(dataAll)` should be an array of the dates, did you try debugging and seeing if it actually appends the data to rows or where else it has problems? – Robin Gertenbach Mar 08 '16 at 11:06
As it stands the range that gets entered into the spreadsheet is set up as: dataRange = sheet.getRange(1, 1, rows.length, 1); dataRange.setValues(rows); This brings the error mentioned. If I manually change it to: dataRange = sheet.getRange(1, 1, 1, 1); dataRange.setValues(rows); I get this error instead on the "dataRange.setValues(rows);" section: Incorrect range height, was 0 but should be 1 – JGA Mar 08 '16 at 11:48
Did you check in the debugger if you actually receive such an Object and that it is properly extracted from from response? – Robin Gertenbach Mar 08 '16 at 12:35
For dataAll and dataSet I get: ({'2016-02-29':{read:0, unread}, '2016-03-01':{read:0, unread:0}, '2016-03-02':{read:1, unread:0}, '2016-03-03':{read:1, unread:0}, Rows returns [] and data returns undefined – JGA Mar 08 '16 at 15:06
Is your loop `for (i = 0; i < Object.keys(dataSet).length; i++) { data = dataSet[Object.keys(dataSet)[i]]; rows.push([data.read]); }`? – Robin Gertenbach Mar 08 '16 at 16:10
Yes it is. The error seems all to do with the range section on these lines: dataRange = sheet.getRange(1, 1, rows.length, 1); dataRange.setValues(rows); With the error changing when I change the rows.length part of the above – JGA Mar 08 '16 at 16:21

"The coordinates or dimensions of the range are invalid"

1 Answers1

Linked