Replace cell value of a column with cell value from another column based on a condition

Question

Quite new to google sheets apps script. I need help to do the following:

1. when the value in column S changes to 'Review' > take values from column W for all rows in 'Review' > replace existing values in column L with values from column W.
2. Is there a way to have a keyboard shortcut to execute it? Many thanks for looking into it!

The question is very similar to this one Set Value Based on Another Cell's Value in the Same Row but instead of changing column L to a set/specific value, I need to change it to the corresponding value from column W.

Answer 1

How about this:

• Copy and save this function in the Project editor of your spreadsheet.
• The answer assumes that your sheet name is "Sheet1" (line 14). You may need to change this.
• I have left a number of Logger statements in the script. If you wish, you can use these to clarify/confirm variable values at different stages of the script.
• the answer will work if:
  • text is entered in a single cell
  • text is dragged down several rows
  • text is copy/paste from one column to another

---

    function onEdit(e) {
  // Logger.log(JSON.stringify(e)) // DEBUG

  // get Event Objects
  var editedCol = e.range.columnStart
  var editedRowStart = e.range.rowStart
  var editedRowEnd = e.range.rowEnd
  var editedSheet = e.range.getSheet()
  var editedSheetName = editedSheet.getName()
  var numRows = editedRowEnd-editedRowStart+1
  // test for editing a single cell or a range
  if(editedRowEnd !== editedRowStart || e.value == undefined){
    // end row <> start row, so dragged value, or copy/paste
    // the editedValue is in the row ABOVE the start row
    var editedValue = editedSheet.getRange((editedRowStart-1), 19).getValue()
    // Logger.log("DEBUG: dragged or copy/paste: the edited value = "+editedValue)
    var sourceRange = editedSheet.getRange(editedRowStart,23,numRows)
    var targetRange = editedSheet.getRange(editedRowStart,12,numRows)
  }else{
    // edited a single column, event object available
    var editedValue = e.value
    // Logger.log("DEBUG: data entry single cell: the edited value = "+editedValue)
    var sourceRange = editedSheet.getRange(editedRowStart,23)
    var targetRange = editedSheet.getRange(editedRowStart,12)
  }
  // Logger.log("DEBUG: edited sheet name = "+editedSheet.getName()+", edited rowStart = "+editedRowStart+", edited rowEnd = "+editedRowEnd+", edited column = "+editedCol)
  // Logger.log("DEBUG: edited value = "+editedValue+", source range = "+sourceRange.getA1Notation()+", target range = "+targetRange.getA1Notation())
  // Logger.log("Number of edited rows = "+numRows)

  // test for edit on sheet:"Sheet1", in Column:S(19) and value:"Review"
  if (editedSheetName !== "Sheet1" || editedCol !== 19 || editedValue !== "Review") {
    // Logger.log("DEBUG: INVALID edit: sheet <> input &/or col <> 1 &/or value <>Review")
    return
  } else {
    // Logger.log("DEBUG: VALID edit: sheet = Sheet1 & col = 19 & value =Review")
    // copy data from Column W (23) to column L9(12)
    if (numRows == 1){
      targetRange.setValue(sourceRange.getValue())
    }
    else{
      targetRange.setValues(sourceRange.getValues())
    }
  }
}