How to get range and then set value in Google Apps Script

Question

I am trying to run a function on edit that will look for the value 'c' in a range of cells and then replace it with the word 'Closed'.

This is what I have so far:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getRange('CA12:CA15').getValues();
  var closed = 'c'
  for(var i=0; i<data.length;i++) {
  if(data[i] == closed) {

    Logger.log('yes')
    Logger.log(data[i]);    
  }
    }
  }

So this is successfully logging 'yes' and the value 'c' when I have this value within the given range. But how do I now replace this value?

I tried storing the range of data[i] using getRange() but it won't allow me to do this. If I can get this range then I know I can then use range.setValue('Closed') but a bit stuck at the moment. I know I'm doing something very simple very wrong but any help would be great.

Solution

Key points:

• getValues returns a 2 dimensional array ordered by rows and then columns. You're accessing a 1D array with data[i]
• i the index of array + start row is equal to the current row in loop.

Code snippet(slow):

if(data[i][0] == closed) { //2D
 sheet.getRange('CA'+(12+i)).setValue('Closed');
}

• But the above is slow, because we're calling setValue() for each true condition. Better way is to use arrays.

Code snippet(fast 70x):

var rng =sheet.getRange('CA12:CA15');
var data = rng.getValues();
var output = data.map(function(e) { return e[0] == closed ? ['Closed'] : e });
rng.setValues(output); //Single call

• Fastest way is to use Find and Replace either from the sheets UI or API

Essential Reading:

• Best Practices
• Array#map
• 2D array
• FR API request

Comments

[DevB1]

Thank you very much - the first option actually works well for me, although I understand it is not too efficient. However, I have one more issue. The data range I need to look at is actually the entire sheet rather than just CA12:CA15. However, when I try to extend the range it doesn't work ...