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

Question

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 1

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"]];

Answer 2

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

Answer 3

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.

Answer 4

I improved script execution time by a factor of 10x. it works for me like this:

function xabo_api() { 
var sskey = 'KEY-CODE';
var doc = SpreadsheetApp.openById(sskey);    
var conn = Jdbc.getConnection("jdbc:mysql://HOST:PORT/DB", "USER", "PWD");
var stmt = conn.createStatement();

//stmt.setMaxRows(5000);

var rs = stmt.executeQuery("select * from xabo;");
var sheet = doc.setActiveSheet(doc.getSheetByName("xabo laboratories"));

//var cell = doc.getRange('A2');

var row = 0;
var data = [];

while (rs.next()) {
    var rowData = [];
    for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
    rowData[col] = (rs.getString(col + 1));
}
data[row] = rowData;
row++;
}

 // My script writes from the second row, as first I use for headers only

 var range = sheet.getRange(2,1,row, col); // check edges or uses a1 notation
 range.setValues(data);

 //   This is what google api doc suggests: It takes 10x more row by row
 //   while (rs.next())
 //   {
 //       for (var col = 0; col < rs.getMetaData().getColumnCount(); col++){
 //         cell.offset(row, col).setValue(rs.getString(col + 1));
 //        }
 //     row++;
 //   } 

 rs.close();
 stmt.close();
 conn.close();
 }