I'm a newcomer to this site and I have a few questions:

I want to make a time recorder, for which someone will input any data in a Google Sheet ( a cell any place)

My code works successfully, but I can't see any results from my spreadsheets.

Here is my code:

    function endofstudy(e) {
        var app = SpreadsheetApp;
        var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
        var currentCell = app.getActiveSpreadsheet().getRange("sheetname!K1255:K2000")
         if( currentCell.getRange < "101" ) { 
          var nextCell = currentCell.offset(0, -9);
          var newDate = Utilities.formatDate(new Date(),"GMT 09:00", "HH:mm");
          nextCell.setValue(newDate);
        }
      }

CodePudding user response：

The function you describe is typically called onEdit and is a simple trigger that doesn't need to be installed. Your function endofstudy need to be installed through the script editor.

I've shown an onEdit(e) that uses the event object to tell which cell is being edited and how to offset from there to place a time stamp.

I've edited my script to address the comments below.

Code.gs

function onEdit(e) {
  if( e.range.getSheet().getName() === "Sheet1" ) {  // limit this onEdit to only Sheet1
    if( e.range.getColumn() === 11 ) {  // Limit to only column K
      let row = e.range.getRow();
      if( ( row >= 1255 ) && ( row <= 2000 ) ) {  // Limit to rows 1255 to 2000
        let now = Utilities.formatDate(new Date(),"GMT 09:00", "HH:mm");
        e.range.offset(0,-7).setValue(now);  // offset -7 columns (K to D) from the cell being edited
      }
    }
  }
}

Reference

• Simple Trigger
• Event object
• Range.offset()
• Range.setValue()