How to push 1 D array to a column-CodePudding

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

CodePudding user response：

Flat array to column

function flatArraytoColumn() {
  let a = [...Array.from(new Array(10).keys(), x => [x   1])];//second term of array.from returns each element in an array
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet()
  sh.getRange(1, 1, a.length, 1).setValues(a);//Post into column
  Logger.log(a);//log array
}

Execution log
1:07:57 PM  Notice  Execution started
1:07:58 PM  Info    [[1.0], [2.0], [3.0], [4.0], [5.0], [6.0], [7.0], [8.0], [9.0], [10.0]]
1:07:59 PM  Notice  Execution completed

Active Sheet:

CodePudding user response：

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]