A JavaScript function for Google Sheets that updates a specific cell with the current date/time when cell(s) are updated.

updateModified.js

function getColumnNumberByName(name, sheet) {
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSheet()
  }

  var headers = sheet.getDataRange().offset(0, 0, 1).getValues()[0]
  var column = false

  for (var i = 0; i < headers.length; i++) {
    if (headers[i].trim() === name) {
      column = i + 1
      break
    }
  }

  return column
}

function updateModified(options) {
  var activeSheet = SpreadsheetApp.getActiveSheet()

  if (!options.sheetName || !options.dateColumn || activeSheet.getName() !== options.sheetName) {
    return
  }

  var activeCell = activeSheet.getActiveCell()
  var activeColumn = activeCell.getColumn()
  var activeRow = activeCell.getRow()
  var column = options.dateColumn

  if (typeof options.dateColumn === 'string') {
    column = getColumnNumberByName(options.dateColumn, activeSheet)

    if (!column) {
      throw new Error("Can't find dateColumn with name: " + options.dateColumn)
    }
  }

  if (activeColumn === column) {
    return
  }

  if (options.columns) {
    var trackedColumns = []

    if (typeof options.columns === 'string') {
      options.columns = [options.columns]
    }

    for (var i = 0; i < options.columns.length; i++) {
      if (typeof options.columns[i] === 'string') {
        var trackedColumn = getColumnNumberByName(options.columns[i], activeSheet)

        if (trackedColumn) {
          trackedColumns.push(trackedColumn)
        } else {
          throw new Error("Can't find column with name: " + options.columns[i])
        }
      } else {
        trackedColumns.push(options.columns[j])
      }
    }

    if (!trackedColumns.includes(activeColumn)) {
      return
    }
  }

  if (options.rows) {
    if (!Array.isArray(options.rows)) {
      options.rows = [options.rows] 
    }
    
    var validRow = false
    
    for (var j = 0; j < options.rows.length; j++) {
      if (typeof options.rows[j] === 'string') {
        var range = options.rows[j].split('-')
        var firstRow = range[0]
        var lastRow = range[1] || range[0]

        if (activeRow >= parseInt(firstRow) || activeRow <= parseInt(lastRow)) {
          validRow = true
        }
      } else if (activeRow === options.rows[j]) {
        validRow = true
      }
    }

    if (!validRow) {
      return
    }
  }

  var cell = activeSheet.getRange(activeRow, column)
  var date = new Date()

  if (options.timezone && options.dateFormat) {
    date = Utilities.formatDate(date, options.timezone, options.dateFormat)
  }

  cell.setValue(date)
}

function onEdit() {
  // Any row that has any column updated, will have that row's "Last Modified" column set to the current date
  updateModified({
    sheetName: 'Sheet1',
    dateColumn: 'Last Modified'
  })
  
  // Any row that has the "Data" column updated, will have that row's "Last Modified" column set to the current date
  updateModified({
    sheetName: 'Sheet1',
    dateColumn: 'Last Modified',
    columns: 'Data'
  })
  
  // If rows 2 through 5 have the "Data" column updated, that row's "Last Modified" column will be set to the current date
  updateModified({
    sheetName: 'Sheet1',
    dateColumn: 'Last Modified',
    columns: 'Data',
    rows: '2-5'
  })
  
  // If row 9 has the "Data" column or the 5th column updated, it's 7th column will be set to the current date in a custom format/timezone
  updateModified({
    sheetName: 'Sheet1',
    dateColumn: 7,
    columns: ['Data', 5],
    rows: 9,
    timezone: 'America/Chicago',
    dateFormat: 'M/dd/yy HH:mm'
  })
  
  // If row 9 or rows 15 through 22 have the "Data" column updated, that row's "Last Modified" column will be set to the current date
  updateModified({
    sheetName: 'Sheet1',
    dateColumn: 'Last Modified',
    columns: 'Data',
    rows: [9, '15-22']
  })
}

This looks awesome but I can't figure out how to use it. I made a column called "Last Modified". I copied the script and ran it. What am I missing? Thanks for the help!

Thanks for the help... This is one of my first scripts and will be super helpful.

@GitHubKtShoe The last half (the onEdit function) needs to be modified for your specific use. There are 5 instances of the updateModified function as examples but you'd typically only have one.

I had to move in a different direction but could use some help if willing... here is my current code...
https://gist.github.com/GitHubKtShoe/ddd6cf162885590521a8eadb10632f35

I just want to add 2 additional If statements like this....

if (header = 'Last Modified' in Column 1, Use column 1 for the time stamp., else do not return the date)
If (header = 'User' in Column 2, Use column 2 for the use email, else do not return the user email).

I am trying to avoid the script impacting single sheets that do not have those 2 columns and exact headers.

What is the options parameter in the updateModified function?

@aobajuluwa You can see example usage at the bottom (inside of the onEdit function).

Thanks! I was able to get it to work :). I'm curious though, how do you think I could implement this without defining a header row? For instance if I just wanted to use the the column header letters (A, B, C..).

@aobajuluwa Column headers are defined as numbers starting at 0.

So for example, if you wanted Column C to be updated:

  updateModified({
    sheetName: 'Sheet1',
    dateColumn: 2
  })

Got it, thanks again. I am having an issue though, whenever I change the sheet name I start having issues with the function even though I'm referencing the sheet name in the function. Have you experienced anything like this?

@aobajuluwa Can you describe the issues you're having? I'm using this script on a document that has multiple sheets and it successfully works on the specific sheet I define.

On line 82, || should be &&, or row restriction will not actually be applied.