All I want is a Timestamp

Inserting the current date and time into a Google Sheets cell can be done with the following keyboard shortcut:

If you have found this page via searching specifically for this information, then please consider your objective fulfilled. You’re welcome!

There is a story around me getting to this point, though, since behind the simple table above is a small journey involving Google Sheets’ functions and macros, Javascript, and eventually QMK Firmware for mechanical keyboards. Still with me? Read on…

To-Do List

Using Google Sheets, I wanted to have a task list, similar to this screenshot, where I could log the start and end times of my tasks.

I made the assumption that there was a built-in function that could generate a timestamp for me, and when I went looking, I found NOW.

Using it in the spreadsheet would seem to have given me what I wanted, formatted in the way that I expect. Great!

When a task is finished, all I should have to do is make another call to NOW, and I would get a new generated date and time, right? Well, that happened, but also…

…the start time also got re-generated, ending up the same value as the finish time!

I thought that surely this could not be correct behaviour, but deleting the finish time caused the start time to be re-generated yet again!

So, it would seem that cells that use the NOW function get re-calculated whenever any change occurs in the spreadsheet. Perhaps there is a way to adjust this behaviour…?

In the Spreadsheet Settings, located under the File > Spreadsheet Settings > Calculation menu options, there are three re-calculation options for the spreadsheet, but none of them turn off re-calculation.

Looking back at the documentation for NOW, which I really should have viewed in more detail earlier, brings into focus that I’m using the wrong tool for the job:

• Note that NOW is a volatile function, updating on every edit made to the spreadsheet, and can hurt spreadsheet performance.
• NOW will always represent the current date and time the last time the spreadsheet was recalculated, rather than remaining at the date and time when it was first entered.

Now that I know I have a dynamic values problem, I wonder if there is any way to get a static datetime value, rather than what seems like a reference to a function that gets executed periodically?

At first glance, it looks like there are potentially two other functions that could fit the bill, since they return “values”:

• DATEVALUE
• TIMEVALUE

However, both of these functions require a static string parameter, and “return integers that can be used in formulas”, those integers being a serial number representation of the date or time.

I definitely know that this is not what I want, so no need to go further down this rabbit hole.

Custom Behaviour with Macros

At this stage, it looks like I will need to create my own custom function/behaviour to get what I want. In Google Sheets, you do this with Macros.

Since what I wanted was a NOW value, I will call the macro the next best thing: _now, and get it to do the following:

When I type _now into a cell, and the cell loses “focus” (ie I move the cursor to another cell), the value of that cell changes from the string _now, to the current date and time.

In Google Sheets, custom functions are created using Javascript, and are written in the Script Editor (Tools > Script Editor).

To get the desired behaviour, the _now function will need to hook into Sheets’ Simple Triggers, specifically the onEdit(e) trigger function, which “runs when a user changes a value in a spreadsheet.”

Opening up the Script Editor provides a default file called macros.gs, so that’s where the function will go:

macros.gs

/** @OnlyCurrentDoc */
function onEdit(e) {
  if (e.range.getValue() == "_now") {
    let date = new Date();
    let formattedDate =
      date
        .toLocaleString("en-AU", { timeZone: "Australia/Sydney", hour12: false })
        .replace(",", "");
    e.range.setValue(formattedDate);
  }
}

This function does the following:

• Whenever an edit occurs in the spreadsheet, the content of the cell is checked to see if it contains the string "_now" (technically, the value of the range of the event object e is checked)
• If it does contain "_now", then it uses Javascript to create a new date
• From that date object, a new date string is created and formatted according to my current locale (Sydney, Australia), and in 24-hour time
• The content of the cell is then set to the formatted date string

Let’s see how this works back in the spreadsheet:

Looks good to me! We’re done here now, right?

Well, something about this still did not sit right with me: surely the desire for a current date and time could not be so uncommon that all this ceremony and customisation was needed…?

The punch line is that, as you already know, if I had just looked into Google Sheets’ keyboard shortcuts, I could have saved myself all this time (though I would probably not have learned about all the things I’ve covered here, nor written this blog post at all, so I guess that is a good thing…?).

Opening up the shortcut search modal (⌘ + /) and searching for “time” displays the following:

The solution was unfortunately hidden away from me, but it was simpler than what I had created. So, rather than keep the custom function, I removed it in favour of using the “Insert current date and time” shortcut.

The only issue I can see now, though, is that a 4-key “shortcut” is a bit unwieldy, and since I will be using this a lot moving forward, I want it to be shorter. And, just for fun, I want to actually map it to a key on my keyboard.

So, it’s time to crack open QMK and make this happen!

(I know I could potentially make the shortcut a bit shorter using an operating-system level custom keyboard shortcut, rather than go down to the metal of my keyboard firmware, but where’s the fun in that?)

Configuring a Google Sheets Datetime Key

I have gone into the details on how to create new keyboard keycodes that map to custom actions in a couple of other blog posts:

• Escape the defaults and Control your keyboard with QMK
• Chording QWERTY with QMK Combos

So, I will just summarise the changes I needed to make to get a datetime appearing on a key press of my Ergodox EZ, and the blog posts above should be able to fill in any areas you may be uncertain about. You can also view the final result in my QMK Keymaps.

Custom Keycode

Add a custom keycode for a Google Sheets timestamp, which I will call GS_TIMESTAMP, to the custom_keycodes list:

enum custom_keycodes {
  // ....
  GS_TIMESTAMP,
  // ...
};

My custom_keycodes.

Custom Action

Add a custom action for when the key corresponding to the GS_TIMESTAMP is pressed. In this case, I want to map it to the Google Sheets shortcut: ⌘ + Option + Shift + ;.

This gets coded up in the process_record_user() function as one of the case options in the switch statement:

bool process_record_user(uint16_t keycode, keyrecord_t *record) {
  if (record->event.pressed) {
    switch (keycode) {
      // ...
      case GS_TIMESTAMP:
        SEND_STRING(SS_DOWN(X_LGUI)SS_DOWN(X_LALT)SS_DOWN(X_LSHIFT));
        SEND_STRING(SS_TAP(X_SCOLON));
        SEND_STRING(SS_UP(X_LGUI)SS_UP(X_LALT)SS_UP(X_LSHIFT));
        return false;
      // ...
    }
  }
  return true;
}

My process_record_user function.

Assign Keycode to Key

Now that GS_TIMESTAMP has a definition and an action that it performs, it needs to be assigned to a key on the keyboard:

// ...
[BASE] = LAYOUT_ergodox(  // layer 0 : default
  // left hand
  GS_TIMESTAMP, KC_1, KC_2, KC_3, KC_4, KC_5, KC_LEFT,
  // ...
),
// ...

Note that the positioning of the GS_TIMESTAMP keycode on the top left key of the keyboard, before the “1” key, is just meant to be illustrative of the kind of code change required. I ended up defining it somewhere else on my keymap.

If you followed along with your own QMK keyboard mapping, you can now re-compile your keymap, and enjoy one-key timestamp-ing in any of your Google Sheets!

And remember, if you come across a problem in Google Sheets that surely should have a solution, make sure to read every bit of documentation you can find before reaching for that text editor!