Input timestamp into last cell

Question

I work on a script to input timestamp in the lastcell when all previus cells are modified. The following is not working.

function timestamp() {
  var archivo =SpreadsheetApp.getActiveSpreadsheet();
  var ss = archivo.getSheetByName("No Contact");
  var activa = ss.getActiveCell();
  var valor =activa.getValue();
  var filaActiva = activa.getRow();
  var colActiva =activa.getColumn();
  var lastcell =ss.getLastColumn();

  if(filaActiva>=2 && (colActiva>=2) && archivo.getActiveSheet()){
    if(activa.getLastCell().getValue()){  
    
    }else{
    lastcell.setValue(new Date()).setNumberFormat("MM/dd-HH:mm");
    }
  }
}

function onEdit(){
  timestamp()
}

Answer 1

Your code fails at several places

#1 - if(filaActiva>=2 && (colActiva>=2) && archivo.getActiveSheet()){

• The sheet "No Contact" is used to define many variables, so one would think that it is important to make sure that the edit took place on sheet = "No Contact. But archivo.getActiveSheet() doesn't evaluate the sheet, it merely confirms that there IS an active sheet.

Use: && archivo.getActiveSheet().getName() == "No Contact"){

---

#2 - activa.getLastCell().getValue();

• activa references a cell but getValue requires a reference to a sheet.

• There is no such function as getlastCell

You need to construct a range (based on a specific sheet) using the variables for the row and last column.

Use: ss.getRange(filaActiva, lastcell).getValue()

This should return the value in the last column in the edited row of the sheet named "No Contact".

---

#3 - lastcell.setValue(new Date()).setNumberFormat("MM/dd-HH:mm");

• lastcell is not a valid parameter.

In order to setValue, you need to define a sheet and range. lastcell is a just an Column index.

Use:

ss.getRange(filaActiva, lastcell).setValue(new Date()).setNumberFormat("MM/dd-HH:mm")

---

Other comments

1 - I don't know that there are hard and fast rules, but the naming of variables in the script appears unconventional. The script below is a re-write using more conventional naming.

2 - Since you are running this function from onEdit, it would be useful to take advantage of the Event Objects. The amended script below reflects these.

3 - Logger.log(JSON.stringify(e)) is a handy tool that will list the various event objects.

4 - I've left a lot of Logger.log statements in the script as debugging tools. These DO slow down the script a little but they can easily be commented out. 5 - I inserted an ELSE for the initial IF as a "DEBUG" tool to confirm that the script was working as expected. The entire ELSE can be deleted once you are satisfied that the script is operating to expectation.

---

function onEdit(e){
  timestamp(e)
}

function timestamp(e) {

  // display the event objects
  Logger.log(JSON.stringify(e)) // DEBUG
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  
  // set some variables
  var sheetname = "No Contact" // the name of the sheet to watch
  var valor = e.value // the value in the edited cell
  var filaActiva = e.range.rowStart // the row of the edited cell
  var colActiva = e.range.columnStart // the column of the edited cell
  var lastcell = e.range.getSheet().getLastColumn() // the last column with a value of the edited sheet
  
  Logger.log("DEBUG: the spreadsheet name is "+e.source.getName()+", the sheet = "+e.range.getSheet().getName())
  Logger.log("DEBUG: edited cell = "+e.range.getA1Notation()+", edited row = "+filaActiva+", edited column= "+colActiva+", edited value = "+valor+", the last column = "+lastcell);

  // test for edited row, column and sheet
  if(filaActiva>=2 && (colActiva>=2) && ss.getActiveSheet().getName() == sheetname){
    // the edited row was greater than or equal to 2 &
    // the edited column was greater than or equal to 2 &
    // the edited sheet was equal to "No Contacts" 
    
    // test for value in cell of last column of edited row
    if(e.range.getSheet().getRange(filaActiva,lastcell).getValue()){  
      // there was a value in the cell of the last column of edited row
      Logger.log("DEBUG: there is a value in the cell of the last column of the edited row so do nothing")
    }
    else{
      // there was NOT a value in the cell of the last column of edited row
      // so insert a Timestamp 
      Logger.log("DEBUG: there is NO value in the cell of the last column of the edited row so insert Timestamp")
      e.range.getSheet().getRange(filaActiva,lastcell).setValue(new Date()).setNumberFormat("MM/dd-HH:mm");
    }
  }
  else{
    // the edited row wasn't greater than or equal to 2 &/or
    // the edited column wasn't greater than or equal to 2 &/or
    // the edited sheet wasn't equal to "No Contacts" 
    Logger.log("DEBUG: if was NOT successful")
  }
}