Schedules with google

For requests or help with our API
Post Reply
User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Schedules with google

Post by jordansparks » Fri Mar 25, 2022 7:32 am

I got the following as a private msg. Posting it here for everyone:

Here is the n8n / Javascript to perform a google calendar update w/o webhooks. I would like to reuse this code for schedules which requires the GET schedules/xxx for deletion. Google calendar will then update calendly. Code below.

Joerg


1st snipped: retrieve Apts subroutine used by Google Calendar update.

Code: Select all

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "filePath": "/home/node/.n8n/od_sync/timestamp.bin"
      },
      "name": "Read Apt DateTStamp last access",
      "type": "n8n-nodes-base.readBinaryFile",
      "typeVersion": 1,
      "position": [
        440,
        300
      ]
    },
    {
      "parameters": {
        "options": {}
      },
      "name": "Convert to JSON",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        640,
        300
      ]
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        860,
        320
      ]
    },
    {
      "parameters": {
        "value": "={{ new Date(new Date().getTime()) }}",
        "dataPropertyName": "DateToday",
        "toFormat": "YYYY-MM-DD",
        "options": {}
      },
      "name": "Today",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        640,
        500
      ]
    },
    {
      "parameters": {
        "mode": "jsonToBinary",
        "options": {
          "mimeType": "application/json"
        }
      },
      "name": "Move Binary Data",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        2420,
        300
      ]
    },
    {
      "parameters": {
        "operation": "sort",
        "sortFieldsUi": {
          "sortField": [
            {
              "fieldName": "DateTStamp",
              "order": "descending"
            }
          ]
        },
        "options": {}
      },
      "name": "Sort by DateTStamp",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        1980,
        300
      ],
      "continueOnFail": true
    },
    {
      "parameters": {
        "functionCode": "// only retrieve future appointments with updated last acces DateTStamp\n// this is to avoid a full update after open dental software upgrade that involves touching all mysql tables\nlet baseurl = 'https://api.opendental.com/api/v1/appointments?Limit=50&dateStart='+$json[\"DateToday\"]+'&DateTStamp='+$json[\"DateTStamp\"]\n\n//Alternate URL for testing\n//let baseurl = 'https://api.opendental.com/api/v1/appointments?Limit=50&DateTStamp='+$json[\"DateTStamp\"]\n//let baseurl = 'https://api.opendental.com/api/v1/appointments?Limit=50&dateStart=2020-10-27&dateEnd=2022-02-09'+'&DateTStamp='+$json[\"DateTStamp\"]\n\nif (!items[0].json.offset) {\n  offset = 0\n} else\noffset = items[0].json.offset;\n\nreturn [\n    {\n      json: {\n        url : baseurl+'&Offset='+offset.toString(),\n        offset: items[0].json.offset\n      }\n    }\n]"
      },
      "name": "Create URL",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1060,
        320
      ]
    },
    {
      "parameters": {
        "authentication": "headerAuth",
        "url": "={{$node[\"Functionx\"].json[\"url\"]}}{{$json[\"url\"]}}",
        "options": {
          "splitIntoItems": true
        }
      },
      "name": "OD API Get multiple",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        1280,
        320
      ],
      "alwaysOutputData": false,
      "notesInFlow": true,
      "credentials": {
        "httpHeaderAuth": {
          "id": "5",
          "name": "Header Auth KL Office"
        }
      },
      "notes": "For production use Date_Stop and DateTStamp in query."
    },
    {
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$items().length}}",
              "value2": 50
            }
          ]
        }
      },
      "name": "Loop on pagination",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1500,
        320
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "number": [
            {
              "name": "offset",
              "value": "={{($runIndex+1)*50}}"
            }
          ],
          "string": [
            {
              "name": "DateTStamp",
              "value": "={{$node[\"Merge\"].json[\"DateTStamp\"]}}"
            },
            {
              "name": "DateToday",
              "value": "={{$node[\"Merge\"].json[\"DateToday\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set Offset&Save Request parameters",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1760,
        480
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "functionCode": "const allData = []\n\nlet counter = 0;\ndo {\n  try {\n    const aja = $items(\"OD API Get multiple\",0,counter);                   \n    allData.push.apply(allData, aja);\n  } catch (error) {\n    return allData;  \n  }\n  counter++;\n} while(true);\n\n"
      },
      "name": "Merge Pagination Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1760,
        300
      ]
    },
    {
      "parameters": {
        "authentication": "headerAuth",
        "url": "=https://api.opendental.com/api/v1/patients/{{$json[\"PatNum\"]}}",
        "options": {
          "splitIntoItems": false
        }
      },
      "name": "OD Get Patient Info by ID",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        2900,
        480
      ],
      "alwaysOutputData": false,
      "credentials": {
        "httpHeaderAuth": {
          "id": "5",
          "name": "Header Auth KL Office"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge Apts and Patient Info",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        3100,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "// Code here will run once per input item.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.functionItem\nconst moment = require('moment');\n\n// convert Appointement Time with local timezone to ISO UTC\nvar AptStartLocalTZ = moment.tz($json[\"AptDateTime\"], $env['GENERIC_TIMEZONE']);\nitem.AptStartISOUTC = new Date(AptStartLocalTZ);\n\n// calculate Appointement Ending Time in UTC\nitem.AptEndISOUTC = new Date(item.AptStartISOUTC);\nitem.AptEndISOUTC.setSeconds(item.AptEndISOUTC.getSeconds() + (5*60*$json[\"Pattern\"].length));\n\n// create Opaque Google id\nitem.AptGoogleID = \"opendent\"+$json[\"AptNum\"];\n\nreturn item;"
      },
      "name": "OD Apt to Google Format",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        3320,
        300
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "DateTStamp",
              "value": "={{$json[\"DateTStamp\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": true
        }
      },
      "name": "Select latest DateTStamp",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        2200,
        300
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "fileName": "/home/node/.n8n/od_sync/timestamp_OD.bin"
      },
      "name": "Write Binary File OD Timestamp",
      "type": "n8n-nodes-base.writeBinaryFile",
      "typeVersion": 1,
      "position": [
        2640,
        300
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "operation": "removeDuplicates",
        "compare": "selectedFields",
        "fieldsToCompare": {
          "fields": [
            {
              "fieldName": "AptNum"
            }
          ]
        },
        "options": {
          "removeOtherFields": false
        }
      },
      "name": "Remove Duplicate AptNum",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        2200,
        480
      ]
    },
    {
      "parameters": {
        "keys": {
          "key": [
            {
              "currentKey": "DateTStamp",
              "newKey": "LastAccess_DateTStamp"
            }
          ]
        }
      },
      "name": "Rename Keys",
      "type": "n8n-nodes-base.renameKeys",
      "typeVersion": 1,
      "position": [
        860,
        140
      ],
      "notesInFlow": true,
      "notes": "Do not rename, used by SetLastaccess on all items."
    },
    {
      "parameters": {
        "functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\n\nfor (var i = 0; i < items.length;) {\n  if ((items[i].json.LastAccess_DateTStamp)==(items[i].json.DateTStamp)) {\n    items.splice(i,1);\n  } \n    else i++;\n}\n\nreturn items;\n"
      },
      "name": "Keep only new items",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        2640,
        480
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "LastAccess_DateTStamp",
              "value": "={{$item(0).$node[\"Rename Keys\"].json[\"LastAccess_DateTStamp\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set LastAccess on all items",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        2420,
        480
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Read Apt DateTStamp last access",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Apt DateTStamp last access": {
      "main": [
        [
          {
            "node": "Convert to JSON",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert to JSON": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          },
          {
            "node": "Rename Keys",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "Create URL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Today": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Move Binary Data": {
      "main": [
        [
          {
            "node": "Write Binary File OD Timestamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sort by DateTStamp": {
      "main": [
        [
          {
            "node": "Select latest DateTStamp",
            "type": "main",
            "index": 0
          },
          {
            "node": "Remove Duplicate AptNum",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create URL": {
      "main": [
        [
          {
            "node": "OD API Get multiple",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OD API Get multiple": {
      "main": [
        [
          {
            "node": "Loop on pagination",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Loop on pagination": {
      "main": [
        [
          {
            "node": "Merge Pagination Data",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Set Offset&Save Request parameters",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Offset&Save Request parameters": {
      "main": [
        [
          {
            "node": "Create URL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge Pagination Data": {
      "main": [
        [
          {
            "node": "Sort by DateTStamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OD Get Patient Info by ID": {
      "main": [
        [
          {
            "node": "Merge Apts and Patient Info",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Merge Apts and Patient Info": {
      "main": [
        [
          {
            "node": "OD Apt to Google Format",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Select latest DateTStamp": {
      "main": [
        [
          {
            "node": "Move Binary Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Remove Duplicate AptNum": {
      "main": [
        [
          {
            "node": "Set LastAccess on all items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Keep only new items": {
      "main": [
        [
          {
            "node": "OD Get Patient Info by ID",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge Apts and Patient Info",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set LastAccess on all items": {
      "main": [
        [
          {
            "node": "Keep only new items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
2nd snipped: Update Google Calendar:

Code: Select all

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        220,
        300
      ]
    },
    {
      "parameters": {
        "operation": "get",
        "calendar": "srmgug2008lngoep8d3o49fcmg@group.calendar.google.com",
        "eventId": "={{$node[\"Save ID\"].json[\"AptGoogleID\"]}}",
        "options": {}
      },
      "name": "Google Calendar Get Event By ID",
      "type": "n8n-nodes-base.googleCalendar",
      "typeVersion": 1,
      "position": [
        900,
        60
      ],
      "alwaysOutputData": false,
      "credentials": {
        "googleCalendarOAuth2Api": {
          "id": "6",
          "name": "Google Calendar Judith Yang"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "calendar": "srmgug2008lngoep8d3o49fcmg@group.calendar.google.com",
        "start": "={{$json[\"AptStartISOUTC\"]}}",
        "end": "={{$json[\"AptEndISOUTC\"]}}",
        "additionalFields": {
          "attendees": [],
          "description": "=Procedure: {{$json[\"ProcDescript\"]}}",
          "id": "={{$json[\"AptGoogleID\"]}}",
          "summary": "={{$json[\"AptDateTime\"].substring(11,16)}} {{$json[\"FName\"]}} {{$json[\"LName\"].substring(0,1)}}. {{$json[\"Note\"].substring(0,700)}}"
        }
      },
      "name": "Google Calendar Create Event",
      "type": "n8n-nodes-base.googleCalendar",
      "typeVersion": 1,
      "position": [
        2000,
        120
      ],
      "retryOnFail": true,
      "waitBetweenTries": 5000,
      "credentials": {
        "googleCalendarOAuth2Api": {
          "id": "6",
          "name": "Google Calendar Judith Yang"
        }
      }
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"status\"]}}",
              "value2": "cancelled"
            }
          ]
        }
      },
      "name": "Google Calendar Event cancelled?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1560,
        260
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"id\"]}}",
              "operation": "isEmpty"
            }
          ]
        }
      },
      "name": "Google Calendar Event non-existent?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1780,
        280
      ]
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge Google Events and OD Apts",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        900,
        280
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        1120,
        280
      ],
      "notesInFlow": true,
      "notes": "Don't rename / Used by \"If\""
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": true,
              "value2": "={{$node[\"SplitInBatches\"].context[\"noItemsLeft\"]}}"
            }
          ]
        }
      },
      "name": "IF items left",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        2400,
        300
      ]
    },
    {
      "parameters": {},
      "name": "Save ID",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        640,
        300
      ]
    },
    {
      "parameters": {
        "workflowId": "10"
      },
      "name": "Retrieve Apt Update",
      "type": "n8n-nodes-base.executeWorkflow",
      "typeVersion": 1,
      "position": [
        440,
        300
      ]
    },
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "mode": "everyX",
              "value": 5,
              "unit": "minutes"
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        220,
        60
      ]
    },
    {
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$json[\"ProvNum\"]}}",
              "operation": "equal",
              "value2": 1
            }
          ]
        }
      },
      "name": "Select Provider 1",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1300,
        280
      ]
    },
    {
      "parameters": {
        "operation": "update",
        "calendar": "srmgug2008lngoep8d3o49fcmg@group.calendar.google.com",
        "eventId": "={{$json[\"id\"]}}",
        "updateFields": {
          "description": "=Procedure: {{$json[\"ProcDescript\"]}}",
          "end": "={{$json[\"AptEndISOUTC\"]}}",
          "start": "={{$json[\"AptStartISOUTC\"]}}",
          "summary": "={{$json[\"AptDateTime\"].substring(11,16)}} {{$json[\"FName\"]}} {{$json[\"LName\"].substring(0,1)}}. {{$json[\"Note\"].substring(0,700)}}"
        }
      },
      "name": "Google Calendar Update Event (\"touch\")",
      "type": "n8n-nodes-base.googleCalendar",
      "typeVersion": 1,
      "position": [
        2000,
        300
      ],
      "retryOnFail": true,
      "waitBetweenTries": 5000,
      "credentials": {
        "googleCalendarOAuth2Api": {
          "id": "6",
          "name": "Google Calendar Judith Yang"
        }
      }
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "requestMethod": "PATCH",
        "url": "=https://www.googleapis.com/calendar/v3/calendars/srmgug2008lngoep8d3o49fcmg@group.calendar.google.com/events/{{$json[\"id\"]}}",
        "options": {},
        "bodyParametersUi": {
          "parameter": [
            {
              "name": "status",
              "value": "confirmed"
            }
          ]
        }
      },
      "name": "Try undo event cancellation",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        2220,
        -60
      ],
      "retryOnFail": true,
      "waitBetweenTries": 5000,
      "credentials": {
        "oAuth2Api": {
          "id": "7",
          "name": "Google Judith Yang"
        }
      },
      "notes": "https://stackoverflow.com/questions/18188681/undeleting-google-calendar-event/71283066#71283066\n\nThe patch operation on the status property seems to fail after deleting/cancelling an event with a 403 error on events that have been deleted some time ago. Setting the status to \"cancelled\" or deleting the event and then followed by setting the status to \"confirmed\" worked for me. This behavior is in accordance with the API documentation: \"Such cancelled events will eventually disappear, so do not rely on them being available indefinitely. Deleted events are only guaranteed to have the id field populated.\"."
    },
    {
      "parameters": {
        "command": "cp /home/node/.n8n/od_sync/timestamp_OD.bin /home/node/.n8n/od_sync/timestamp.bin"
      },
      "name": "Update last Apt DateTStamp",
      "type": "n8n-nodes-base.executeCommand",
      "typeVersion": 1,
      "position": [
        2620,
        280
      ]
    },
    {
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$json[\"Op\"]}}",
              "operation": "notEqual"
            }
          ],
          "string": [
            {
              "value1": "={{$json[\"AptStatus\"]}}",
              "operation": "notEqual",
              "value2": "Broken"
            }
          ]
        }
      },
      "name": "OD Apt really not cancelled?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        2000,
        -80
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Retrieve Apt Update",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Calendar Get Event By ID": {
      "main": [
        [
          {
            "node": "Merge Google Events and OD Apts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Calendar Create Event": {
      "main": [
        [
          {
            "node": "IF items left",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Calendar Event cancelled?": {
      "main": [
        [
          {
            "node": "OD Apt really not cancelled?",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Google Calendar Event non-existent?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Calendar Event non-existent?": {
      "main": [
        [
          {
            "node": "Google Calendar Create Event",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Google Calendar Update Event (\"touch\")",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge Google Events and OD Apts": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "Select Provider 1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF items left": {
      "main": [
        [
          {
            "node": "Update last Apt DateTStamp",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Save ID": {
      "main": [
        [
          {
            "node": "Merge Google Events and OD Apts",
            "type": "main",
            "index": 1
          },
          {
            "node": "Google Calendar Get Event By ID",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Retrieve Apt Update": {
      "main": [
        [
          {
            "node": "Save ID",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Cron": {
      "main": [
        [
          {
            "node": "Retrieve Apt Update",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Select Provider 1": {
      "main": [
        [
          {
            "node": "Google Calendar Event cancelled?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Calendar Update Event (\"touch\")": {
      "main": [
        [
          {
            "node": "IF items left",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Try undo event cancellation": {
      "main": [
        [
          {
            "node": "IF items left",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OD Apt really not cancelled?": {
      "main": [
        [
          {
            "node": "Try undo event cancellation",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "IF items left",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
3rd snipped: Delete Google calendar entries.

Code: Select all

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -640,
        520
      ]
    },
    {
      "parameters": {
        "authentication": "headerAuth",
        "url": "=https://api.opendental.com/api/v1/appointments/{{$json[\"GoogleID\"]}}",
        "options": {}
      },
      "name": "OD Apt Get by ID",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        860,
        140
      ],
      "credentials": {
        "httpHeaderAuth": {
          "id": "5",
          "name": "Header Auth KL Office"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "operation": "getAll",
        "calendar": "srmgug2008lngoep8d3o49fcmg@group.calendar.google.com",
        "returnAll": true,
        "options": {
          "updatedMin": "={{$json[\"DateTStamp\"]}}"
        }
      },
      "name": "Google Calendar Get All Events updated since yesterday",
      "type": "n8n-nodes-base.googleCalendar",
      "typeVersion": 1,
      "position": [
        440,
        320
      ],
      "notesInFlow": false,
      "credentials": {
        "googleCalendarOAuth2Api": {
          "id": "6",
          "name": "Google Calendar Judith Yang"
        }
      }
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge OD Apt and Google Event Info",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        1080,
        300
      ]
    },
    {
      "parameters": {
        "operation": "delete",
        "calendar": "srmgug2008lngoep8d3o49fcmg@group.calendar.google.com",
        "eventId": "={{$json[\"id\"]}}",
        "options": {}
      },
      "name": "Google Calendar Delete Event by ID",
      "type": "n8n-nodes-base.googleCalendar",
      "typeVersion": 1,
      "position": [
        2000,
        340
      ],
      "credentials": {
        "googleCalendarOAuth2Api": {
          "id": "6",
          "name": "Google Calendar Judith Yang"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "filePath": "/home/node/.n8n/od_sync/timestamp_google.bin"
      },
      "name": "Read Apt DateTStamp last access",
      "type": "n8n-nodes-base.readBinaryFile",
      "typeVersion": 1,
      "position": [
        -420,
        520
      ]
    },
    {
      "parameters": {
        "functionCode": "// Prepare for OD Database query\n\nfor (var i = 0; i < items.length;) {\n  // Save last access date in every item\n  items[i].json.DateTStamp =$node[\"Convert to JSON\"].json[\"DateTStamp\"];\n\n  // only touch opendent items \n  if (!items[i].json.id.startsWith('opendent')) {\n    items.splice(i,1);\n  } else {\n    // remove opendental-prefix from id\n    items[i].json.GoogleID = parseInt(items[i].json.id.replace('opendent',''));\n    // remove if already updated or invalid opendental-id (NaN)\n//    if ((((items[0].json.DateTStamp)==(items[0].json.updated)))||isNaN(items[i].json.GoogleID)) {\n    if (isNaN(items[i].json.GoogleID)) {\n        items.splice(i,1);\n    }\n    else i++;\n  }\n}\n\nreturn items;"
      },
      "name": "Delete and Trim Apt ID Prefix, remove cancelled Google Events",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        660,
        320
      ]
    },
    {
      "parameters": {
        "mode": "jsonToBinary",
        "options": {
          "mimeType": "application/json"
        }
      },
      "name": "Move Binary Data",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        2860,
        240
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "DateTStamp",
              "value": "={{$json[\"DateTStamp\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": true
        }
      },
      "name": "Select latest DateTStamp",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        2640,
        240
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "fileName": "/home/node/.n8n/od_sync/timestamp_google.bin"
      },
      "name": "Write Binary File OD Timestamp",
      "type": "n8n-nodes-base.writeBinaryFile",
      "typeVersion": 1,
      "position": [
        3080,
        240
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "operation": "sort",
        "sortFieldsUi": {
          "sortField": [
            {
              "fieldName": "DateTStamp",
              "order": "descending"
            }
          ]
        },
        "options": {}
      },
      "name": "Item Lists",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        2420,
        240
      ]
    },
    {
      "parameters": {
        "options": {}
      },
      "name": "Convert to JSON",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        -200,
        520
      ],
      "notesInFlow": true,
      "notes": "Don't change Name - required by Function Node"
    },
    {
      "parameters": {
        "action": "calculate",
        "value": "={{ new Date(new Date().getTime()) }}",
        "operation": "subtract",
        "duration": 1,
        "timeUnit": "months",
        "dataPropertyName": "DateTStamp_Earliest",
        "options": {}
      },
      "name": "Today - 1 month",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        -420,
        360
      ]
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        20,
        500
      ]
    },
    {
      "parameters": {
        "interval": 5,
        "unit": "minutes"
      },
      "name": "Interval",
      "type": "n8n-nodes-base.interval",
      "typeVersion": 1,
      "position": [
        -640,
        360
      ]
    },
    {
      "parameters": {
        "conditions": {
          "dateTime": [
            {
              "value1": "={{$json[\"DateTStamp\"]}}",
              "value2": "={{$json[\"DateTStamp_Earliest\"]}}"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        180,
        340
      ]
    },
    {
      "parameters": {
        "mode": "jsonToBinary",
        "convertAllData": false,
        "sourceKey": "={{$json[\"DateTStamp_Earliest\"]}}",
        "options": {
          "mimeType": "application/json"
        }
      },
      "name": "Move Binary Data1",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        440,
        540
      ]
    },
    {
      "parameters": {
        "fileName": "/home/node/.n8n/od_sync/timestamp_google.bin"
      },
      "name": "Write Binary File OD Timestamp1",
      "type": "n8n-nodes-base.writeBinaryFile",
      "typeVersion": 1,
      "position": [
        660,
        540
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "requestMethod": "PATCH",
        "url": "=https://www.googleapis.com/calendar/v3/calendars/srmgug2008lngoep8d3o49fcmg@group.calendar.google.com/events/{{$json[\"id\"]}}",
        "options": {},
        "bodyParametersUi": {
          "parameter": [
            {
              "name": "status",
              "value": "confirmed"
            }
          ]
        }
      },
      "name": "Try undelete cancelled events",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        1980,
        40
      ],
      "credentials": {
        "oAuth2Api": {
          "id": "7",
          "name": "Google Judith Yang"
        }
      },
      "continueOnFail": true,
      "notes": "https://stackoverflow.com/questions/18188681/undeleting-google-calendar-event/71283066#71283066\n\nThe patch operation on the status property seems to fail after deleting/cancelling an event with a 403 error on events that have been deleted some time ago. Setting the status to \"cancelled\" or deleting the event and then followed by setting the status to \"confirmed\" worked for me. This behavior is in accordance with the API documentation: \"Such cancelled events will eventually disappear, so do not rely on them being available indefinitely. Deleted events are only guaranteed to have the id field populated.\"."
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"status\"]}}",
              "value2": "=cancelled"
            }
          ]
        },
        "combineOperation": "any"
      },
      "name": "Google Event Cancelled?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1760,
        400
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"GoogleID\"]}}",
              "value2": "={{$json[\"AptNum\"]}}"
            }
          ]
        }
      },
      "name": "Google Event and OD Apt entry??",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1300,
        300
      ]
    },
    {
      "parameters": {
        "dataType": "string",
        "value1": "={{$json[\"AptStatus\"]}}",
        "rules": {
          "rules": [
            {
              "value2": "Scheduled"
            },
            {
              "value2": "Broken",
              "output": 1
            },
            {
              "value2": "cancelled",
              "output": 2
            }
          ]
        }
      },
      "name": "OD Apt Scheduled, Broken or cancelled?",
      "type": "n8n-nodes-base.switch",
      "typeVersion": 1,
      "position": [
        1540,
        220
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"status\"]}}",
              "value2": "=cancelled"
            }
          ]
        },
        "combineOperation": "any"
      },
      "name": "Google Event cancelled?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1760,
        140
      ]
    },
    {
      "parameters": {
        "keys": {
          "key": [
            {
              "currentKey": "updated",
              "newKey": "DateTStamp"
            }
          ]
        }
      },
      "name": "Rename update to DateTStamp",
      "type": "n8n-nodes-base.renameKeys",
      "typeVersion": 1,
      "position": [
        2200,
        240
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Read Apt DateTStamp last access",
            "type": "main",
            "index": 0
          },
          {
            "node": "Today - 1 month",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OD Apt Get by ID": {
      "main": [
        [
          {
            "node": "Merge OD Apt and Google Event Info",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Calendar Get All Events updated since yesterday": {
      "main": [
        [
          {
            "node": "Delete and Trim Apt ID Prefix, remove cancelled Google Events",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge OD Apt and Google Event Info": {
      "main": [
        [
          {
            "node": "Google Event and OD Apt entry??",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Apt DateTStamp last access": {
      "main": [
        [
          {
            "node": "Convert to JSON",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Delete and Trim Apt ID Prefix, remove cancelled Google Events": {
      "main": [
        [
          {
            "node": "OD Apt Get by ID",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge OD Apt and Google Event Info",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Move Binary Data": {
      "main": [
        [
          {
            "node": "Write Binary File OD Timestamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Select latest DateTStamp": {
      "main": [
        [
          {
            "node": "Move Binary Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Item Lists": {
      "main": [
        [
          {
            "node": "Select latest DateTStamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert to JSON": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Today - 1 month": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Interval": {
      "main": [
        [
          {
            "node": "Read Apt DateTStamp last access",
            "type": "main",
            "index": 0
          },
          {
            "node": "Today - 1 month",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "Google Calendar Get All Events updated since yesterday",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Move Binary Data1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Move Binary Data1": {
      "main": [
        [
          {
            "node": "Write Binary File OD Timestamp1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Try undelete cancelled events": {
      "main": [
        [
          {
            "node": "Rename update to DateTStamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Event Cancelled?": {
      "main": [
        null,
        [
          {
            "node": "Google Calendar Delete Event by ID",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Event and OD Apt entry??": {
      "main": [
        [
          {
            "node": "OD Apt Scheduled, Broken or cancelled?",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Google Event Cancelled?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OD Apt Scheduled, Broken or cancelled?": {
      "main": [
        [
          {
            "node": "Google Event cancelled?",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Google Calendar Delete Event by ID",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Google Calendar Delete Event by ID",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Event cancelled?": {
      "main": [
        [
          {
            "node": "Try undelete cancelled events",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Rename update to DateTStamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Rename update to DateTStamp": {
      "main": [
        [
          {
            "node": "Item Lists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
[/quote]
Jordan Sparks, DMD
http://www.opendental.com

joergzastrau
Posts: 23
Joined: Sun Feb 27, 2022 2:53 am

Re: Schedules with google

Post by joergzastrau » Fri Mar 25, 2022 7:53 am

Edited 3x, include setup instructions, Errata as of 03/27/22

Hi Jordan,

I am sorry that the code is in a state that I don't consider ready for publication. However, please review and improve.

Walkthrough to get this working:


Install and setup N8N.io

Download n8n Desktop App from http://n8n.io (or install the docker image). In this post I used n8n Version 1.3.0 (MAC) and OD 21.3 and 21.4.

Start&Quit n8n once to create the configuration "home" folder. This folder contains the file n8n-desktop.env (e.g. ~/.n8n on mac/Linux).
Set the environment variable “NODE_FUNCTION_ALLOW_EXTERNAL=moment” by editing the file n8n-desktop.env to enable the supplied moment.js.


Retrieve Appointments from Open Dental

Setup Open Dental API Access

Copy&Paste the 1st snipped Code from posting #1 into n8n. Connect the Start Node. Select the first HTTP-Node “OD API Get multiple”. Select Credential for Header Auth, “Create New”.

Put in “Authorization” under Name and "ODFHIR {developerKey}/{customerAPIKey}" under Value.

Customize the workflow

Note 1: Currently the last access to Open Dental is stored in a file timestamp.bin, then updated in timestamp_OD which will be copied over timestamp.bin when everything went smooth. Unfortunately the way n8n works makes it hard to store temporary data. This is an area for improvement.

Put the Path to a file called timestamp.bin in “Read Apt DateTSTamp last access”, e.g. “/Users/joerg/Desktop/timestamp.bin” on a MAC. Put example content in timestamp.bin: {"DateTStamp":"2022-03-04 22:17:53”} in OD API format.
Edit the Node “Write Binary File OD Timestamp” and put in the path to timestamp_OD.bin, e.g. “/Users/joerg/Desktop/timestamp_OD.bin”.

Edit “Update last Apt DateTStamp” and correct the paths,
e.g. “cp /Users/judy/Desktop/timestamp_OD.bin /Users/judy/Desktop/timestamp.bin/“.

Execute the workflow. At this point you should be able to retrieve OD appointments.

Note 2: Open Dental allows for 64 Bit Integer as Appointment numbering. N8N does not (2^53 - 1).

Save your work.


Creating and updating Google Calendar events.

Note 3: Setting up Google OAuth is the hardest part. We do it twice here.

Setup Google Calendar Access
Copy&Paste the 2nd snipped Code from posting #1 into n8n. Connect the Start node to “Retrieve Apt Update”

In order to create credentials, select the “Calendar Get Event By ID” Node and select “new credentials”. Copy the OAuth Redirect URL

Sign up for the Google Cloud Platform
Create a Project by clicking on “Select a project” and “New Project”
Select a Project name “e.g. OD Calendar Sync” and click “Create”.
Select the project and goto “Api and Services”, “Enable APIS and Services”, select Google Calendar API and enable it.

From APIs and Services, Select OAuth consent Screen, make it an external Application and fill out the App information, skip “App domain” and “Authorized domain”, put in developer contact information and save.
Add scopes for Google Calendar API: …/auth/calendar.events, …/auth/calendar.events.owned and save.
Add a test user.

From APIs and Services, click Credentials and “Create Credentials”, Select OAuth client ID.
Select “Web application” as Application type and give it Name.
Paste the OAuth Redirect URL after clicking "ADD URI" and click "Create".

Copy the Client ID and Client Secret from Google Cloud Platform to the n8n Google Calendar node. Click “Sign in with Google” and select your calendar in the n8n node.

In the “Try undo event cancellation” node, select "Create new credentials".
Put in “Authorization URL”: “https://accounts.google.com/o/oauth2/v2/auth
Put in Access Token URL: “https://www.googleapis.com/oauth2/v4/token
Put in Client ID and Client Secret from above.
Put in Scope: “https://www.googleapis.com/auth/calendar.events
Put in Auth URI Query Parameters: “access_type=offline”
Put In Authentication: “Header”.
And click “Connect my account”.

Customize the workflow

Set the Workflow ID of the previously created Workflow to retrieve OD Appointments In “Retrieve Apt Update” (here: 1).
Edit “Update last Apt DateTStamp” and correct the paths, e.g. “cp /Users/joerg/Desktop/timestamp_OD.bin /Users/judy/Desktop/timestamp.bin/“

Execute the workflow. At this point you should be Create and Update Google Calendar entries from OD appointments for Provider Number 1 to Google Calendar.

Note 4: There are other areas for improvement. The current N8N.io node for Google Calendar Node is - old-style - (it doesn't use syncToken). Also the workflow only supports one provider and one google calendar but can be easily extended by Copy&Paste.

Save your work.


Delete Entries from Google Calendar

OLD: Copy&Paste the 3rd snipped Code from posting #1 into n8n. Connect the Start Node.

NEW: The 3rd snipped Workflow from posting #1 contains an error. Please Copy&Paste the following Workflow instead.

Code: Select all

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "authentication": "headerAuth",
        "url": "=https://api.opendental.com/api/v1/appointments/{{$json[\"GoogleID\"]}}",
        "options": {}
      },
      "name": "OD Apt Get by ID",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        1740,
        -80
      ],
      "credentials": {
        "httpHeaderAuth": {
          "id": "3",
          "name": "Header Auth account"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "operation": "getAll",
        "calendar": "info@american-orthodontics.com",
        "returnAll": true,
        "options": {
          "updatedMin": "={{$json[\"DateTStamp\"]}}"
        }
      },
      "name": "Google Calendar Get All Events updated since yesterday",
      "type": "n8n-nodes-base.googleCalendar",
      "typeVersion": 1,
      "position": [
        1320,
        100
      ],
      "notesInFlow": false,
      "credentials": {
        "googleCalendarOAuth2Api": {
          "id": "2",
          "name": "info@american-orthodontics.com"
        }
      }
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge OD Apt and Google Event Info",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        1960,
        80
      ]
    },
    {
      "parameters": {
        "operation": "delete",
        "calendar": "srmgug2008lngoep8d3o49fcmg@group.calendar.google.com",
        "eventId": "={{$json[\"id\"]}}",
        "options": {}
      },
      "name": "Google Calendar Delete Event by ID",
      "type": "n8n-nodes-base.googleCalendar",
      "typeVersion": 1,
      "position": [
        2860,
        160
      ],
      "credentials": {
        "googleCalendarOAuth2Api": {
          "id": "2",
          "name": "info@american-orthodontics.com"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "filePath": "/Users/judy/Desktop/timestamp_google.bin"
      },
      "name": "Read Apt DateTStamp last access",
      "type": "n8n-nodes-base.readBinaryFile",
      "typeVersion": 1,
      "position": [
        460,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "// Prepare for OD Database query\n\nfor (var i = 0; i < items.length;) {\n  // Save last access date in every item\n  items[i].json.DateTStamp =$node[\"Convert to JSON\"].json[\"DateTStamp\"];\n\n  // only touch opendent items \n  if (!items[i].json.id.startsWith('opendent')) {\n    items.splice(i,1);\n  } else {\n    // remove opendental-prefix from id\n    items[i].json.GoogleID = parseInt(items[i].json.id.replace('opendent',''));\n    // remove if already updated or invalid opendental-id (NaN)\n//    if ((((items[0].json.DateTStamp)==(items[0].json.updated)))||isNaN(items[i].json.GoogleID)) {\n    if (isNaN(items[i].json.GoogleID)) {\n        items.splice(i,1);\n    }\n    else i++;\n  }\n}\n\nreturn items;"
      },
      "name": "Delete and Trim Apt ID Prefix, remove cancelled Google Events",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1540,
        100
      ]
    },
    {
      "parameters": {
        "mode": "jsonToBinary",
        "options": {
          "mimeType": "application/json"
        }
      },
      "name": "Move Binary Data",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        3740,
        20
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "DateTStamp",
              "value": "={{$json[\"DateTStamp\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": true
        }
      },
      "name": "Select latest DateTStamp",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        3520,
        20
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "fileName": "/Users/judy/Desktop/timestamp_google.bin"
      },
      "name": "Write Binary File OD Timestamp",
      "type": "n8n-nodes-base.writeBinaryFile",
      "typeVersion": 1,
      "position": [
        3960,
        20
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "operation": "sort",
        "sortFieldsUi": {
          "sortField": [
            {
              "fieldName": "DateTStamp",
              "order": "descending"
            }
          ]
        },
        "options": {}
      },
      "name": "Item Lists",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        3300,
        20
      ]
    },
    {
      "parameters": {
        "options": {}
      },
      "name": "Convert to JSON",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        680,
        300
      ],
      "notesInFlow": true,
      "notes": "Don't change Name - required by Function Node"
    },
    {
      "parameters": {
        "action": "calculate",
        "value": "={{ new Date(new Date().getTime()) }}",
        "operation": "subtract",
        "duration": 1,
        "timeUnit": "months",
        "dataPropertyName": "DateTStamp_Earliest",
        "options": {}
      },
      "name": "Today - 1 month",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        460,
        140
      ]
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        900,
        280
      ]
    },
    {
      "parameters": {
        "interval": 5,
        "unit": "minutes"
      },
      "name": "Interval",
      "type": "n8n-nodes-base.interval",
      "typeVersion": 1,
      "position": [
        240,
        140
      ]
    },
    {
      "parameters": {
        "conditions": {
          "dateTime": [
            {
              "value1": "={{$json[\"DateTStamp\"]}}",
              "value2": "={{$json[\"DateTStamp_Earliest\"]}}"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1060,
        120
      ]
    },
    {
      "parameters": {
        "mode": "jsonToBinary",
        "convertAllData": false,
        "sourceKey": "={{$json[\"DateTStamp_Earliest\"]}}",
        "options": {
          "mimeType": "application/json"
        }
      },
      "name": "Move Binary Data1",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        1320,
        320
      ]
    },
    {
      "parameters": {
        "fileName": "/home/node/.n8n/od_sync/timestamp_google.bin"
      },
      "name": "Write Binary File OD Timestamp1",
      "type": "n8n-nodes-base.writeBinaryFile",
      "typeVersion": 1,
      "position": [
        1540,
        320
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "requestMethod": "PATCH",
        "url": "=https://www.googleapis.com/calendar/v3/calendars/srmgug2008lngoep8d3o49fcmg@group.calendar.google.com/events/{{$json[\"id\"]}}",
        "options": {},
        "bodyParametersUi": {
          "parameter": [
            {
              "name": "status",
              "value": "confirmed"
            }
          ]
        }
      },
      "name": "Try undelete cancelled events",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        2860,
        -180
      ],
      "credentials": {
        "oAuth2Api": {
          "id": "4",
          "name": "Unnamed credential"
        }
      },
      "continueOnFail": true,
      "notes": "https://stackoverflow.com/questions/18188681/undeleting-google-calendar-event/71283066#71283066\n\nThe patch operation on the status property seems to fail after deleting/cancelling an event with a 403 error on events that have been deleted some time ago. Setting the status to \"cancelled\" or deleting the event and then followed by setting the status to \"confirmed\" worked for me. This behavior is in accordance with the API documentation: \"Such cancelled events will eventually disappear, so do not rely on them being available indefinitely. Deleted events are only guaranteed to have the id field populated.\"."
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"status\"]}}",
              "value2": "=cancelled"
            }
          ]
        },
        "combineOperation": "any"
      },
      "name": "Google Event Cancelled?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        2640,
        140
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"GoogleID\"]}}",
              "value2": "={{$json[\"AptNum\"]}}"
            }
          ]
        }
      },
      "name": "Google Event and OD Apt entry??",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        2180,
        80
      ]
    },
    {
      "parameters": {
        "dataType": "string",
        "value1": "={{$json[\"AptStatus\"]}}",
        "rules": {
          "rules": [
            {
              "value2": "Scheduled"
            },
            {
              "value2": "Broken",
              "output": 1
            },
            {
              "value2": "cancelled",
              "output": 2
            }
          ]
        }
      },
      "name": "OD Apt Scheduled, Broken or cancelled?",
      "type": "n8n-nodes-base.switch",
      "typeVersion": 1,
      "position": [
        2420,
        0
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"status\"]}}",
              "value2": "=cancelled"
            }
          ]
        },
        "combineOperation": "any"
      },
      "name": "Google Event cancelled?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        2640,
        -80
      ]
    },
    {
      "parameters": {
        "keys": {
          "key": [
            {
              "currentKey": "updated",
              "newKey": "DateTStamp"
            }
          ]
        }
      },
      "name": "Rename update to DateTStamp",
      "type": "n8n-nodes-base.renameKeys",
      "typeVersion": 1,
      "position": [
        3080,
        20
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Read Apt DateTStamp last access",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OD Apt Get by ID": {
      "main": [
        [
          {
            "node": "Merge OD Apt and Google Event Info",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Calendar Get All Events updated since yesterday": {
      "main": [
        [
          {
            "node": "Delete and Trim Apt ID Prefix, remove cancelled Google Events",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge OD Apt and Google Event Info": {
      "main": [
        [
          {
            "node": "Google Event and OD Apt entry??",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Apt DateTStamp last access": {
      "main": [
        [
          {
            "node": "Convert to JSON",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Delete and Trim Apt ID Prefix, remove cancelled Google Events": {
      "main": [
        [
          {
            "node": "OD Apt Get by ID",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge OD Apt and Google Event Info",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Move Binary Data": {
      "main": [
        [
          {
            "node": "Write Binary File OD Timestamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Select latest DateTStamp": {
      "main": [
        [
          {
            "node": "Move Binary Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Item Lists": {
      "main": [
        [
          {
            "node": "Select latest DateTStamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert to JSON": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Today - 1 month": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Interval": {
      "main": [
        [
          {
            "node": "Read Apt DateTStamp last access",
            "type": "main",
            "index": 0
          },
          {
            "node": "Today - 1 month",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "Google Calendar Get All Events updated since yesterday",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Move Binary Data1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Move Binary Data1": {
      "main": [
        [
          {
            "node": "Write Binary File OD Timestamp1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Try undelete cancelled events": {
      "main": [
        [
          {
            "node": "Rename update to DateTStamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Event Cancelled?": {
      "main": [
        null,
        [
          {
            "node": "Google Calendar Delete Event by ID",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Event and OD Apt entry??": {
      "main": [
        [
          {
            "node": "OD Apt Scheduled, Broken or cancelled?",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Google Event Cancelled?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OD Apt Scheduled, Broken or cancelled?": {
      "main": [
        [
          {
            "node": "Google Event cancelled?",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Google Event Cancelled?",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Google Event Cancelled?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Event cancelled?": {
      "main": [
        [
          {
            "node": "Try undelete cancelled events",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Rename update to DateTStamp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Rename update to DateTStamp": {
      "main": [
        [
          {
            "node": "Item Lists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
Select credentials in “Google Calendar Get All Events updated since yesterday”, “OD Apt Get by ID” and “Try undelete cancelled events”.

Edit “Read Apt DateTStamp last access” and. Put in correct path and create the file, e.g. “/Users/joerg/Desktop/timestamp_google.bin” with the DateTime in ISO format, e.g. "{"DateTStamp":"2022-03-24T13:49:18.544Z"}".

Save your work.


This is something that I did for a single OD installation and and my last bit of -real- coding was back in 2001 or something. Some of the code is taken from examples published on n8n.io, e.g. the pagination code and the idea for the loop.

Joerg

Post Reply