CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ezuk
ezuk

Reputation: 3196

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

Upvotes: 70

Views: 128668

Answers (9)

flyingjamus
flyingjamus

Reputation: 4023

You can try something like this:

function appendString() {
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var currentValue = range.getCell(i,j).getValue();
      var withString = currentValue + " string";
      range.getCell(i,j).setValue(withString);
    }
  }
}

Upvotes: 132

Şahan Şenvar
Şahan Şenvar

Reputation: 48

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

Upvotes: 1

Sero
Sero

Reputation: 161

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

Upvotes: 0

Jon
Jon

Reputation: 1328

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

Upvotes: 0

Stephan Stamm
Stephan Stamm

Reputation: 1153

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

Upvotes: 20

TheMaster
TheMaster

Reputation: 50452


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.

Upvotes: 1

glen
glen

Reputation: 1725

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

Upvotes: 7

Alexander Bird
Alexander Bird

Reputation: 40639

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)

Upvotes: 3

Voy
Voy

Reputation: 6284

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)

Upvotes: 27

Related Questions