Google Apps Script Spreadsheets - Write Array to cells

google apps script getrange
google script cannot convert array to number
google apps script array push
google apps script setvalues
google spreadsheet script get cell value
google script multidimensional array
google script array
google sheets api update cell

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

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

write array to single column using script - Docs , Hi Mark - well done on your progress with learning about arrays in JavaScript/​Google Apps Script. Hopefully I'm not missing anything else, but I  MMULT to Average Every N Cells as an Array in Google Sheets – Step 2 Please take a close look at image # 2 above. As per the image we want to find the average of every 3 cells as the value in cell H2 is 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.

Google Apps Script Patterns: Writing rows of data to Google Sheets , A common pattern is to loop over the returned data and push values into an array and then write them to a sheet. An example using calendar  At the moment, during each iteration, I write the URL to the individual cell - I think this will be slow over a large number of documents. So I thought I'd try creating an array and then write the array into the column after all iterations are done. And I am failing - I don't understand some of the examples that I find in searches.

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

Reading & Writing Cell Values | Sheets API, Spreadsheets can have multiple sheets, with each sheet having any number of rows or columns. A cell is a Apps Script Java More To write data to a single range, use a spreadsheets.values.update request: Apps Script If COLUMNS is specified, each inner array is written to a column instead of a row. 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)); }

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

Class Range | Apps Script, following scopes: https://www.googleapis.com/auth/spreadsheets.currentonly Empty cells are represented by an empty string in the array. To write the data we need to specify a range to.setValues(). In the above example it is using.getRange(row, column, numRows, numColumns)to add data after the last row in the sheet. In the example the following parameters are used: row= sheet.getLastRow()+1– to get the last row of existing data and adding one to get the next row

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.

How do I write data to a single range in Google Apps Script?, So keep the number of spreadsheet function calls low. If you want to update multiple cells, it is usually better to fetch the full array of values first,  To create a bound script, select Tools > Script editor from within Google Sheets. The Spreadsheet service treats Google Sheets as a grid, operating with two-dimensional arrays. To retrieve the data

JavaScript 2 Dimensional Arrays, Reading & Writing Array To and , how to insert data from js object array to a google sheet in bulk to publish/write data from an array of js objects from an adwords script http://googleappsscript.​blogspot.com/2010/06/optimizing-spreadsheet-operations.html. Use setValue method of Range class to set the value of particular cell.. function storeValue() { var ss = SpreadsheetApp.getActiveSpreadsheet(); // ss is now the spreadsheet the script is associated with var sheet = ss.getSheets()[0]; // sheets are counted starting from 0 // sheet is the first worksheet in the spreadsheet var cell = sheet.getRange("B2"); cell.setValue(100); }

how to insert data from js object array to a google sheet in bulk , Just a quick post to log another Google Apps script how to - this time how to grab a 2D array of CSV data from a URL and then paste it into a  google.script.history (client-side) contiguous range of cells with data in them or the cell at the edge of the spreadsheet in that direction. the 2D array are

Writing 2D Data Arrays to a Google Spreadsheet from Google Apps , Array Formulas allow you to output a range of cells, rather than a single value. Now it works, and Google Sheets will output an array with each cell five different techniques, using Add-ons, Formulas, Formatting, Pivot Tables or Apps Script. I'm attempting to import a CSV file into a Google-Spreadsheet using Google-Apps-Script. I started with this tutorial which I found online. I ran into some difficulties with it and some searching brought me to this SO post. By coincidence it looks like the OP was trying to do the exact same thing as me so I adapted the answer to my own needs.

Comments
  • 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.
  • I believe this is for a ROW of results not a COLUMN of results as was stated in the problem.
  • This doesn't seem to answer the OP's question.
  • 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)
  • OMG what's the reason to write such useless and nonworking code as an answer here?
  • I fully agree with the other comment. Please consider removing it and go read GAS documentation.
  • 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.
  • It's just the same as in this dialog taken from the film "Amadeus" except that you can replace 'notes' with 'brackets'.
  • @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!