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?

  • 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 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 at 7:46
0

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

  • Ah right, thank you! This means that the thing that I am trying to accomplish is not possible with the "Onedit function. Is there another way (other function) to get a date- & timestamp when the outcome of a function in a cell as changed? Like with the use of "OnChange", what would the script be? – Ralph Aug 6 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.