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

arrays google-apps-script google-sheets
Playing with GAS picture Playing with GAS · Jul 15, 2012 · Viewed 93.4k times · Source

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".

Answer

Eric Koleda picture Eric Koleda · Jul 19, 2012

Range.setValue() is used for setting the same value in every cell of the range, while setValues is used to set an array of values into the corresponding cells in the range. Be aware that this method expects a multi-dimensional array, with the outer array being rows and the inner array being columns. So in your case the data should look like:

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

Related questions

How to union ranges in google spreadsheets

I want to union ranges from any Google spreadsheets. The example Sheet1!A:A {12, 131, 45} Sheet2!A:A {12, 131, 46} The unknown function =formula_for_union_range(Sheet1!A:A; Sheet2!:A:A) should return {12, 131, 45, 12, 131, 46} The question How is it possible?

Read More
indexOf returning -1 despite object being in the array - Javascript in Google Spreadsheet Scripts

I am writing a script for a Google Docs Spreadsheet to read a list of directors and add them to an array if they do not already appear within it. However, I cannot seem to get indexOf to return anything …

Read More
Creating hash array in Google Apps Script

I've been trying to work with Trello and the Google Apps Script this week. I am trying to create an array of hashes that I can then use to load the spreadsheet. Google apps script doesn't like the typical javascript …

Read More