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?