Write an array of values to a range of cells in a spreadsheet

user_1432507 0
Viewed 125404

Using Google Apps Script, is there a way to write the values in a Google Spreadsheet array to a range without looping?

I am thinking something like the following to put one name each into cells A1:A3

function demoWriteFromArray() {

   var employees=["Adam","Barb","Chris"];

   ssActive = SpreadsheetApp.getActiveSheet();

   rgMyRange = ssActive.getRange("A1:A3");

   rgMyRange.setValue(employees)
}

Problem with above is that after execution, A1:A3 all contain ={"Adam","Barb","Chris"} and display "Adam".

google-apps-script google-sheets arrays
9 Answers

The top answer provides a nice, compact way of writing an array to a ROW but to write to a COLUMN it is a bit harder but since that's the question here, here's a quick function to do it:

function addArrayToSheetColumn(sheet, column, values) {
  const range = [column, "1:", column, values.length].join("");
  const fn = function(v) {
    return [ v ];
  };
  sheet.getRange(range).setValues(values.map(fn));
}

Where you'd then call the function in your code like so:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const cache = ss.getSheetByName("_cache_");
const results = ["Adam","Barb","Chris"];
addArrayToSheetColumn(cache, "A", results);
user_13150530

Even though it's a bit late, someone might come across this answer at some point. I wrote a function that coerces a 1-d Array into a 2-d Array (matrix), which is what is expected as a parameter to Range.setValues():

// Morphs a 1-d array into a 2-d array for use with Range.setValues([][])
function morphIntoMatrix(array) {

  // Create a new array and set the first row of that array to be the original array
  // This is a sloppy workaround to "morphing" a 1-d array into a 2-d array
  var matrix = new Array();
  matrix[0] = array;

  // "Sanitize" the array by erasing null/"null" values with an empty string ""
  for (var i = 0; i < matrix.length; i ++) {
    for (var j = 0; j < matrix[i].length; j ++) {
      if (matrix[i][j] == null || matrix[i][j] == "null") {
        matrix[i][j] = "";
      }
    }
  }
  return matrix;
}

I can't guarantee that any values except basic data types will be preserved. The sanitization is necessary because JavaScript internally seems to replace empty values in the original Array with null or "null" when it is assigned to be the element of another Array. At least, that's my experience. It works though, someone may find it useful.

user_19868710
user_128278330

you can use push

var employees=["Adam","Barb","Chris"];
var newarray = [[],[]] ;
newarray[0].push(employees) ;
ssActive = SpreadsheetApp.getActiveSheet();
rgMyRange = ssActive.getRange("A1:A3");
rgMyRange.setValues(newarray)

notice that you need to change setValue to setValues

user_30033950 
var employeesRow = ["Adam","Barb","Chris"];    
var employeesCol = employeesRow.map(x => [x]); //where x is your item/element in the array
Logger.log(employeesCol); //expected result [["Adam"],["Barb"],["Chris"]]
user_175737440
Related