javascriptgoogle-sheetsifttt

How can I apply a formula to a Google Sheet row only after a row has been updated?

I'm using IFTTT to add rows to a Google Sheet based on new Google Calendar Events. When a Google Calendar Event is added, IFTTT adds a new row to a Google Sheet. Column A is the StartTime from the Google Calendar Event, which is formatted as "March 31, 2021 at 08:30PM". Column B is the Description from the Google Calendar Event, which I use to pass a telephone number in the format 12145551212.

For Column C, I would like to use a formula that uses a regular expression to take the StartTime, subtract a day, and only make use of the date, so it would read "March 30, 2021. Column D would use a formula that uses a regular expression to parse out the time, so it would read "08:30PM" My formulas I'm trying to use are "=REGEXREPLACE(A2, " at.$",)-1" and "=REGEXREPLACE(A2, "^. at ",)"

My problem is that the C and D columns must appear empty until columns A and B are added. If a formula is already in the row, IFTTT enters the new row on the first row that is totally clear. It bypasses my formulas this way if I simply copy a formula all the way down the column. I tried passing the actual formula from IFTTT to Google Sheets with "{{StartTime}}|||{{Description}}|||=REGEXREPLACE(A2, " at.$",)-1|||=REGEXREPLACE(A2, "^. at ",)" but the regex formulas did not end up being entered in Sheets and those cells were left blank.

This is all in an effort to use an SMS app (ClickSend) to send out appointment reminders 24 hours before an appointment that gets entered into a new Google Calendar Event. New Calendar Event > IFTTT > Google Sheet > IFTTT > New Calendar Event (trigger) > IFTTT > Send SMS appointment reminder at Start-time of trigger Calendar Event. It would be nice to cut out the spreadsheet entirely. Unfortunately, the IFTTT Google Calendar interface, does not seem to have a way to make a new calendar event based on a new event minus 24 hours while also passing the description field.

If someone knows a better way to accomplish this without being a fluent coder (I try, but am not great), I'm all ears.

Solution

  • You'll have to use Arryaformula() to achieve that. This formula

    =ArrayFormula(iferror(if(len(A2:A), split(A2:A, " at ", 0),)))

    entered in row 2 will create an output of two columns when there is a value in column A. You would have to format the output columns as you wish (date and time formats).

    enter image description here

    Note that you can also choose to 'hide' the formula behind headers in row 1.

    ={"Date", "Time" ; ArrayFormula(iferror(if(len(A2:A), split(A2:A, " at ", 0),)))}

    I hope that helps?

    REFERENCE