Google-sheets – Help needed with script that auto-updates cells in column in Google Spreadsheet showing last modify date

google sheetsgoogle-apps-scriptgoogle-apps-script-triggersgoogle-sheets-timestamp

I'm looking for some help with a script for a spreadsheet.

I found a script (see below) that does half the trick for me, but I encounter two difficulties.

What I am trying to accomplish is the following:

  • In my Spreadsheet all cells in column A starting form row 3 do contain functions which give one of the follow two outputs; "yes" or "no".
  • I want to set a date- & timestamp in column B every time the cell in column A for the same row is changed (from "yes" to "no" or vice-versa).
  • If any other cells in the same row change (e.g. column C or D), the date- & timestamp must not be affected.

As said, I encounter two difficulties:

  • Because the cells in column A contain a function, the script does not give a date- & timestamp when the outcome of the function changes. The script only sets the date- & timestamp when I make an adjustment to the function itself.
  • The script also sets a date- & timestamp when I change cells in column C.

The script I am using now:

function onEdit(e) {  
  // Your sheet params  
  var sheetName = "Sheet7";  
  var dateModifiedColumnIndex = 2;  
  var dateModifiedColumnLetter = 'B';  

  var range = e.range; // range just edited  
  var sheet = range.getSheet();  
  if (sheet.getName() !== sheetName) {  
    return;  
  }  

  // If the column isn't our modified date column  
  if (range.getColumn() != dateModifiedColumnIndex) {   
    var row = range.getRow();  
    var time = new Date();  
    time = Utilities.formatDate(time, "GMT-08:00", "dd/MM/yyyy, hh:mm:ss");  
    var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter +
 row.toString());  
    dateModifiedRange.setValue(time);  
  };  
 };

Can someone help me?

Best Answer

This is a partial answer.

From the question:

Because the cells in column A contain a function, the script does not give a date- & timestamp when the outcome of the function changes. The script only sets the date- & timestamp when I make an adjustment to the function itself.

That is the way that on edit / on change / on open triggers works, they are fired only when change is made by the user by using the Google Sheets

The script also sets a date- & timestamp when I change cells in column C.

More details are required