/files/images/posts/screenshots/gsheets-autotimestamp-header.png

I of my nigh frequent everyday usecases for spreadsheets is coincidental tracking of personal activeness, like daily practise, or my progress in Duolingo; here'south what the spreadsheets for those two examples expect like, respectively:

image daily-spreadsheets.png

You'll notice that both take a datetime field – I include this non because I'm specially anal most when exactly I did something, but considering perchance downwardly the road I want to do a quickie pivot table analysis, similar how often I did things in the morn versus evening, or weekday versus weekend, etc.

More data is generally better than less, since if I don't capture this info, I can't go back in time to redo the spreadsheet. However, the mundane piece of work of data entry for an actress field, especially added upward over months, risks creating enough friction that I might eventually abandon my "casual tracking".

Google Sheets does provide handy keyboard shortcuts for adding engagement and time to a field:

  • Ctrl/Cmd+: to insert appointment: 7/21/2020
  • Ctrl/Cmd+Shift + : to insert time: 3:25:24 PM
  • Ctrl/Cmd+ Alt + Shift + : to insert the full timestamp: 7/21/2020 12:05:46

Nonetheless, doing that per row is still work; more importantly, I wanted to be able to utilise the iOS version of Google Sheets to enter exercises every bit soon equally I did them (eastward.g. random sets of pushups or pullups), and these fourth dimension/engagement shortcuts are non-real.

What I needed was for the datetime field to be automatically exist filled each time I started a new row, i.e. as soon every bit I filled out the beginning field of a new row, due east.g. Duolingo word or exercise type.

The solution

What I wanted tin can be done by using the Script Editor and writing a Google Apps Script snippet. Hither are the steps:

Showtime, create a Google Sheet that has at least 2 columns, and with i of the non-first columns titled datetime:

image google-sheets-blank-datetime-template.png

Then, from the menubar, open up Tools » Script Editor:

image google-sheets-menu-script-editor.png

This will take you to a blank template for a new Google App Script projection/file:

image google-sheets-app-script-editor-new.png

1 of the features of Google Apps Script for Google Sheets is uncomplicated triggers, i.east. functions with reserved names like onOpen(e), onEdit(east), and onSelectionChange(e) that execute on mutual document events. What we want is to alter a row (i.e. insert a timestamp) when it is edited, so nosotros want onEdit(e):

But before we go into onEdit, we desire a helper function that, given a fieldname like "datetime", it returns the column index equally Google Sheets understands it, eastward.g. 2, in the given screenshot examples. Here's one manner to write that role, which we'll call getDatetimeCol():

                              var                SHEET_NAME                =                '                Sheet1                '                ;                var                DATETIME_HEADER                =                '                datetime                '                ;                office                getDatetimeCol                (){                var                headers                =                SpreadsheetApp                .                getActiveSpreadsheet                ().                getSheetByName                (                SHEET_NAME                ).                getDataRange                ().                getValues                ().                shift                ();                var                colindex                =                headers                .                indexOf                (                DATETIME_HEADER                );                render                colindex                +                ane                ;                }

The onEdit() part can exist described similar this:

  • get the currently edited prison cell (which requires getting the currently active canvas)
  • if the edited cell (i.due east. active prison cell) is in the first column
    • and it is not bare
    • and the corresponding datetime field is blank
  • then set the value of the datetime field to the electric current timestamp – in this example, I use the ISO 8601 standard of 2020-07-20 14:03

Here's the entire code snippet with both functions:

                              var                SHEET_NAME                =                '                Sheet1                '                ;                var                DATETIME_HEADER                =                '                datetime                '                ;                function                getDatetimeCol                (){                var                headers                =                SpreadsheetApp                .                getActiveSpreadsheet                ().                getSheetByName                (                SHEET_NAME                ).                getDataRange                ().                getValues                ().                shift                ();                var                colindex                =                headers                .                indexOf                (                DATETIME_HEADER                );                return                colindex                +                i                ;                }                office                onEdit                (                east                )                {                var                ss                =                SpreadsheetApp                .                getActiveSheet                ();                var                jail cell                =                ss                .                getActiveCell                ();                var                datecell                =                ss                .                getRange                (                cell                .                getRowIndex                (),                getDatetimeCol                ());                if                (                ss                .                getName                ()                ==                SHEET_NAME                &&                cell                .                getColumn                ()                ==                1                &&                !                cell                .                isBlank                ()                &&                datecell                .                isBlank                ())                {                datecell                .                setValue                (                new                Appointment                ()).                setNumberFormat                (                "                yyyy-MM-dd hh:mm                "                );                }                };

Now, select File » Save and give your project a name (it can be annihilation):

image google-sheets-app-script-editor-save-project.png

And your sail should have the power of motorcar-inserted timestamps:

gif animation of google sheet auto updating

Big thanks to the following resources, from which I adapted my solution:

  • Abbas Abdulla's response to the mail service "Automatic Timestamp when a Cell on the same row gets Updated", on Google Docs Editors Help forum
  • StackOverflow: How to use a column header to reference a jail cell in Google Apps Script Spreadsheet