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

Answer 5

To wrap the array elements so that they can be written to a column, you could also use array.forEach()

This:

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

employees.forEach((item,index,array) => array[index] = [item]);

Will make the following change to your array:

["Adam","Barb","Chris"]  -->  [["Adam"],["Barb"],["Chris"]]

More information on forEach: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/forEach

Answer 6

An easier solution could be mapping the whole array and converting it using the toString() function and then using

range.setValue(employees.toString())

Answer 7

I saw this question when researching an answer for my own application. An alternative solution is to use a custom function that returns a 2D array.

Here's an example. Suppose "Adam, Barb, Chris; Aaron, Bill, Cindy" is text in A1. If you put a reference to a custom function in B1, you can fill the range B1:D2 with individual names.

Custom function written in Apps Script:

function splitNames(names) {
  // you could do any other work necessary here
  var arr = names.split("; ");
  arr[0] = arr[0].split(", ");
  arr[1] = arr[1].split(", ");

  // output should be 2D array where each element of the
  // main array is a range spanning 1 or more columns and
  // one row and each sub array are cells within that range
  return arr;
}

The formula in B1 referencing the custom function: =splitNames(A1)

Note, this function will throw an exception if the cells adjacent to B1 are not empty. This could be handled with the IFERROR formula or possibly try/catch within the custom formula.

Answer 8

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

Answer 9

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

Answer 10

Here are four functions: (1) setting a horizontal range of values (2) setting a vertical range of values (3) setting an area of values (4) copying an area of values and pasting it.

function SetHorizontalRange() {
var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var Sheet = Spreadsheet.getActiveSheet();
var row = 1; var numRows = 1;
var column = 1; var numColumns = 3; //("A1:C1")
var Range = Sheet.getRange(row, column, numRows, numColumns)
var values = [[["Adam"],["Barb"],["Chris"]]];


Range.setValues(values)
} 

//////////////////////////////////////////////////////////////////////////////ℱℳ
function SetVerticalRange() {
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = Spreadsheet.getActiveSheet();
  var row = 1; var numRows = 3; 
  var column = 1; var numColumns = 1; //("A1:A3")
  var Range = Sheet.getRange(row, column, numRows, numColumns)
  var values = [["Adam"],["Barb"],["Chris"]];


  Range.setValues(values)
} 

//////////////////////////////////////////////////////////////////////////////ℱℳ
function SetArea(){
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = Spreadsheet.getActiveSheet();
  var row = 1; var numRows = 3; 
  var column = 1; var numColumns = 3; //("A1:C3")
  var Range = Sheet.getRange(row, column, numRows, numColumns)
  var values = [[[["Adam"]], [["Barb"]], [["Chris"]]], [[["Barb"]], [["Chris"]], [["Adam"]]], [[["Chris"]], [["Adam"]], [["Barb"]]]];


  Range.setValues(values)
}

//////////////////////////////////////////////////////////////////////////////ℱℳ
function CopyPasteArea(){
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = Spreadsheet.getActiveSheet();
  var row = 1;  var column = 1;
  var Range = Sheet.getRange(row, column);
  var values = new Array(3);
  for (var y = 0; y < 3; y++) {
    values[y] = new Array(3);
    for (var x = 0; x < 3; x++) {
      values[y][x] = Range.offset(x,y).getValues(); Logger.log(x+' '+y);      
    }
  }


  Sheet.getRange(4, 1, 3, 3).setValues(values); Logger.log(values)
  //https://developers.google.com/apps-script/best_practices
}