CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
tirpitz
tirpitz

Reputation: 15

Automatic timestamp when 2 cell is filled out

Google Sheet Apps Script

function onEdit(e) {
  const editedRange = e.range
  const sheet = editedRange.getSheet()
  if (
    sheet.getName() === 'Data' &&
    editedRange.getLastColumn() >= 5 /*E*/ &&
    editedRange.getColumn() <= 6 /*F*/
  ) {
    const timestampRange = sheet.getRange(editedRange.getRow(), 1, editedRange.getNumRows(), 1)
    timestampRange.setValue(new Date())
  }
}

When E&F is Filled then A is Time Stamp

but i get TypeError: Cannot read property 'range' of undefined onEdit @ Code.gs:2

Upvotes: 1

Views: 97

Answers (1)

Logan
Logan

Reputation: 2140

Your error TypeError: Cannot read property 'range' of undefined onEdit @ Code.gs:2 means that there is no event object e from the range or it is failing. This is because you are running the script directly on the script editor. enter image description here

The trigger onEdit(e) can't be run from the script editor as it requires an event object that is only being created upon making edits on the Spreadsheet. It is running automatically every time you change the value of a cell. See onEdit(e) for more information.

To test your script you have to edit a cell then check the execution logs by clicking the Executions Tab on the menu on the left.

enter image description here

I have tested your script and it is running without an error. However, according to the condition you've stated it is supposed to only put timestamp when both E & F are filled so I have tweaked the code a bit to work according to this.

Try:

function onEdit(e) {
  const editedRange = e.range
  const sheet = editedRange.getSheet()
  var row = editedRange.getRow();

  if (
    sheet.getName() === 'Data' &&
    (editedRange.getColumn() == 5 || editedRange.getColumn() == 6) &&
    (sheet.getRange(row, 5).getValue() !== "" && sheet.getRange(row, 6).getValue() !=="")
  ) {
    const timestampRange = sheet.getRange(editedRange.getRow(), 1, editedRange.getNumRows(), 1)
    timestampRange.setValue(new Date())
  }
}

Result: enter image description here

Upvotes: 0

Related Questions