Garmaine Staff asked 1 year ago

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("");
          }
        }
      }
    }
  }
}