When I edit a cell ("active cell"), the actual date set in another one ("celltoedit"). But when I delete the original one, "celltoedit" doesn't.
It's my first script, so I need help for dummies, please.
I have problems with the last part. With this code when I edit a cell, actual date appear in another one, but after this, both are automatically deleted.
The code is this:
//Some functions to get the Letter or 'vice versa'
function columnToLetter(column) {
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
function letterToColumn(letter) {
var column = 0,
length = letter.length;
for (var i = 0; i < length; i++) {
column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column;
}
//Source: https://stackoverflow.com/a/21231012/8586908
// When a cell is modified on the spreadsheet this function gets called.
function onEdit(e) {
//We get the spreadsheet object
var sheet = e.range.getSheet();
//Save currently activeCell
var activeCell = SpreadsheetApp.getActiveRange();
var column = activeCell.getColumn();
var row = activeCell.getRow();
//If the cell is empty probably the user has just deleted it so we don't have to put the date
if (activeCell.getValue() !== "") {
//We get the cell we want to edit
var cellToEdit = sheet.getRange(columnToLetter(column + 33) + (row)).getCell(1, 1)
//If the cell is empty
if (cellToEdit.getValue() == "") {
//We edit the cell to save the actual date, just if cell is in interested sheet and range.
if (e.range.getSheet().getName() != '1ºESO') return; //we put here this to limit its operation to a single sheet using Name's sheet.
if (e.range.columnStart >= 9 && e.range.columnStart <= 14 && e.range.rowStart >= 9 & e.range.rowStart <= 2653) { //Define interested range here: I9:N2653
cellToEdit.setValue(new Date());
//If the cell is deleted
if (activeCell.getValue() == "") {
//and the edition is in range
if (e.range.getSheet().getName() != '1ºESO') return; //we put here this to limit its operation to a single sheet using Name's sheet.
if (e.range.columnStart >= 9 && e.range.columnStart <= 14 && e.range.rowStart >= 9 & e.range.rowStart <= 2653) { //Define interested range here: I9:N2653
//we delete cell with date
cellToEdit.setValue("");
}
}
}
}
}
}
