One of my most frequent everyday usecases for spreadsheets is casual tracking of personal activity, like daily exercise, or my progress in Duolingo; here’s what the spreadsheets for those two examples look like, respectively:

You’ll notice that both have a datetime field – I include this not because I’m particularly anal about when exactly I did something, but because maybe down the road I want to do a quickie pivot table analysis, like how often I did things in the morning 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 work of data entry for an extra field, especially added up over months, risks creating enough friction that I might eventually abandon my “casual tracking”.

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

• Ctrl/Cmd+: to insert date: 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

However, doing that per row is still work; more importantly, I wanted to be able to use the iOS version of Google Sheets to enter exercises as soon as I did them (e.g. random sets of pushups or pullups), and these time/date shortcuts are non-existent.

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

The solution

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

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

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

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

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

function onEdit(e) { 

}

But before we get into onEdit, we want a helper function that, given a fieldname like "datetime", it returns the column index as Google Sheets understands it, e.g. 2, in the given screenshot examples. Here’s one way to write that function, which we’ll call getDatetimeCol():

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+1;
}

The onEdit() function can be described like this:

• get the currently edited cell (which requires getting the currently active sheet)
• if the edited cell (i.e. active cell) is in the first column
  • and it is not blank
  • and the corresponding datetime field is blank
• then set the value of the datetime field to the 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+1;
}

function onEdit(e) {  
  var ss = SpreadsheetApp.getActiveSheet();
  var 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 Date()).setNumberFormat("yyyy-MM-dd hh:mm");
  }
};

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

And your sheet should have the power of auto-inserted timestamps:

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

• Abbas Abdulla’s response to the post “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 cell in Google Apps Script Spreadsheet