Skip to content

Instantly share code, notes, and snippets.

@oscarmorrison
Last active December 3, 2023 21:44
Show Gist options
  • Star 73 You must be signed in to star a gist
  • Fork 8 You must be signed in to fork a gist
  • Save oscarmorrison/bf14dab89854238d4cca to your computer and use it in GitHub Desktop.
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

@iluc71
Copy link

iluc71 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
Copy link

breedus commented Feb 4, 2017

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

@breedus
Copy link

breedus 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
Copy link

jajube 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
Copy link

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
Copy link

likwah 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
Copy link

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

image

but I had to format the column as such:

image

@umbertog21
Copy link

from today dont work !

Copy link

ghost 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
Copy link

jfretin commented Mar 20, 2018

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

@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