I'm trying to create a cell that automatically saves the modification date of a specific range of cells. I don't know the best way to do it, but I think that with a script it would be something like this:
function onEdit(e){
var DateCol = "K8";
var curDate = Utilities.formatDate(new Date(), "UTC+1", "hh:mm")
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var range = sheet.getRange('C11:K18');
//range.activate();
var rowIndex = range.getRowIndex();
var colIndex = range.getColumnIndex();
// May need to set up multiple ranges like this and check all ranges if
// checked fields are not in adjacent cells
var watchRange = { // B2:G20
top : 11, // start row
bottom : 18, // end row
left : 3, // start col
right : 11, // end col
};
// Exit if we're out of range
if (rowIndex < watchRange.top || rowIndex > watchRange.bottom) return;
if (colIndex < watchRange.left || colIndex > watchRange.right) return;
var currentValue = range.getValue();
currentValue = currentValue.replace(/^\s+|\s+$/g,""); //"trim" kludge
if (currentValue.length == 0){
// Set a column to show last edit date
SpreadsheetApp.getActiveSheet().getRange(DateCol + rowIndex).setValue("Edited: " +
curDate);
}
}
Right now it does not give me an execution error, but it does not write the date in the cell and it selects the range of all the sheets and not one in particular.
I have managed not to select all the sheets by modifying line 4
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
I have modified line 26
if (currentValue.length != 0)
Modifying line 29 writes me in the cell that I want
SpreadsheetApp.getActiveSheet().getRange(DateCol).setValue(curDate);
The only thing that fails me is that it puts me 1 hour less whatever I do.
I have modified this and it has been fixed
var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
Most likely this is because your spreadsheet has a different time zone than the one indicated in
In Configuring the regional location and calculation settings of a spreadsheet, it is explained how to consult/modify the time zone of your spreadsheet.