IFTTT start and end times aren't parseable by Google Docs Spreadsheet?

  • 1
  • Idea
  • Updated 4 months ago
  • Not Planned
The recipe for adding a row to a google docs spreadsheet after a trip complete contains some date formatting that doesn't seem to allow the spreadsheet to 'parse' the format

The provided format is:

May 02, 2014 at 08:11PM

When I try applying formatting or operations (end cell - start cell to derive duration) it doesn't seem to be able to

I've imported csv data from the Trip Viewer sample app and the date formats in that csv are:

3/28/2014 16:42:00

When I try to apply formatting and operations they work as expected.

So that's a long winded way of saying that maybe you can adjust the output format of the IFTTT date-times into something more easily parseable?

Thanks
Photo of Alex Soto

Alex Soto

  • 128 Points 100 badge 2x thumb
  • frustrated

Posted 4 years ago

  • 1
Photo of Brendan Nee

Brendan Nee, Alum

  • 780 Points 500 badge 2x thumb
Thanks for the suggestion - I'll pass this along to the IFTTT team.
Photo of observer

observer

  • 60 Points
It would be good if this was addressed, but per this comment others are having the same issue http://webapps.stackexchange.com/questions/43665/format-date-ingredient-in-ifttt-channel
It seems its an IFTTT problem. Perhaps Automatic can apply their influence on IFTTT to make this more workable.

My sense is that the ingredients place holders {{}} format is ripe for providing custom format attributes.

Whilst I get of "content" creation the current IFTTT format is nice and readable it does not work well for data integration.

Perhaps a work around for Automatic is to provide some additional ingredients that provide the date in a machine parable format (be it a serial date number as an integer, or text that can be parsed by standard Excel DATEVALUE functions etc.
Photo of Tom

Tom, Former Employee

  • 41,706 Points 20k badge 2x thumb
Thanks for chiming in observer. I'm not sure if you're completely tied to using IFTTT, but are you familiar with the data export feature of the web dashboard? This functionality might help out in this circumstance. Here's a link to the related FAQ.
Photo of Dimitar Sakarov

Dimitar Sakarov

  • 80 Points 75 badge 2x thumb
Hi Guys,

I have a workaround I've successfully used in my IFTTT-populated spreadsheet.

Adding one more column with formula, which ends up being properly formatted and can be used as a date solved the issue for me.

The formula should look as the one below, where the "A2" cell reference should point to the cell where you have the original IFTTT exported date. In my case the exported date is in the first column, and starts from the second row (which is A2), so in B2 I can have:
 
=SUBSTITUTE(A2, " at ", " ")
This only copies the content from the first cell, dropping the " at " substring, which is breaking the date syntax for Google Sheets.

Then I'll have:
in A2:   "March 17, 2016 at 06:51PM"      (invalid IFTTT date)
in B2:   "March 17, 2016 06:51PM"          (formatted and now valid date)

Then you can have more formulas on top of B2, e.g.

Getting the DAY-of-WEEK:      =TEXT(B2,"DDD")                -->  "Thu"
Extracting the hour only:         =TEXT(E20-E19,"hh:mm")    -->  "08:13"
... etc.

I spent some time dealing with this and it now works like a charm for me, so I hope it can be helpful for others as well.

Cheers!
Dimitar
(Edited)
Photo of Jeff

Jeff, Alum

  • 14,146 Points 10k badge 2x thumb
Thanks for sharing this with the Automatic Community, Dimitar :)
Photo of TheRealSoAndSo

TheRealSoAndSo

  • 60 Points
Awesome solution, Dimitar.

But, better yet, put the section

=SUBSTITUTE(A2, " at ", " ")

into your IFTTT recipe, where "A2" is now the ingredient "{{RunAt}}" (*IMPORTANT: IN QUOTES!!*) or whatever timestamp variable you're using native to IFTTT.

IFTTT passes the math on to Google Sheets, which then seems to parse it directly.

The bonus is also then: you can then also do additional math against this field, AS it enters the Google Sheets spreadsheet, and spend less on the Google Sheets side manipulating and massaging data. For example, I've now got a Workflow button calling an IFTTT timesheet recipe with two additional ingredients. The first is merely set to "Started Work" or "Finished Work". The second ingredient, however, allows me to advance the time if I wish.

When I run the Workflow recipe, it calls the IFTTT recipe two times in succession: once to add a new row with the existing RunAt plus ExtraIngredient2 set to 0 (and Ingredient 1 set to "Started Work", and the second call to add a new row with ExtraIngredient2 set to a number value (representing the number of hours I want to add to the date value the second time the IFTTT recipe is called) (and Ingredient 1 set to "Finished Work").

Thereby allowing me to immediately make two entries in my timesheet two hours apart, the first with the current time, the second with two hours from now. (You could obviously do whatever math you want to here).

In doing so, I expanded the IFTTT cell math to account for this, allowing me to add hours in the second call (as I want to make a start and end entry into my timesheet spreadsheet, two hours apart). Google Sheets' math requires that this hour value applied to a date field be divided by 24 hours.

In the end, this looks like the following under the "Formatted row" portion of the recipe in my IFTTT:

{{ExtraIngredient1}} ||| =SUBSTITUTE("{{RunAt}}", " at ", " ")+({{ExtraIngredient2}}/24) ||| "Manually Entered"

The final prepwork is to set the entire column of values in your Google Sheets spreadsheet to be formatted in the DATE format you'd like to see, otherwise you merely get a decimal value, which of course looks odd. Or do additional massaging in additional fields.

Also, don't forget the ARRAYFORMULA function in Google. Very useful for auto-massaging entire columns of data.

Cheers!
(Edited)
Photo of Amy

Amy

  • 68,970 Points 50k badge 2x thumb
Thanks, NinkasiSwain. And welcome to the community! 
Photo of Tom

Tom

  • 60 Points
I would like to see my driving data on a spreadsheet and am no programmer. Is there something I can buy to sync with google sheets with pre-set formulas? 
Photo of Derrick S.

Derrick S., Official Rep

  • 7,354 Points 5k badge 2x thumb
Hi Tom,

You can export your trip data to a .CSV file using the Web Dashboard: https://dashboard.automatic.com/#/ No programming required, but keep in mind that you can only load about a month's worth of trip data at a time.