Row Modified timestamp

Is there a clever way to make a row modified timestamp? This is so I can sort a list by date modified. The onEdit trigger doesn’t work since Glide updates the sheet with a script/api. Thanks in advance!

1 Like

Something like this.

1 Like

If you are using the built in edit function, you can use the date time special value.

3 Likes

Use onchange instead. But, sometimes, it fired not on the single cell but on the range of cells.

This is the best way to handle it if he’s using the edit pencil in my opinion. Point the current datetime special value to a “Last edit” column and you’re set.

3 Likes

This is what I ended up using, which has the added benefit of working on all of the sheets, because it only looks for a column with “Modified Date” in the first row (also, it won’t modify the first row):

function onChange(e) {

	const sheet = SpreadsheetApp.getActiveSheet()

	const rowIndex = (function() {
		switch (e.changeType) {
			case "EDIT":
				return sheet.getActiveCell().getA1Notation().replace(/\D/g, "").trim()
			case "INSERT_ROW":
				return sheet.getLastRow()
			default:
				throw new Error(`This function can't handle changeType: ${e.changeType}`)
		}
	})()

	const column = sheet.getDataRange().getValues()[0].indexOf("Modified Date") + 1

	if (rowIndex != 1 && column !== 0)
		sheet.getRange(rowIndex, column).setValue(new Date())
}

NOTE: This works based on a trigger, so after adding this function to the project, you have to create an “On change” trigger to run it.

1 Like

Hi there, I tried this but I get an error message “cannot read change type” for this line in your code, “switch (e.changeType)”. What am I missing?

Did you run the script manually using the “run” button?

Anyway, this can be handled natively by Glide though, can you specify why you want a script?

1 Like

I got it to work with this code in the end… Do you mean the built in edit pen function? It’s not intuitive to users and I have only so much phone screen space to place instructional text. End users are not tech savvy.

function onChange(e) {

var s = SpreadsheetApp.getActiveSheet();

if( s.getName() == “sheet1” ) { //checks that we’re on the correct sheet

var r = s.getActiveCell();

if( r.getColumn() == 1 ) { //checks the column

var nextCell = r.offset(0, 1);

if( nextCell.getValue() === ‘’ ) //is empty?

nextCell.setValue(new Date());

}

}

}

1 Like

Or you can use the set column action on buttons to provide a better experience for non tech-savvy users.

1 Like

Thanks for that! That’s much better than google script:-)
Thank you ThinhDinh for looking into this, much appreciated!

2 Likes

Sorry to bother you again Thinh but when it’s submitted, the form notification says “error!” instead of “success”. How to change or disable?

Can you clarify why it says “error”?

Sorry about this. For some reason, the error has ‘gone away’!

3 Likes