Get the date to copy to google calendar

AppSheet AppSheet Q&A
,
1

I created action, ‘Add to GCal’. It is a button on a order.
If the appointment date is filled up, the button appears and then when clicked will be redirected to google calendar.
I so far managed to get the name, remarks and location from appsheet automatically copied to the form through this code:

CONCATENATE(
“https://www.google.com/calendar/render?action=TEMPLATE&text=”,
ENCODEURL([ContactRec].[Name]),
“&dates=”,
ENCODEURL([TIME FORMAT FOR GCAL]),
“&details=”,
ENCODEURL("Remarks: “), ENCODEURL( [Remarks]), ENCODEURL(” Next Topic: "), ENCODEURL([Next_Topic]),
“&location=”,
ENCODEURL([ContactRec].[Territory]),
“&sf=true&output=xml”
)

I am simply unable to grab the date. It must be the date format.

I tried this:
TEXT([AppointmentDate], “YYYY/DD/MM/HH/MM/SS”)

2

Ivy_Private:

I am simply unable to grab the date.

What do you mean by this? Does your data include the date and time you need? Do you need the current date and time? Please clarify.

1 Like
3

Instead of TEXT() use YEAR(), MONTH() and DAY() functions to get the desired format.

What you need to pass is the ISO format - I got this from stack overflow:

  • dates (ISO date format, startdate/enddate - must have both start and end time or it won’t work. The start and end date can be the same if appropriate.)
    • to use the user’s timezone: 20161208T160000/20161208T180000
    • to use global time, convert to UTC, then use 20131208T160000Z/20131208T180000Z
    • all day events, you can use 20161208/20161209 - note that the old google documentation gets it wrong. You must use the following date as the end date for a one day all day event, or +1 day to whatever you want the end date to be.
4 Likes
4

Bellave_Jayaram:

T

I feel like this could be my answer, how would I put it as my formula though:

I tried this and it isn’t working.
CONCATENATE(YEAR([AppointmentDate]),MONTH([AppointmentDate]),DAY([AppointmentDate]),“T”,HOUR(“002:00:00”),MINUTE(“002:00:00”),SECOND(“002:00:00”)"/"YEAR([AppointmentDate]),MONTH([AppointmentDate]),DAY([AppointmentDate]),“T”,HOUR(“002:00:00”),MINUTE(“002:00:00”),SECOND(“002:00:00”))

AppointmentDate is where the date is.

5

Yes, I need the date in the format it needs to be so I can add it too google calendar.
Like this: 20161208T160000/20161208T180000

I simply do not know how, I tried this formula:
CONCATENATE(YEAR([AppointmentDate]),MONTH([AppointmentDate]),DAY([AppointmentDate]),“T”,HOUR(“002:00:00”),MINUTE(“002:00:00”),SECOND(“002:00:00”)"/"YEAR([AppointmentDate]),MONTH([AppointmentDate]),DAY([AppointmentDate]),“T”,HOUR(“002:00:00”),MINUTE(“002:00:00”),SECOND(“002:00:00”))

6

Try:

SUBSTITUTE(
  "{}/{}",
  "{}",
  CONCATENATE(
    TEXT(
      [AppointmentDate],
      "YYYYMMDD"
    ),
    "T",
    TEXT(
      "02:00:00,
      "HHMMSS"
    )
  )
)

See also:

4 Likes
7

Steve:

auto> SUBSTITUTE(> "{}/{}",> "{}",> CONCATENATE(> TEXT(> [AppointmentDate],> "YYYYMMDD"> ),> "T",> TEXT(> "02:00:00,> "HHMMSS"> )> )> )> >

Perfect! I have not tried Substitute before. Works like a charm

2 Likes