Iterate over range, append string to each

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

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`)
})

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)

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

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

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)

[tag:google-sheets]
You can easily do this with Find and Replace.

• Select your range

• Find:

    ^(.*)$
  

• Replace:

    $1AppendString
  

• Mark Use Regular Expressions

• Click Replace All

I don't see any advantage of using script here, but, if you must, you can also issue a Find Replace request through sheets API.

Google Sheets uses a Multidimensional Array so to make your life easier you can just flatten the array like this:

range.getValues().flat().forEach(function(item, i){
    var currentValue = item[i].getValue();
    var withString = currentValue + " string";
    item[i].setValue(withString);
});

This is how I would do this. It is a bit long but I think it's pretty pragmatic and reusable. Definitely functional.

This uses the V8 Engine and TypeScript

/*
    Transforms the original "Array of Arrays"—
    [
        [a, b, c, d, e],
        [a, b, c, d, e],
        [...],
        ...,
    ]
  
    into an "Array of Objects".
    [
        {timestamp: a, email: b, response_1: c, response_2: d, response_3: e},
        {timestamp: a, email: b, response_1: c, response_2: d, response_3: e},
        {...},
        ...,
    ]
*/
var original_values = SpreadsheetApp.getActiveSheet()
  .getRange("A:E")
  .getValues()
  .map(
    ([
      a, b, c, d, e,
   // f, g, h, i, j,
   // k, l, m, n, o,
   // p, q, r, s, t,
   // u, v, w, x, y,
   // z, aa, ab, ac, ad,
   // etc...
    ]) => {
      return Object.create({
        timestamp: a,
        email: b,
        response_1: c,
        response_2: d,
        response_3: e,
      });
    }
  );

/*
    Appends the string to some part of the Objects in our Array.
    Since the Objects match the table structure (hopefully) we can be
    pretty specific.
    
    I tried to mock how a Google Form might collect responses.
*/
var appended_string = original_values.map(
  (arg: { timestamp; email; response_1; response_2; response_3 }) => {
    switch (typeof arg.response_1) {
      case "string":
        return Object.assign(arg, {
          response_1: (arg.response_1 += " string"),
        });

      default:
        return arg;
    }
  }
);

/*
    Need to reshape the "Array of Objects" back into an "Array of Arrays".
    Pretty simple compared to the original.
*/
var values_to_set = appended_string.map(
  (arg: { timestamp; email; response_1; response_2; response_3 }) => {
    return [
      arg.timestamp,
      arg.email,
      arg.response_1,
      arg.response_2,
      arg.response_3,
    ];
  }
);

/*
    Here we'll take our finalized "values_to_set Array of Arrays" and
    use it as the input for ".setValues()".

    All Google Sheets data starts and ends as an "Array of Arrays" but...
    It is significantly easier to work with as an "Array of Objects".

    Rhetorical Question: Who wants to keep track of indexes?
*/
SpreadsheetApp.getActiveSheet().getRange("A:E").setValues(values_to_set);