What product/components do you use and which version/fix level?

Webmethouds.io Integration

Are you using a free trial or a product with a customer license?

Forever free trial.

What are trying to achieve? Please describe in detail.

I successfully used the Google Calendar Events from Google sheets recipe:
https://env721092.int-aws-de.webmethods.io/#/recipes/fl57ae98d1b587bdc2864a7c/workflow/preview

With some tweaking, I was able to get the system to post an event in a sample Google calendar.

The date format turns out to be super tricky, as it expects the following format:
2022-04-28T13:00:00.000Z

My first question is how can I set up the call, so that no matter what format the worksheet has the date/time is transferred correctly for creating that calendar entry.

The second question is: How can I prevent the system from losing the startdate and enddate parameters?

I created a Loom video that I hope makes the above questions a bit clearer:

Looking forward to your answers, Mark.

P.S. The worksheet is available here: New WebMethods Google Worksheet -> Calendar Integration Test - Google Sheets

Does anyone know of a way to access Google Sheet date/time in a way so it doesn’t matter which format the date/time has been posted in the cell?

At the moment for this workflow/API call to work it needs to be posted like this: 2022-05-02T13:00:00.000Z
Which is really hard to read/decipher.

What I would like for it not to matter how the date/time is displayed in the worksheet: 1pm PST May 5th 2022 for example.

The workflow grabs ideally how the date/time is formatted natively in Google sheets and transforms it so that Google Calendar can understand it when the workflow calls that API.

The worksheet is here: New WebMethods Google Worksheet -> Calendar Integration Test - Google Sheets

More background in the post above.

Let me know, Mark.

One scenario:In input fetch current date output should be next +2days ex:-if input 16 may 2022 then ouput is 18 may 2022…if output comes on weekends(treat Saturday and Sunday as weekends) then output should be next working day like monday

Hi Haseem,

You describe an interesting scenario, where your API call is manipulating the date of the calendar entry to always be on a workday two days out.

I am trying to solve a problem before that:

How can I enter a day/time into a google sheet cell and no matter what date/time format I choose to post in that cell, the API call is able to interpret that date/time in the correct way to create the calendar entry correctly including time zone and sommer/winter time adjustment?

Once that is solved we can tackle the next scenarios.

Still hoping for an elegant solutions

Hi Mark,

I think what you’re asking for is an extension to the connector to allow you to get the raw data back. I.e. you have a date column where everything is formatted as a date, but the text shows 21 February 2022 and 23/02/2022 in another column.

When you query these in wm.io integration the formatted dates are returned, which are really pretty much as you see them in the sheet, but actually they’re a date so have a number behind them which allows you to do date subtraction/addition, etc.

The google sheets API has:

valueRenderOption
How values should be represented in the output. The default render option is FORMATTED_VALUE

dateTimeRenderOption:
How dates, times, and durations should be represented in the output. This is ignored if valueRenderOption is FORMATTED_VALUE . The default dateTime render option is SERIAL_NUMBER .

But you cannot control these from within the connector call currently.

If you use the API and pick unformatted/serial number you’ll get the serial-number back not the formatted value - Is that a correct understanding of what you’re looking for? (I notice in your sheet, they’re not even formatted as a date).

The downside to this is google sheets uses an unusual date representation (like excel), which means you’d have to convert the serial number to something else for a downstream call.

Most APIs tend to handle dates in the ISO8601 format you’re looking at.

See the difference here from the possible API Responses:

Unformatted - Serial Number (Column C from your Google Sheet):

{
"range": "Sheet1!C2:C6",
"majorDimension": "ROWS",
"values": [
[
44679.541666666664
],
[
44680.541666666664
],
[
44681.541666666664
],
[
44683.541666666664
],
[
44686.541666666664
]
]
}

Formatted (Formatted_Value) (Column C from your Google Sheet):

{
"range": "Sheet1!C2:C6",
"majorDimension": "ROWS",
"values": [
[
"2022-04-28T13:00:00.000Z"
],
[
"2022-04-29T13:00:00.000Z"
],
[
"2022-04-30T13:00:00.000Z"
],
[
"2022-05-02T13:00:00.000Z"
],
[
"2022-05-05T13:00:00.000Z"
]
]
}

Well, as Sheets and Calendar are both Google properties, I thought one should be able to understand the date formatting of the other.
That wasn’t the case here. So I tinkered with the format in the cell of the Google sheet, until it worked for Google Calendar. It needed to be super precise: 2022-05-05T13:00:00.000Z down to twice “:” and then a dot “.”.

In the worksheet I want to display the date/time in whatever format I like to display it: 5/5/2022 13:00:00 or Thursday, May 5, 2022 at 1:00:00 PM the API call shouldn’t care and still post the calendar entry at the right time/date including time zones.

What do I need to change in my API call to allow for the above formatting flexibility in the worksheet?

It’s a bit of a painful part of the google sheet APIs ultimately.

From my testing, even formatted as a date, you can input in any format it recognizes, but the only way to retrieve the consistent value (From the API which our connector uses) is to use the serial_number format and unfortunately this connector doesn’t provide this as an option right now.

I have a potential alternative approach which should be within your power - why not create another column in the sheet and use a formula in this column to convert the date formatted value into an ISO date format string (2022-05-05T13:00:00.000Z). This should then give a consistent representation when you use the connector for that column, which you can then use downstream, but still allow the input data to be date formatted, and specified in any format Google Sheets accepts.

I’ve not tested this, but don’t see why it wouldn’t work.

Fyi the serial number thing has it’s origin back in lotus1-2-3 (for those of us who remember this )

Thanks! That is a good workaround. I will test it out soon.
Love the Lotus 1-2-3 reference