Auto-archive monthly by concatenation

Question

now that I have an "auto-change-date" function in my spreadsheet that automatically updates the "date" field when I make a change in the "comment" field.

It would be very helpful now, to have an "automatic archive" that copies the content from the "comment" field whenever updated and concatenates it with the existing string from the last comment.

This way I want to be able to delete the "comment" field - content and still keep ALL generated comments in this new "archive" field.

I have prepared a playground to give that all a try. Maybe you can just fill it up and add a column for your changes. Please also consider, that there is an auto-date function running in the background concerning my last issue.

https://docs.google.com/spreadsheets/d/1lWlPhS-XF0dtOZ4kOS9LU8haxcF5cWBKdbzp_lh1Z9I/edit?usp=sharing

Answer 1

try this:

function onEdit(e){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();
  var text; 

  if (column==2) { //Replace 2 with the column number of your comments cell//
    var newRange = sheet.getRange(row,column+1); //If the date is in the next column
    var today = Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'dd.MM.yyyy');
    newRange.setValue(today);
  }
  if (column == 2){
    text = sheet.getRange(row, 4).getValue();
    sheet.getRange(row, 4).setValue(text + e.value+".");
  }
}

I updated the code in the sheet you provided, adding the if statement at the bottom of your function, I tested by editing the comments and they got appended to the 4th column.