Invalid DateTime on Postgres -> Google Sheets flow

[Developer]

Describe the problem/error/question

I’m unable to parse and format a date that I know exists in a ISO format into a Google Sheet.

What is the error message (if any)?

The error message Invalid DateTime appears for the keys appointment_date and lead_submitted_date, and this appears in all rows when the Google Sheets node finishes.

Here’s the JS expression being used:

{{ $json.appointment_date_est ? DateTime.fromISO($json.appointment_date_est, {zone: 'utc'}).format('yyyy-LL-dd') : null }}

{{ $json.lead_submitted_date_est ? DateTime.fromISO($json.lead_submitted_date_est, {zone: 'utc'}).format('yyyy-LL-dd') : null }}

This whole thing works when I individually test only Google Sheets node, but it doesn’t work when I test the full workflow. I’m not sure what I’m missing. I tried adding a wait node before Google Sheets, thinking the data was somehow delayed, but this didn’t solve anything.

I recently updated to 1.75.2 and I’m not sure if there was a regression or something. I’m not able to roll back.

Please share your workflow

Share the output returned by the last node

The output looks like this, an array of objects of the following shape:

{
"surrogate_key": "key",
"appointment_id": 1,
"user_id": 1,
"appointment_date": "Invalid DateTime",
"lead_submitted_date": "Invalid DateTime",
"first_name": "Test",
"last_name": "Test",
"email": "[email protected]",
"dietitian_name": "Test",
"tracking_data_json": {
"utm_source": "test",
"utm_medium": "email",
"utm_campaign": "test"
},
"traffic_channel": "ads",
"appointment_type_name": "test",
"appointment_status": "test",
"contact_type": "test"
}

The input looks like this, an array of objects of the following shape:

{
"surrogate_key": "key",
"appointment_id": 1,
"user_id": 1,
"first_name": "Test",
"last_name": "Test",
"email": "[email protected]",
"appointment_date_est": "2024-10-31T00:00:00.000Z",
"lead_submitted_date_est": "2024-10-27T00:00:00.000Z",
"dietitian_name": "Test",
"tracking_data_json": {
"utm_source": "test",
"utm_medium": "email",
"utm_campaign": "test"
},
"traffic_channel": "ads",
"appointment_type_name": "test",
"appointment_status": "test",
"contact_type": "test"
},

Information on your n8n setup

• n8n version: 1.75.2
• Running n8n via (Docker, npm, n8n cloud, desktop app): cloud
• Database (default: SQLite): n/a
• n8n EXECUTIONS_PROCESS setting (default: own, main): n/a
• Operating system: n/a

Thanks

Any help, recommendation or info is appreciated

[Developer]

I’m starting to think this is a bug

[gualter]

hi @Developer

I made a quick test and this way it works:

{{  $json.appointment_date_est ? DateTime.fromISO( $json.appointment_date_est).setLocale('utc').format('yyyy-LL-dd') : null }}

Can you try it this way?

[Developer]

Thanks @gualter !

I just tested the proposed solution and I couldn’t make it work:

Screenshot shows one full test. It does work when I individually and manually test the single node about Google Sheets, but it doesn’t work on the full workflow test.

For additional context this happens for all values, not just one-offs of data:

What other info could I provide to further debug this issue?

I appreciate the help

[gualter]

hello

On my example, I tested with your data (nevermind the extra myNewField) and it works fine:

The sheet is updated. Can you try setting up a new test sheet on a different file and see if that works? Could be something on that particular spreadsheet.