CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
mcansado
mcansado

Reputation: 2164

Writing 1D array into a sheet column in Apps Script

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.

Upvotes: 7

Views: 9985

Answers (3)

Oscar Manrique
Oscar Manrique

Reputation: 29

As an alternative, make sure when creating the array to push values using "[]" around the value or variable you are inserting into the array, like this:

array.push(["M"];
range.setValues(array);

This will create a 2d array that can be properly inserted using setValues.

Upvotes: 0

JPV
JPV

Reputation: 27292

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)
}

Upvotes: 4

Alan Wells
Alan Wells

Reputation: 31310

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.

Upvotes: 1

Related Questions