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

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