0

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()
}
1
0

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")
  }
}
4
  • Hi Tedinoz, thank you very much but it doesnt work for me, i dont know if am doing something wron...
    – Jeff
    Nov 15 at 14:37
  • TypeError: activa.getRange is not a function
    – Jeff
    Nov 15 at 16:18
  • Yikes - my fault. I've fixed that. I also noticed that && archivo.getActiveSheet() didn't test whether the edit was on sheet = "No Contact", so I added this to the list. I also added some other comments that you may (or not) find helpful.
    – Tedinoz
    Nov 16 at 0:12
  • Thank you @Tedinoz you're a very helpful person.
    – Jeff
    Nov 24 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.