Skip to content

Instantly share code, notes, and snippets.

Embed
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 ", " "))

##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

@augustgerro

This comment has been minimized.

Copy link

commented Apr 18, 2016

For which channels it's work?
If I try to use Pocket+Google Spreadsheets, it gives error "Unrecognized Ingredient: {{OccurredAt}}"

@oscarmorrison

This comment has been minimized.

Copy link
Owner Author

commented May 24, 2016

Hi @augustgerro you may need to use the time stamp that Pocket provides. This may have a different name

@tymtraveler

This comment has been minimized.

Copy link

commented May 31, 2016

what is value1 and value2?

@osmithy

This comment has been minimized.

Copy link

commented Jul 18, 2016

This is exactly what I needed!!! Thanks so much.

@dspolleke

This comment has been minimized.

Copy link

commented Aug 11, 2016

I get a "Parse Error" when i try this.
the value actually in the sheet is: =TIMEVALUE(SUBSTITUTE("August 11, 2016 at 04:05PM"," at ", " ")) + DATEVALUE(SUBSTITUTE("August 11, 2016 at 04:05PM"," at ", " "))

@cbabos

This comment has been minimized.

Copy link

commented Aug 21, 2016

Currently the arguments must be separated by semicolons (;) in equation. This only fixes the parsing error however. It seems the Spreadsheet cannot parse the date (nor the time) anyway... (Additional info: it seems that the new spreadsheet version is default now)

@faquin

This comment has been minimized.

Copy link

commented Oct 11, 2016

I had to modify {{OccurredAt}} => {{CreatedAt}} but after that it worked very well.
Thanks!

@EvilistMonkey

This comment has been minimized.

Copy link

commented Jan 1, 2017

Why not just use =TODAY() and =NOW() or any of the other date formulas?

@Borlean

This comment has been minimized.

Copy link

commented Jan 14, 2017

Perfect. This is exactly what I was looking for, thanks a lot.

Just make sure you update the "OccurredAt" to be whatever the ingredient is listed as in IFTTT. Can't just copy pate the formula depending on the applet. Value 1 and Value 2 seem to be the OPs way of showing that you can still have additional ingredients besides the date/timestamp. Won't impact how the above formulas work.

@TedKihm

This comment has been minimized.

Copy link

commented Jan 22, 2017

I've got a weird problem where the ingredient {{CreatedAt}} from a Google Assistant trigger is not sending any data to Spreadsheet. All I get is an empty cell when using {{CreatedAt}}. I also see this behavior with the standard google Applet ID DMC8yDAW "Log notes in a Google Drive spreadsheet"

When I use the formula in Formatted Row:
{{TextField}}|||=DATEVALUE(SUBSTITUTE("{{CreatedAt}}"," at "," "))|||=TIMEVALUE(SUBSTITUTE("{{CreatedAt}}"," at "," "))

I get: #VALUE! entries
As example, the DATEVALUE formula in the spreadsheet resolves to =DATEVALUE(SUBSTITUTE(""," at "," "))

I get Invalid Applet if I use {{OccurredAt}} with this trigger.

Can anyone replicate this and either say it is working for them or not ? Any ideas on how to troubleshoot or is there another trigger that uses {{CreatedAt}} ? Thanks for the help!!

Using =Now() "works", but all past rows update to the current date/time every time a new row is appended

@PhilD41

This comment has been minimized.

Copy link

commented Jan 24, 2017

I can confirm that it does the same thing for me. I get an empty cell with {{CreatedAt}}. Which is very annoying as this functionality is exactly what I was wanting. I need to have the timestamp as well though. I expect this is an IFTTT issue though.

@iluc71

This comment has been minimized.

Copy link

commented Feb 2, 2017

Thank you. This is exactly what I was looking for. If you tried then showed "Parse error" message, change local at Menu File, Spreadsheet setings... to United States. Thanks again!

@breedus

This comment has been minimized.

Copy link

commented Feb 4, 2017

@PaulNewcomb, =TODAY(), etc. on the column would pull the current date every time the doc's rendered

@breedus

This comment has been minimized.

Copy link

commented Feb 4, 2017

By the way, this solution worked great for me: feel dumb for not having figured it out on my own. As noted, you just need to make sure adjust the OccuredAt ingredient to be specific to your applet. Mine was AddedAt.

@jajube

This comment has been minimized.

Copy link

commented Mar 18, 2017

For an unknown reason it doesn't work with other languages even when you are using the correct functions and parameters.
In general, every function wrote in any language not English doesn't work well with IFTTT. It throws an error in the cell of the spreadsheet.
You need yo go to the Spreadsheet settings and select "Always use the names of the functions in English."

@monkeydust

This comment has been minimized.

Copy link

commented Apr 20, 2017

Hi - has anyone managed to get this to work, so I want to use Google Home to log to Google Sheet with timestamp. {{CreatedAt}} is still showing as blank. The formula here doesnt work for me. Any ideas? Thanks MD

@likwah

This comment has been minimized.

Copy link

commented May 26, 2017

I have the same problem to do timestamp on google sheets. I used {{CreatedAt}} but it turn in to empty cell on google sheets. I also tried to use {{OccuredAt}} but ifttt refused it. Any other option to make timestamp that won't be recalculated on Google sheets?

@ayesamson

This comment has been minimized.

Copy link

commented Jun 17, 2017

This worked for me:
=DATEVALUE(SUBSTITUTE("{{OccurredAt}}"," at ", " ")) + TIMEVALUE(SUBSTITUTE("{{OccurredAt}}"," at ", " "))

image

but I had to format the column as such:

image

@umbertog21

This comment has been minimized.

Copy link

commented Nov 7, 2017

from today dont work !

@marekj

This comment has been minimized.

Copy link

commented Feb 23, 2018

This seems to be a solution https://www.youtube.com/watch?v=ufujOWXsq40&feature=youtu.be
Per https://productforums.google.com/forum/#!topic/googlehome/QuCPPxB9YLs;context-place=topicsearchin/googlehome/category$3Awindows

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

@jfretin

This comment has been minimized.

Copy link

commented Mar 20, 2018

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

@balexander85

This comment has been minimized.

Copy link

commented May 6, 2018

👍 🙏

@norske52

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

commented Jan 6, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.