Copy active cell to other cells containing string

Question

In Google Sheets I'm trying to create a script that will take the value from the active cell and paste that value to any cell in Column B containing the string "HR". Any ideas?

Answer 1

This isn't too bad; you just have to wrap your head around a few concepts from Apps Script and Javascript to make it efficient. But first let's start with the naive approach!

function firstTry() {
  var activeSheet = SpreadsheetApp.getActiveSheet();  // whatever is open
  var activeCell = SpreadsheetApp.getCurrentCell();   // this is a single-cell range
  var activeCellValue = activeCell.getValue();        // could be a string, number, etc
  // Now let's look in column B for stuff to change
  for (var i = 1; i <= activeSheet.getLastRow(); i++) {
    var cell = activeSheet.getRange("B" + i);
    var val = cell.getValue();
    var valStr = String(val);                        // We could have gotten a number
    if (valStr.indexOf("HR") != -1) {
      cell.setValue(activeCellValue);
    }
  }
}

This will probably work, but isn't too efficient: each call to getValue() or setValue() takes some time. It'd be better to just get all the values at once, and then paste back a modified Column B when we're satisfied:

function improvement() {
  var activeSheet = SpreadsheetApp.getActiveSheet();  // whatever is open
  var activeCell = SpreadsheetApp.getCurrentCell();   // this is a single-cell range
  var activeCellValue = activeCell.getValue();        // could be a string, number, etc
  // Now let's look in column B for stuff to change
  var rowsWithData = activeSheet.getLastRow() - 1;
  var colBRange = activeSheet.getRange(1,             // start on row 1
                                       2,             // start on column 2
                                       rowsWithData,  // this many rows
                                       1);            // just one column
  // Let's get the data as an array of arrays. JS arrays are 0-based, btw
  var colBData = colBRange.getValues();               
  for (var i = 0; i < colBData.length; i++) {
    var val = colBData[i][0];                         // row i, first column
    var valStr = String(val);                         // We might have gotten a number
    if (valStr.indexOf("HR") != -1) {
      colBData[i][0] = activeCellValue;               // modify copied data
    }
  }
  // Lastly, write column B back out
  colBRange.setValues(colBData);
}

You could go further with a fancy filter function instead of looping over the data explicitly, but that starts to get less clear.

Caveats as the OP points out in comments below, blindly calling setValues like this will pave over any formulas you have. This would have been no big deal, except that this includes hyperlinks. You could get really involved by calling getFormulas in parallel with getValues and then decide whether to call setValue or setFormula depending on the original contents of each cell.