-1

I can't seem to figure out how I can use the variable from my onSelectionChange function in the onEdit function. I want to append the cell values into another cell to retain its history but I don't want the current update to be in the history. I tried using PropertiesService but I can't get it to work. Could someone please help?

var latest_update;
var latest_date;
var updates_history;

function onSelectionChange(e){
    var row = e.range.getRow();
    var col = e.range.getColumn();
  if (col == 3){
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    updates_history = ss.getRange(e.range.getRow(),col + 2).getValue(); //store column E value, Updates History
    latest_update = ss.getRange(e.range.getRow(),col).getValue(); //store column C value, Latest Update value
    latest_date = ss.getRange(e.range.getRow(),col + 1).getValue(); //store column D value, Latest Update Date

  } else { return;}
}

function onEdit(e){
  onSelectionChange(e);
    var row = e.range.getRow();
    var col = e.range.getColumn();
  if (col ==3){
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    ss.getRange(e.range.getRow(),col + 2).setValue(latest_date + ": " + latest_update + "\n" + updates_history); //append Latest Update and Latest Update Date to Updates History
  } else {return;}
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Mike
  • 1

1 Answers1

0

Using PropertiesService

Try something like this:

function onSelectionChange(e){
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet0" && e.range.columnStart == 3){
    var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    PropertiesService.getScriptProperties().setProperty("LastSelect",`${e.range.getValue()}~${e.range.offset(0,1).getValue()}~${e.range.offset(0,2).getValue()}`);
  }
}

function onEdit(e){
  const sh = e.range.getSheet()
  if (sh.getName() == "Sheet0" && e.range.columnStart == 3){
    const t = PropertiesService.getScriptProperties().getProperty("LastSelect").split("~");
    sh.getRange(e.range.rowStart,e.range.columnStart + 2).setValue(t[1] + ": " + t[0] + "\n" + t[2]); 
  } 
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thank you for your help! I'm getting an error – Mike Jul 22 '22 at 16:59
  • I don't know where my edited comment went but the error I'm getting is: TypeError: Cannot read property 'split' of null at onEdit I actually got the global variable working but it doesn't work all the time and sometimes it enters too much. It's behaving as if it's not saving the variable quickly enough and sometimes combining multiple instances setProperty events. – Mike Jul 22 '22 at 17:23