Make IFTTT Date Format play nice with Google Spreadsheets

IFTTTDate.md

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

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

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.

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!

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

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.

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

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

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?

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

but I had to format the column as such:

from today dont work !

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

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

👍 🙏

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

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

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

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

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

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!

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:

\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());
  }
}

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