Iterate over range, append string to each

Question

I have a range of cells selected in a Google Sheets (activerange). I want to iterate over every cell in that range, and add a string to the end. The string is always the same, and can be hard coded into the function.

It seems like a really simple thing, but I've been messing with the code for an hour now and can't get anything useful to happen, and the docs are really not helping.

Here's what I have now. I don't code JS (I do know VBA, for all that helps..).

function appendString() {
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  for (var i = 0; i < range.length; i++) {
    var currentValue = range[i].getValue();
    var withString = currentValue + " string";
    range[i].setValue(withString);
  }
}

Answer 1

You can try something like this:

//
// helper `forEachRangeCell` function
//

function forEachRangeCell(range, f) {
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();

  for (let i = 1; i <= numCols; i++) {
    for (let j = 1; j <= numRows; j++) {
      const cell = range.getCell(j, i)

      f(cell)
    }
  }
}

//
// Usage
//

const range = SpreadsheetApp.getActiveSheet().getActiveRange();

forEachRangeCell(range, (cell) => {
  cell.setValue(`${cell.getValue()} string`)
})

Answer 2

Or alternatively use setValues() which writes the all values at the same time. Seems to execute quicker too.

var range = SpreadsheetApp.getActiveSheet().getActiveRange();
var numRows = range.getNumRows();
var numCols = range.getNumColumns();
var writeValues = []
for (var i = 1; i <= numRows; i++) {
  var row = []
  for (var j = 1; j <= numCols; j++) {
    var currentValue = range.getCell(i,j).getValue();
    var withString = currentValue + " string";
    row.push(withString)
  }
  writeValues.push(row)
}
range.setValues(writeValues)

Answer 3

EDIT March 2020: You can use modern ECMAScript now. If you enable the V8 runtime, this works:

function appendString() {
  const range = SpreadsheetApp.getActiveSheet().getActiveRange();
  const values = range.getValues();
  const modified = values.map(row => row.map(currentValue => currentValue + " string"));
  range.setValues(modified);
}

If you have to use an older Javascript version, you can:

function appendString() {
    var range = SpreadsheetApp.getActiveSheet().getActiveRange();
    var values = range.getValues();

    values.forEach(function(row, rowId) {
        row.forEach(function(col, colId) {
            values[rowId][colId] += " string";
        });
    });

    range.setValues(values);
}

Be aware that rowId and colId are zero-based. In the accepted answer, the indices are one-based. Or you use map without the arrow operator:

function appendString() {
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  var values = range.getValues();

  var modified = values.map(function (row) {
    return row.map(function (col) { 
      return col + " string"; 
    }); 
  })

  range.setValues(modified);
}

Answer 4

here's update to Voy's post, uses range.getValues() to get all values and omitting temporary array. should be even faster because range.getCell().getValue() is omitted in the two dimensional loop. Do note that the indexes start from 0 in this snippet. I also find this more readable.

  var cells = range.getValues();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numCols; j++) {
      cells[i][j] += " string";
    }
  }

  range.setValues(cells);

Answer 5

Here is a very general purpose function which iterates over a range's values. It can also be used to do a reduce function on it (which is useful in your case). It can also break out of the loop if you ever only want to find the first of an element.

It can very easily be changed to accept an actual Range instance instead of the array of values.

function range_reduce(rangeValues,fn,collection) {
  collection = collection || [];
  var debug_rr = "<<";
  for(var rowIndex = 0, row=undefined; rowIndex<rangeValues.length && (row = rangeValues[rowIndex]); rowIndex++) { 
    for(var colIndex = 0, value=undefined; colIndex<row.length && (value = row[colIndex]); colIndex++) {
      try {
        collection = fn(collection, value, rowIndex, colIndex);
      } catch (e) {
        if(! e instanceof BreakException) {
          throw e;
        } else {
          return collection;
        }
      }
    }
  }
  return collection;
}

// this is a created, arbitrary function to serve as a way
// to break out of the reduce function. Your callback would
// `throw new BreakException()` and `rang_reduce` would stop
// there and not continue iterating over "rangeValues".
function BreakException();

In your case:

var range = SpreadsheetApp.getActiveSheet().getActiveRange()
var writeValues = range_reduce(range.getValues(), function(collection, value, row, col) {
    collection[row] || collection.push([]);
    collection[row].push(value + " string");
});
range.setValues(writeValues)