Writing 1D array into a sheet column in Apps Script

Question

I'm trying to write a long 1D array into a column in a sheet using Apps Script but can't make it work. Have tried setValues() but it required a 2D array.

function writeArrayToColumn() {
var mainSheet = SpreadsheetApp.getActiveSheet()
var array = ["M", "C", "D", "F", "R", "S", "Q", "V", "G"]
var range = mainSheet.getRange(2, 1, array.length, 1)
range.setValue(array)
}

This writes only "M" in first 9 cells not "M" in 1, "C" in 2, so on and so forth. If I use setValues() I get an error:

Cannot convert Array to Object[][].

There are similar questions on Stack Overflow but none could answer this.

Answer 1

In addition to Sandy's explanation, adding in a map() function should also work.

function writeArrayToColumn() {
var mainSheet = SpreadsheetApp.getActiveSheet()
var array = ["M", "C", "D", "F", "R", "S", "Q", "V", "G"]
    .map(function (el) {
        return [el];
    });
var range = mainSheet.getRange(2, 1, array.length, 1)
range.setValues(array)
}

Answer 2

I think you want to write new values to each row. So this is the code you need:

function writeArrayToColumn() {
  var mainSheet = SpreadsheetApp.getActiveSheet();
  var array = ["M", "C", "D", "F", "R", "S", "Q", "V", "G"];
  var outerArray = [],
      tempArray = [],
      i=0;

  for (i=0;i<array.length;i+=1) {
    tempArray = [];
    tempArray.push(array[i]);
    outerArray.push(tempArray);
  };

  var range = mainSheet.getRange(2, 1, array.length, 1);

  range.setValues(outerArray);
};

If you want to write the array to one row, where each array element will go into a new cell, create an outer array, and push the inner array into the outer array:

function writeArrayToColumn() {
  var mainSheet = SpreadsheetApp.getActiveSheet();
  var array = ["M", "C", "D", "F", "R", "S", "Q", "V", "G"];
  var range = mainSheet.getRange(2, 1, array.length, 1);
  var outerArray = [];
  outerArray.push(array);

  range.setValues(outerArray);
};

If you want to put each array element into a new row, that would be different. You'd need to loop through the array, and create a new array for every element.