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

Denigrate

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

Cheyenne

I was getting the error "Incorrect range width, was 5 but should be 1 (line 5, file "Code")" - and took a look at the provided documentation. I realized I missed the detail that inner arrays are rows, outer arrays are columns. This answer appears to address the question correctly. – Impasto

I believe this is for a ROW of results not a COLUMN of results as was stated in the problem. – Bookplate

So how to automatically convert ["Adam","Barb","Chris"] to [["Adam"],["Barb"],["Chris"]]? – Ginn

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

Bookplate

Running this code as shown (putting second block in a separate function) returns "TypeError: Cannot read properties of null (reading 'getRange')" from the last line of your first block. Any idea why? – Prototrophic

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.

Fennell

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

Cirrostratus

This doesn't seem to answer the OP's question. – Portraitist

There is a potential problem with this in that it seems that when there is an error there is no rollback. Perhaps thats why google recommends a different approach. (I'm looking around Stack Overflow to solve this on my own code) – Leanoraleant

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

Geordie

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

Betty

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.

Instanter

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

Valerie

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

Carapace

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
}

Hube

OMG what's the reason to write such useless and nonworking code as an answer here? – Receive

I fully agree with the other comment. Please consider removing it and go read GAS documentation. – Davila

sorry about that, I forgot a bracket. It works now. The reason for putting it is to show people how to set horizontal and vertical ranges as well as an array. Maybe you two can let me know what doesn't work. – Hube

It's just the same as in this dialog taken from the film "Amadeus" except that you can replace 'notes' with 'brackets'. – Davila

Good point. But since I'm new to GAS and Stackoverflow, I'll keep it up just for kicks... and show people what not to do. – Hube

@Sergeinsas Or maybe reading these comments is like this dialogue from George Lopez except you replace the "Max" with our usernames: "George Lopez: Sk8erboyz (#4.6)" (2004) Benny: [sees Max and several other nerdy kids] I've seen this movie before. [chants] Benny: Nerds! Nerds! Nerds! – Bebe