Skip to content

Instantly share code, notes, and snippets.

@fenrir-naru
Last active May 13, 2021 23:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save fenrir-naru/fa44b9b30dfc38e522cb5048e13a9590 to your computer and use it in GitHub Desktop.
Save fenrir-naru/fa44b9b30dfc38e522cb5048e13a9590 to your computer and use it in GitHub Desktop.
Outlook desktop to Google calendar
Public WithEvents myOlFolder As Outlook.Folder
Public WithEvents myOlItems As Outlook.Items
Public Sub Application_Startup()
Set myOlFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar)
Set myOlItems = myOlFolder.Items
End Sub
Private Sub sendREST(ByVal Item As Object, ByVal Mode As String)
Dim params As Object
Set params = CreateObject("Scripting.Dictionary")
params.Add "mode", Mode
params.Add "subject", Item.Subject
params.Add "start", Item.Start
params.Add "end", Item.End
params.Add "id", Item.GlobalAppointmentID
Debug.Print JsonConverter.ConvertToJson(params, " ", 2)
Dim httpObj As Object
Set httpObj = CreateObject("MSXML2.ServerXMLHTTP")
httpObj.Open "POST", "https://script.google.com/macros/******/exec", False
httpObj.SetRequestHeader "Content-Type", "application/json"
httpObj.Send JsonConverter.ConvertToJson(params)
Debug.Print httpObj.responseText
Set httpObj = Nothing
End Sub
Private Sub myOlFolder_BeforeItemMove(ByVal Item As Object, ByVal MoveTo As Folder, Cancel As Boolean)
Dim delFolder As Folder
Set delFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderDeletedItems)
If MoveTo Is Nothing Then
Call sendREST(Item, "delete")
ElseIf delFolder = MoveTo Then
Call sendREST(Item, "delete")
End If
End Sub
Private Sub myOlItems_ItemAdd(ByVal Item As Object)
Call sendREST(Item, "add")
End Sub
Private Sub myOlItems_ItemChange(ByVal Item As Object)
Call sendREST(Item, "change")
End Sub
function push_event(str) {
let json = JSON.parse(str);
let timeZone = Session.getScriptTimeZone();
let t_start = new Date(json.start), t_end = new Date(json.end);
if((t_end - t_start) % (24 * 60 * 60000) == 0){
t_end = new Date(t_end.valueOf() - 1E3);
}
console.log(json);
let lock = LockService.getScriptLock();
if(!lock.tryLock(30E3)){return;}
try{
const calendar = CalendarApp.getCalendarById("******@gmail.com");
let events = calendar.getEvents(
new Date(t_start.valueOf() - 24 * 60 * 60E3 * 180),
new Date(t_end.valueOf() + 24 * 60 * 60E3 * 180),
{search: json.id});
let count_goal = ((json.mode == "delete") ? 0 : 1),
count = 0;
events.filter(function(event){
if(count >= count_goal){return true;}
event.setTitle(json.subject);
event.setTime(t_start, t_end);
count += 1;
return false;
}).forEach(function(event){
event.deleteEvent();
});
while(count < count_goal){
calendar.createEvent(
json.subject,
t_start, t_end,
{description: `Copied from your Office 365 calendar( ${json.id} )`});
count += 1;
}
}finally{
lock.releaseLock();
}
}
function test() {
let str = `{
"subject": "test",
"start": "2021-05-18T20:00:00.000Z",
"end": "2021-05-18T22:00:00.000Z",
"id": "Outlook_id",
"mode": "add"
}`;
push_event(str);
}
function doPost(e) {
if (e == null || e.postData == null || e.postData.contents == null) {
return;
}
push_event(e.postData.contents);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment