CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
CJ W
CJ W

Reputation: 17

how to print the value before it was changed?(google script)

If the value changes while using Google Script, I want to record the change in the cell next to it. Trigger is tested, but only the value after the change is saved, not the value before the change. Where do I need to fix to get the value before the change?

function onEdit1()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == "sheet1") //"order data" is the name of the sheet where you want to run this script.
  {
    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var aCol = headers[0].indexOf("A") + 1;
    var usdCol = headers[0].indexOf("USD") + 1;
    var rmbCol = headers[0].indexOf("RMB") + 1;
    
    var changedData = sheet.getRange(rowIndex, usdCol).getValues();
    var logData = sheet.getRange(rowIndex, aCol).getValues();
    
    if (aCol > 0 && rowIndex > 1 && editColumn == usdCol)
    {
      if (!sheet.getRange(rowIndex, aCol).isBlank())
      {
        sheet.getRange(rowIndex, aCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "yyyy-MM-dd") + "에 " + changedData + "(으)로 변경" + "/" + logData);
      }else{
        logData = Utilities.formatDate(new Date(), "UTC+8", "yyyy-MM-dd") + "에 " + changedData + "(으)로 변경됨" + " / ";
        // sheet.getRange(rowIndex, aCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "yyyy-MM-dd") + "에 " + changedData + "(으)로 변경됨" + " / ");
      }
    }
  }
}

Upvotes: 0

Views: 849

Answers (1)

Wicket
Wicket

Reputation: 38346

To get the value of a cell before it changes you could use the on edit trigger.

If a single cell is edited and, the event object might included value and oldValue properties.

The following is a very simple example that logs the old value to the cell to the right of the edited cell.

function onEdite(e){
   if(e.oldValue) e.range.offset(0,1).setValue(e.oldValue);
}
  • The value property will be undefined if the cell is cleared.
  • The oldValue property will be undefined if the cell was blank
  • Both properties will be undefined if multiple cells were edited (range property includes more than one cell)

Upvotes: 2

Related Questions