Skip to content

Instantly share code, notes, and snippets.

@oscarmorrison
Last active December 3, 2023 21:44
  • Star 73 You must be signed in to star a gist
  • Fork 8 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save oscarmorrison/bf14dab89854238d4cca to your computer and use it in GitHub Desktop.
Make IFTTT Date Format play nice with Google Spreadsheets

##Date and Time

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

##Date

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

##Time

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

To Use

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

@balexander85
Copy link

balexander85 commented May 6, 2018

👍 🙏

Copy link

ghost 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.

@borazslo
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 ","

@WannabeHomeEconomicus
Copy link

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

@lowrisk75
Copy link

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!

@mbierman
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:
image

@moomdate
Copy link

moomdate commented Nov 9, 2022

\m/

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

@OliverK-dev
Copy link

OliverK-dev commented Dec 8, 2022

In Google Sheets you can just use this formula:

=TIMEVALUE(SUBSTITUTE(A1," at ", " ")) + DATEVALUE(SUBSTITUTE(A1," at ", " "))

I simply replaced {{OccurredAt}} with the cell reference -- i.e., A1

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