Skip to content
Advertisement

How to push 1 D array to a column

I’m trying to push 1D array to a column but Im keep getting that error “Exception: The parameters (number[]) don’t match the method signature for SpreadsheetApp.Range.setValues.” which what I understood is that I cant use setValues for 1D array, it has to be 2D array or more.

Here is my code.

  var range = ss.getRange(3,5,ss.getLastRow()-2).getValues();
  var qValues = [];
  for( var i=0; i<range.length; i++ ) {
    qValues.push([range[i][0]]); // index 0 = column E.
  }

  var values = ss.getRange(3,14,ss.getLastRow()-2,11).getValues(); // N3:T??
  var results = [];
  for(var i=0; i<values.length; i++ ) {
    results.push((([values[i][0]+values[i][2]+values[i][4]+values[i][6]+values[i][8]+values[i][10]])*120*qValues[i][0])/1000); // index 0 = column N, etc.
  }

   ss.getRange(3, 27,).setValues(results);

I tried to add

results.push([""]);

before the setValues statement but it didn’t work as well

If you need more info please let me know.

Thank you

Advertisement

Answer

Basically you need to convert your 1D array into 2D array to put it into a column. It can be done this way:

var arr = [1, 2, 3, 4];
var arr_2d = arr.map(x => [x]);

console.log(arr_2d); // > [[1],[2],[3],[4]]

For your case the last line could be something like this:

ss.getRange(3, 27, results.length, results[0].length).setValues(results.map(x => [x]));

But I don’t know if your array is a valid array and what the range you want to fill. Probably in your case the array is not array at all.

Could you show the contents of the array results?

console.log(results)

Because this operation looks extremely weird:

([ values[i][0] + 
   values[i][2] + 
   values[i][4] + 
   values[i][6] + 
   values[i][8] + 
   values[i][10] ]) * 120 * qValues[i][0]

I frankly don’t understand what you’re trying to gain.

Perhaps there shouldn’t be the brackets [...]:

( values[i][0] + 
  values[i][2] + 
  values[i][4] + 
  values[i][6] + 
  values[i][8] + 
  values[i][10] ) * 120 * qValues[i][0]
Advertisement