Connect google sheet to calendar

[Ron_Adames]

Good morning community,
I’m new on n8n even I can understand the basic and blocked trying to connect google sheet with google calendar:
The idea is creating an event with: Title, description with 1 hour of duration, but I get it to work since google calendar requiere a starting date and ending date. So I don’t know how to proccess or format that data and get it to calendar.

I hope to find a solution with you.
Thanks

[MutedJam]

Hi @Ron_Adames, welcome to the community

The idea is creating an event with: Title, description with 1 hour of duration, but I get it to work since google calendar requiere a starting date and ending date. So I don’t know how to proccess or format that data and get it to calendar.

So your existing Google Sheet has columns such as Title and Description, with the start and end date being part of the description rather than existing on their own? If so, you’d need to isolate these values in a first step to then use them with Google Calendar.

Perhaps you can provide a few example rows from your Google Sheet so I can have a go at coming up with an example?

[Ron_Adames]

Thank you so much for taking time to answer.
Here is the idea, I don’t know if this call tell you more.
The google sheets has Date, Time, Tittle and Description, but even if I put and Starting and Ending I will need the calculation for the calendar. That’s my problem.

[MutedJam]

Thanks @Ron_Adames!

From looking at your last screenshot it seems you’re receiving the timestamps from Google Sheets in an ISO format already:

This is good as it means we can easily parse such values and perform calculations on them. The next good news is that your Time field also includes a date portion, so we don’t need to worry about the Date field.

So with the exception of the end date we can just reference data from the Sheets node in the Google Calendar node using expressions. Here is an example workflow where I am using Luxon to read the example Time value you have provided to determine a start and end date in the Google Calendar node:

I am only using the “Summary” field of Google Calendar here (and write your Title value in it), but you can simply click the “Add Field” button to add more fields as needed.

[Ron_Adames]

Dear @MutedJam,

Thank you so much for helping me with this. I also want to add that with these function I could solved how to make appointments using Dialogflow Chatbot and send it to Google Sheets as Data Base and Google Calendar to get the appointments. I looked for this in the community and not results till now, so I wanted to point it! The only thing I’m missing is to return the json to Dialogflow, but I will figure it out.

Thank you again and Happy Holiays!

This is the result:

[MutedJam]

Glad to hear this helped @Ron_Adames! I have not used Dialogflow, but it seems their webhooks do accept a response with a number of optional fields. So you can probably use the Respond to Webhook node for this which allows you to send a response at any point in your workflow.

[Ron_Adames]

Thank you @MutedJam
I solved it with some info plus this solution from @jan
I will let the workflow node for the community on here, let me know if it’s right the way I copied.
I’d like it to look like with graphic way and move it to Tips and Tricks.

Dialogflow and Google Calendar with Webhook Response

{
“meta”: {
“instanceId”: “91d2d6c5d1971543d6e4e7f3fc1a445192a3a0de2db90de9e2a7daa42162d0a2”
},
“nodes”: [
{
“parameters”: {
“httpMethod”: “POST”,
“path”: “f912b54f-67e8-47af-9d6b-2e96b004d4bb”,
“responseMode”: “lastNode”,
“options”: {}
},
“name”: “Webhook”,
“type”: “n8n-nodes-base.webhook”,
“typeVersion”: 1,
“position”: [
-1760,
-40
],
“webhookId”: “d80e66d7-6c02-44ae-9fd1-c207bb963625”,
“id”: “724e3c92-5af2-4ddb-a9e0-b38d749c5135”
},
{
“parameters”: {
“functionCode”: “return [\n {\n json: {\n "fulfillmentMessages": [\n {\n "text": {\n "text": [\n $json.text\n ]\n }\n }\n ]\n }\n }\n];”
},
“name”: “Response to Dialogflow”,
“type”: “n8n-nodes-base.function”,
“typeVersion”: 1,
“position”: [
-660,
-40
],
“id”: “23ab48bb-38de-46ee-829c-99e9190f8ed7”
},
{
“parameters”: {
“values”: {
“string”: [
{
“name”: “text”,
“value”: “={{ $json["body"]["queryResult"]["outputContexts"][0]["parameters"]["nombre"] }}, gracias por la informacion. Te confirmo tu {{$json["body"]["queryResult"]["outputContexts"][0]["parameters"]["motivo"]}} para el {{ $json["body"]["queryResult"]["outputContexts"][0]["parameters"]["hora"] }}”
}
]
},
“options”: {}
},
“name”: “Set Confirmation”,
“type”: “n8n-nodes-base.set”,
“typeVersion”: 1,
“position”: [
-1560,
-40
],
“id”: “5711d0a3-a414-48ad-bb45-2c46c941ad82”
},
{
“parameters”: {
“functionCode”: “return [\n {\n json: {\n "fulfillmentMessages": [\n {\n "text": {\n "text": [\n $json.text\n ]\n }\n }\n ]\n }\n }\n];”
},
“name”: “Response 1”,
“type”: “n8n-nodes-base.function”,
“typeVersion”: 1,
“position”: [
-1380,
-40
],
“id”: “9eb55194-1dee-47b1-881e-9374a00f002c”
},
{
“parameters”: {
“values”: {
“string”: [
{
“name”: “Hora”,
“value”: “={{ $node["Set Confirmation"].json["body"]["queryResult"]["outputContexts"]["0"]["parameters"]["hora"] }}”
},
{
“name”: “Motivo”,
“value”: “={{ $node["Set Confirmation"].json["body"]["queryResult"]["parameters"]["motivo"] }}”
},
{
“name”: “Nombre”,
“value”: “={{ $node["Set Confirmation"].json["body"]["queryResult"]["parameters"]["nombre"] }}”
}
]
},
“options”: {}
},
“id”: “294dc727-bec3-4eca-b5f9-036672941d04”,
“name”: “Set info for Calendar”,
“type”: “n8n-nodes-base.set”,
“typeVersion”: 1,
“position”: [
-1200,
-40
]
},
{
“parameters”: {
“calendar”: {
“__rl”: true,
“value”: “ea9f557d8364c20d2f995c07a4[email protected]”,
“mode”: “list”,
“cachedResultName”: “Test”
},
“start”: “={{ DateTime.fromISO($json.Hora).toISO() }}”,
“end”: “={{ DateTime.fromISO($json.Hora).plus({hours: 1}).toISO() }}”,
“additionalFields”: {
“attendees”: [],
“description”: “={{ $json["Motivo"] }}”,
“summary”: “={{ $json["Nombre"] }}”
}
},
“id”: “658419e8-ff9a-44ac-8076-e914bd7c9d1d”,
“name”: “Create Appointment”,
“type”: “n8n-nodes-base.googleCalendar”,
“typeVersion”: 1,
“position”: [
-1020,
-40
],
“credentials”: {
“googleCalendarOAuth2Api”: {
“id”: “2”,
“name”: “Google Calendar account”
}
}
},
{
“parameters”: {
“keepOnlySet”: true,
“values”: {
“string”: [
{
“name”: “text”,
“value”: “={{ $json["summary"] }}, tu {{$node["Webhook"].json["body"]["queryResult"]["outputContexts"][0]["parameters"]["motivo.original"] }} para {{ $node["Webhook"].json["body"]["queryResult"]["outputContexts"][0]["parameters"]["fecha.original"] }} a las {{ $node["Webhook"].json["body"]["queryResult"]["outputContexts"][0]["parameters"]["hora.original"] }} ha sido confirmada.”
}
]
},
“options”: {}
},
“id”: “a575726f-a37e-451b-9cb2-4ccdf53176aa”,
“name”: “Extract Info from Calendar”,
“type”: “n8n-nodes-base.set”,
“typeVersion”: 1,
“position”: [
-840,
-40
]
}
],
“connections”: {
“Webhook”: {
“main”: [
[
{
“node”: “Set Confirmation”,
“type”: “main”,
“index”: 0
}
]
]
},
“Set Confirmation”: {
“main”: [
[
{
“node”: “Response 1”,
“type”: “main”,
“index”: 0
}
]
]
},
“Response 1”: {
“main”: [
[
{
“node”: “Set info for Calendar”,
“type”: “main”,
“index”: 0
}
]
]
},
“Set info for Calendar”: {
“main”: [
[
{
“node”: “Create Appointment”,
“type”: “main”,
“index”: 0
}
]
]
},
“Create Appointment”: {
“main”: [
[
{
“node”: “Extract Info from Calendar”,
“type”: “main”,
“index”: 0
}
]
]
},
“Extract Info from Calendar”: {
“main”: [
[
{
“node”: “Response to Dialogflow”,
“type”: “main”,
“index”: 0
}
]
]
}
}
}