App Script to set a cell value in the last row based on another cell in the same row

Question

I am looking for an assistance on creating an App Script. This script needs to do the following:

1. Go to the last row of the active Sheet.
2. Look at the value in last cell of column I, which is Yes or No.
3. Based on the value of the last cell of column I, a new value is assigned to the last cell of column A;
   • Yes = "RUSH"
   • No = "New"

It seems like it would be a simple task, but I can't seem to figure it out. I have been searching for examples, modifying them, and testing it all day with no luck. Any help would be greatly appreciated.

Thank you all!

Answer 1

Recommendation:

This script will get the last row and input "RUSH" or "New" in the last row of column A based on the last cell of column I if it is "Yes" or "No".

function yesOrNo() {
  var ss = SpreadsheetApp.getActiveSheet();
  var lastRow = ss.getLastRow()
  
  var value = ss.getRange(lastRow,9).getValue();

  if (value == "Yes"){
    ss.getRange(lastRow,1).setValue("RUSH");
    }
  else {
      ss.getRange(lastRow,1).setValue("New");
    }
  }

Google Sheets Formula:

You can also achieved it by using the formula below:

=ArrayFormula(IFERROR(ifs($I:$I = "Yes", "Rush", $I:$I = "No", "New"), ""))

References:

• If/else statement
• getValue()
• setValue()