How do I enter multiple timestamps for multiple columns as they are modified?

Question

I was able to modify if in any column within column 1 through 9 was modified the time stamp was entered on column 10 and last modified on column 11.

I know need to get granular and timestamp when each column is first modified, while maintaining the existing timestamp setting.

I believe this is as simple as an "if" statement but I can't seem to figure it out.

Here is what I have so far. This gives me the date and time any cell, between column 1 and 9 is first modified, on that row and returns date and time on column 10, then last modified date and time on column 11 if any changes are done. Now i want to keep record of when column 1-9 are first modified as well, and enter them on the same sheet starting at column 12

function addTimestamp (e){
 //variables
 var startRow = 2;
 var ws = "Daily Log"
 var currentDate = 1

 //get modified row and column
 var row = e.range.getRow();
 var col = e.range.getColumn();

 if(([1,2,3,4,5,6,7,8,9].includes(col))&& row >= startRow 
 &&e.source.getActiveSheet().getName() ==ws){

  var currentDate = new Date();
  e.source.getActiveSheet().getRange(row, 11).setValue(currentDate);
  if(e.source.getActiveSheet().getRange(row, 10).getValue()== ""){
    e.source.getActiveSheet().getRange(row,10).setValue(currentDate);
  } // END IF check if date created exists
 } // END IF column, row, worksheet
}

Special thanks to @raygun who helped me in creating uniq driver IDs.

Answer 1

Here's a function for logging all of your changes into a file:

function onMyEdit(e) {
  e.source.toast('entry');
  let sh = e.range.getSheet();
  if (e.range.columnStart < 10) {
    let f = DriveApp.getFileById('file id');
    let c = f.getBlob().getDataAsString();
    let ts = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "E MMM dd yyyy HH:mm:ss");
    let o = c + ts + ',' + sh.getName() + ',' + e.range.rowStart + ',' + e.range.columnStart + ',' + e.oldValue + ',' + e.value + ',' + e.user.getEmail() + '\r\n';
    f.setContent(o);
    Logger.log(o);
  }
}

Requires Installable Trigger

Data Table:

timestamp	sheetname	row	col	oldvalue	value	useremail
Sat Feb 13 2021 08:53:53 GMT-0700 (Mountain Standard Time)	Sheet1	5	4	30	55	Redacted
Sat Feb 13 2021 08:54:58 GMT-0700 (Mountain Standard Time)	Sheet1	9	4	32	55	Redacted
Sat Feb 13 2021 08:56:05 GMT-0700 (Mountain Standard Time)	Sheet1	14	7	62	new value	Redacted
Sat Feb 13 2021 08:56:45 GMT-0700 (Mountain Standard Time)	Sheet1	1	2	10	45	Redacted
Sat Feb 13 2021 08:56:52 GMT-0700 (Mountain Standard Time)	Sheet1	3	2	11	77	Redacted
Sat Feb 13 2021 09:10:16 GMT-0700 (Mountain Standard Time)	Sheet2	5	2	4	77	Redacted
Sat Feb 13 2021 09:16:08 GMT-0700 (Mountain Standard Time)	Sheet5	8	4	undefined	666	Redacted
Sat Feb 13 2021 09:18:04 GMT-0700 (Mountain Standard Time)	Sheet5	13	7	undefined	66	Redacted
Sat Feb 13 2021 09:19:44 GMT-0700 (Mountain Standard Time)	Sheet5	9	3	undefined	66	Redacted
Sat Feb 13 2021 09:20:29 GMT-0700 (Mountain Standard Time)	Sheet5	11	7	undefined	kk	Redacted
Sat Feb 13 2021 09:22:21 GMT-0700 (Mountain Standard Time)	Sheet1	1	1	undefined	undefined	Redacted

Here's a utility for loading a simple csv into a spreadsheet:

function loadCSVintoSpreadsheet() {
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  const resp1 = ui.prompt('Select Sheet Name', 'Enter destination sheet name', ui.ButtonSet.OK_CANCEL);
  if (resp1.getSelectedButton() == ui.Button.OK && resp1.getResponseText().length > 0) {
    const sh = ss.getSheetByName('Sheet1');
    sh.clearContents();
    const resp2 = ui.prompt('CSV FileId', 'Enter file id of csv', ui.ButtonSet.OK_CANCEL);
    if (resp2.getSelectedButton() == ui.Button.OK && resp2.getResponseText().length > 0) {
      const file = DriveApp.getFileById(resp2.getResponseText());
      const csv = file.getBlob().getDataAsString();
      let csvA = Utilities.parseCsv(csv)
      sh.getRange(1, 1, csvA.length, csvA[0].length).setValues(csvA);
    }
  }
}

Answer 2

Explanation:

Your goal is:

• Keep the datetime for which any cell in columns 1-9 is modified for the first time in column 10.

• For every cell modified in columns 1-9 add a last modified stamp in columns 12-20 respectively. For example if column 1 is modified, update timestamp in column 12, if column 2 is modified update column 13 etc.

For performance reasons, it is always a good practice to store the code you are using often, in variables:

• For example e.source.getActiveSheet() is used 4 times in your code and every time it is called, it consumes resources. You can instead store it in a variable and use that variable instead.

Solution:

function addTimestamp (e){
 //variables
 const startRow = 2;
 const ws = "Daily Log"
 //get modified row and column
 const rng = e.range;
 const as = rng.getSheet();
 const row = rng.getRow();
 const col = rng.getColumn();
  
 if(col>=1 && col<=9&& row >= startRow && as.getName() ==ws){
  const currentDate = new Date();
  const rng_all = as.getRange(row, col+11);
  const rng_ten = as.getRange(row, 10);
  as.getRange(row,11).setValue(currentDate);
  if(rng_all.getValue()== ""){
      rng_all.setValue(currentDate)
   }
  if(rng_ten.getValue()== ""){
      rng_ten.setValue(currentDate);
  } 
 } 
}

Side Note:

You don't need an installable trigger for this particular task. You can replace addTimestamp(e) with onEdit(e) (simple trigger) and it would also work. But it is up to you what you prefer, but it would be a good idea to read the differences (in the hyperlinks I provided).