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

Meneses

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(i, j)
      
      f(cell)
    }
  }
}

//
// Usage
//

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

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

Marismarisa

That's incorrect. Is rows,columns not columns,rows. developers.google.com/apps-script/reference/spreadsheet/… – Tryptophan

Honestly mindblowing that this hsa 133 upvotes and is accepted with such a fatal bug in it... For reference, the fix is getCell(j, i) instead of getCell(i, j) – Beatification

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)

Stowell

Yes this will be much quicker, especially when dealing with large ranges, because the process of updating cells is very slow (because it updates the UI each time). If you're dealing with a lot of cells the accepted answer won't even work because it will time out. – Presa

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

Olodort

map seems much more appropriate in this case: range.setValues(values.map(function (row) { return row.map(function (val) { return val + theConstantValue; }); })) – Clyve

@Clyve Your solution is a bit harder to understand. But it works. And is nice. Maybe I should add it as alternative to my answer. – Olodort

If Apps Script had arrow syntax it would be perhaps easier to understand, without the boilerplate of the function and return keywords: range.setValues(values.map(row => row.map(val => val + theConstantValue))); – Clyve

@tehhowch: I think, your solution is best. I added it to my answer. Thank you. – Olodort

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

Royalty

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)

Tarango

Where can we find the definition for BreakException? – Tractable

lol, good point @trex005. I added the definition and description into the answer. – Tarango

Answer 6

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.

Ezzo

Answer 7

I thing it is best method:

extention methods:

//Every range is an object. I need to know which object is range. That's why I'm made an isRange() extention method:
    Object.prototype.isRange = function () { return this.getCell !== undefined }
    
//This our forEach() function. It's checking wheter is range or not.  After then if object is a range find row and col count and access each one cell.
    Object.prototype.forEach = function (callbackFun, index, row, col) {
      try {
        if (!this.isRange()) {
          throw "this method is only works on range objects."
        }
    
        const numRows = this.getNumRows();
        const numCols = this.getNumColumns();
    
        let row, col, index
        index = 1
        col = 1
        for (let i = 1; i <= numCols; i++) {
          row = 1
          for (let j = 1; j <= numRows; j++) {
            const cell = this.getCell(j, i)
    
            //call lambda funtion
            callbackFun(cell, index, row, col)
            index++
            row++
          }
          col++
        }
    
      } catch (err) {
        Logger.log(err)
        throw err
      }
    }

test code:

//add a range wish to iterate
      let row = SpreadsheetApp.getActiveSheet().getRange("A1:A5")
      
      //run forEach method and write lamda code what you want to make with for each cell
      row.forEach((value, index, row, col) => {
      //I want to print cell value with row and col informations to screen
      Logger.log("cell " + index + " [" + row + "," + col + "] =>" + value.getA1Notation())
      })


output:

    cell 1 [1,1] => A1
    cell 2 [1,2] => B1
    cell 3 [1,3] => C1
    cell 4 [1,4] => D1
    cell 5 [1,5] => E1

Ruiz

Answer 8

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

Palingenesis

Answer 9

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

Flagwaving