Skip to content

Instantly share code, notes, and snippets.

@kodie
Last active April 15, 2024 07:02
Show Gist options
  • Save kodie/6dd1e4cb3cfd70a429d79c751bc8fb42 to your computer and use it in GitHub Desktop.
Save kodie/6dd1e4cb3cfd70a429d79c751bc8fb42 to your computer and use it in GitHub Desktop.
A JavaScript function for Google Sheets that updates a specific cell with the current date/time when cell(s) are updated.
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']
})
}
@aobajuluwa
Copy link

What is the options parameter in the updateModified function?

@kodie
Copy link
Author

kodie commented Mar 15, 2022

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

@aobajuluwa
Copy link

aobajuluwa commented Mar 15, 2022

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..).

@kodie
Copy link
Author

kodie commented Mar 15, 2022

@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
  })

@aobajuluwa
Copy link

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?

@kodie
Copy link
Author

kodie commented Mar 22, 2022

@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.

@pkasting
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment