Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Make IFTTT Date Format play nice with Google Spreadsheets

##Date and Time

=TIMEVALUE(SUBSTITUTE("{{OccurredAt}}"," at ", " ")) + DATEVALUE(SUBSTITUTE("{{OccurredAt}}"," at ", " "))


=DATEVALUE(SUBSTITUTE("{{OccurredAt}}"," at ", " "))


=TIMEVALUE(SUBSTITUTE("{{OccurredAt}}"," at ", " "))

To Use

Simple just place either or both separated by ||| in the
Formatted Row Input on IFTTT Example

Copy link

ghost commented Feb 23, 2018

This seems to be a solution

This works for me on a regular field {{CreatedAt}} with the trigger script

function addDate(e) {
  var lr = SpreadsheetApp.getActiveSheet().getLastRow();
  var timestampRange = SpreadsheetApp.getActiveSheet().getRange(lr, 1)
  if (timestampRange.getValue() == "") {
    timestampRange.setValue(new Date());

and then add tiggers as shown in the video (addDate for event from Spreadsheets on change)

I set this up to "record my blood pressue" for my mom

Copy link

jfretin commented Mar 20, 2018

Thanks for this last tip! It works like a charm.

Copy link

balexander85 commented May 6, 2018

👍 🙏

Copy link

norske52 commented Oct 20, 2018

Thanks, Oscar. This is exactly what I was looking for!

I've just recently begun playing with Siri Shortcuts and now IFTTT.

Copy link

borazslo commented Nov 5, 2018

Another solution working form me right now:
=DATEVALUE(REGEXREPLACE("{{OccurredAt}}";" at .*$"; " "))

  • REGEXPREPLACE instead of the more simple SUBSTITUTE
  • use of ";" instead of ","

Copy link

WannabeHomeEconomicus commented Jan 6, 2019

Thank You sir,
Helped me make my time tracking sheet much more elegant.

Copy link

lowrisk75 commented Apr 20, 2021

Another solution working form me right now:
=DATEVALUE(REGEXREPLACE("{{OccurredAt}}";" at .*$"; " "))

  • REGEXPREPLACE instead of the more simple SUBSTITUTE
  • use of ";" instead of ","

Thank ! I spend 24h looking for a solution and that seems to work!

Copy link

mbierman commented Apr 10, 2022

Uh... there is a much better way to deal with this.

In IFTTT, you add a filter you can format the date so you don't then have to play with it in the Sheet side.

For example, using the following filter:

let mytime = Meta.currentUserTime.format('l LT');
let update=MakerWebhooks.event.Value1;

GoogleSheets.appendToGoogleSpreadsheet.setFormattedRow(mytime + "|||" + update);

Which will format the all of the entries like so:

Copy link

moomdate commented Nov 9, 2022


function myFunction(e) {
  if(e && e.changeType === 'INSERT_ROW') {
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var sheet = ss.getSheetByName("Sheet1");
    sheet.getRange(sheet.getLastRow(),1).setValue(new Date());

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment