Couldn't be bothered to look up the latest workout tracking app, so decided to stick to Google Sheets for now. One problem with this approach is that while it is easy on desktop GSheets to fill out a current timestamp (Command-Option-Shift-semicolon in MacOS), no such shortcut is available AFAIK in the iOS version of Google Sheets.
So I wrote a little custom function in Google Apps Script to do the following:
In an active Google Sheets spreadsheet, given a tab/sheet named myworkouts with a header named datetime, the following script updates a row's corresponding "datetime" column when a value is entered into a first column cell:
var SHEET_NAME = 'myworkouts';
var DATETIME_HEADER = 'datetime';
function getColByHeader(headerval){
var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getDataRange().getValues().shift();
var colindex = headers.indexOf(headerval);
return colindex+1;
}
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSheet();
var cell = ss.getActiveCell();
var datecell = ss.getRange(cell.getRowIndex(), getColByHeader(DATETIME_HEADER));
// Conditions:
// - edited (active) cell is in the first column
// - edited cell is not blank
// - corresponding datetime cell is blank
// - active sheet is named [SHEET_NAME]
if (cell.getColumn() == 1 && !cell.isBlank() && datecell.isBlank() && ss.getName() == SHEET_NAME) {
datecell.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm");
}
};Note: technically this script doesn't update the datetime column upon cell creation (I don't think there is a onCreate() Simple Trigger). Instead, it looks to see if the corresponding datetime cell is empty when the onEdit() event for a first column cell is triggered.
- Google Docs Editors Help community question/answer: Automatic Timestamp when a Cell on the same row gets Updated
- the
getColByHeader()function comes from this StackOverflow answer - Official help page for Custom Functions in Google Sheets
- Official help page for Keyboard shortcuts for Google Sheets
hi there! attached is a screenshot of my code. it was working fine in a test worksheet, but when i tried to apply it to the actual spreadsheet i'm getting this error that i don't understand. can someone help? thanks!
