Incorrect Range Error when writing to cells in google sheets

Question

I'm using a gfx as a button and I want it to set the values from the array to a set of rows and columns. So far this is what I have.

function loadTrades(){
  var balancesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Balances");
  var exchange = balancesSheet.getRange("D1").getValue();
  var trades = Gettrades(exchange);
  var arr = [];
  var c = [];
  for (var i=0;i < trades.length-1;i++) {
    c=[];
    for (var j=0; j< trades[0].length;j++){
      c.push(trades[i][j]);
    }
    arr.push(c);
  }
  //var arr = json2array(trades); this does the same as above but in a funtion of its own.
  var destinationRange = balancesSheet.getRange("D2");
  destinationRange.setValues(arr);
  };

function json2array(data){
  var results = [];
  var keys = [];
  var values = [];
  for (var i in data){
    for (var Key in data[i]){
      if (i == 0) keys.push(Key);
      values.push(data[i][Key]);
    }
    if (i == 0){
      results.push(keys);
      keys = [];
    }
    results.push(values);
    values = [];
  }
  return results;
};

There is up to 500 rows in the array, and 7 columns.
I get the error.

Incorrect range height, was 3 but should be 1.

I think I have a miss understanding of how getRange and setValues work in google sheets.

I've read through this site and a few other examples here on stackoverflow, but can't find my solution.

getTrades(exchanges) grabs a json from a site. I have succesfully posted the json, and put in to to an array, if I just return the values I can't edit them or use them in a formula. So I'm trying to set values for each cell. — Trader Brader
Currently the trades var ireturns [{ID:4014360, Price:"0.01608900", Quanity:"1.00000000", Commision:"0.00100000", 'Commission Asset':"0.00100000", 'Unix TimeStamp':1515126621764, Buy:true, Sell:false}] — Trader Brader

Rubén Rubén · Accepted Answer · 2018-01-08T00:45:28

As the error occurs on

destinationRange.setValues(arr);

the origin of the problem is on the previous line

var destinationRange = balancesSheet.getRange("D2");

The above because balancesSheet.getRange("D2") returns a single cell but arr, according to the error, has a height of 3.

One way to solve this is replace

var destinationRange = balancesSheet.getRange("D2");

by

var destinationRange = balancesSheet.getRange(2,4,arr.length,arr[0].length);

Reference

getRange(row, column, numRows, numColumns)

Incorrect Range Error when writing to cells in google sheets

1 Answers