0

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?

2
  • Welcome. Regarding "The script also sets a date- & timestamp when I change cells in column C." please add more details. How many script projects are attached to your spreadsheet? Doy your script projects have other functions?
    – Rubén
    Aug 5, 2019 at 14:30
  • Hi Ruben, thank you for your reply. There is only one script attached and it is exactly like above. So no other functions are used then listed above.
    – Ralph
    Aug 6, 2019 at 7:46

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.