Microsoft Graph and Salesforce

Does anyone else remember trying to programmatically read exchange emails back in 2009? What a pain it was. At the time I was on the Fulton County help desk. I would take calls and emails then turn them into tickets. It was painful. So I automated it… I was using Perl at the time. There was a Outlook COM Object module that I used to read the emails in the outlook inbox and send replies. Of course you weren’t allowed to send email through the module without clicking the “Ye”s button in outlook. So someone had written “Click Yes.” You probably guessed what it does. Clicks the yes button when it pops up. Then I used mechanize to click through the helpdesk website because of course there were no APIs for that either. I had to translate the javascript on the page into logic in my script. Every time the page was updated I had to update my script. In the end it was beautiful. I made folders in the inbox to categorize emails, the script would read them all, take the contents, generate a ticket, assign it based on the folder/category, and email the user back a ticket number and a link to check the status. The status page was another Perl script that used mechanize to pull the status and last tech comment. That was a fun project. Then I moved off the helpdesk and they changed the helpdesk system. Response times for ticket generation suffered immediately.

Anyways on to the point. Someone came up with the idea to “gamify” SalesForce updates. Basically enter a task every time you talk with a customer. Anyone who has used lightning or maybe read my last post knows this is SLOW: https://vskeeball.com/2021/04/26/salesforce-automation/. These days you can use plenty of APIs to do things. Microsoft has the Graph API to access most O365 services: https://docs.microsoft.com/en-us/graph/use-the-api. By combining these two you can do some interesting things. Like check sent emails and calendar invites that are too external recipients, extract the email domain like “@vmware.com”, search SalesForce for opportunities that are tied to accounts with contacts who’s emails end in the email domain, then add a task to the opportunity. Sounds like fun right?

To start with you must gain access to Graph. Microsoft has provided some great sample code to do so: https://github.com/AzureAD/microsoft-authentication-library-for-python. I’m using device flow as the authentication method as I have no web interface: https://github.com/AzureAD/microsoft-authentication-library-for-python/blob/dev/sample/device_flow_sample.py. During authentication you must provide an application ID. This application ID comes from an application you create in Azure AD: https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-app. During the creation you select the API access your app requires. For my app I needed Mail.Read, Calendar.Read, and User.Read (well maybe not User.Read). This also gets specified as the scope in your device flow authentication request. The Authority URL in the request is “https://login.microsoftonline.com/”+your Azure AD Tenant GUID. Something like: https://login.microsoftonline.com/00000000-0000-0000-0000-00000000000. You can also use the common tenant if allowed. I was unable to. The config for my request looks something like this:

CLIENT_ID = '<MY APPLICATION ID>'
AUTHORITY_URL = 'https://login.microsoftonline.com/<MY TENANT ID>'
RESOURCE = 'https://graph.microsoft.com'
API_VERSION = 'beta'
SCOPE = ["Mail.Read","User.Read","Calendars.Read"]

You can explore the API and test requests using the Graph Explorer: https://developer.microsoft.com/en-us/graph/graph-explorer. The Graph request to get today’s received emails is easy. Just send a request to “https://graph.microsoft.com/v1.0/ me/messages?$filter=receivedDateTime ge 2021-05-04“. Of course replace the date. The format is YYYY-MM-DD. This request will get any email send after the date in UTC. You should probably translate your time into a UTC time stamp. Like this:

startDate = date.today()
endDate = date.today()
startDateTime = datetime.combine(startDate,datetime.min.time())\
  .astimezone(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")    
endDateTime = datetime.combine(endDate,datetime.max.time())\
  .astimezone(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")

This will give you a dateTime string in the UTC time format translated from local time like: “2021-05-04T00:00:00Z”. For email this isn’t required but for calendar entries it is. To find sent items first you need to find the “Sent Items” mailFolder. The full request looks like:

https://graph.microsoft.com/v1.0/me/mailFolders?$filter=displayName eq 'Sent Items'

The response will include the folderID. By using the folderID in the next request the Sent Items will be returned:

 https://graph.microsoft.com/v1.0/me/mailFolders/{sent_items_id}/messages?$filter={filter}

To find calendar entries the request looks like:

https://graph.microsoft.com/v1.0/me/calendarview?startDateTime={startDateTime}&endDateTime={endDateTime}

The startDateTime and endDateTime values will need to be translated into UTC again.

The responses to these may be to large to return in one response. The pages are 10 items long. Extracting the next link from the response a loop can be created and all the items added to a list. The next response looks something like:

"@odata.nextLink": "https://graph.microsoft.com/v1.0/me/messages?$filter=receivedDateTime+ge+2020-05-04&$skip=232"

Unfortunately the Graph API can’t filter based on sent to. For from you can filter messages based on the item data. The filter would look like:

sender/emailAddress/address= eq "whoever@whereever.com"

For toRecipients (who items are sent to) this doesn’t work. This field is not filterable. I believe this is due to the toRecipients being a list/array on the backend. I can’t see that code so who really knows. Filtering on client side is the only option. By looping through all the response objects and then looping through the toRecipeients looking for addresses not ending in vmware.com a list of external emails can be generated.

def find_not_internal(msgobj):
    to_return = []
    for msg in msgobj:
        try:
            for reci in msg['toRecipients']:
                if (reci['emailAddress']['address'][-11:] != "@vmware.com"):
                    to_return.append(msg)
                    break
        except:
            continue
    return to_return

This is similar to the calendar entry but instead of toRecipients use attendees. By taking the returned objects and searching in Salesforce for opportunities associated to accounts with the “@whereever.com” in the contacts a list of opportunities that may be the right ones are returned. The SOQL statement looks something like:

SELECT Id, Name FROM Opportunity where AccountId IN (SELECT AccountId FROM Contact WHERE Email like {emailDomain}) AND IsClosed = FALSE ORDER BY CloseDate ASC LIMIT 5

From here the correct opportunity ID can be used to create a task:

def createTask(sf,oppID,subject,comments= "", **kwargs):
    daystring = date.today().strftime("%Y-%m-%d")
    return sf.Task.create({'WhatId' : oppID,'Subject'\
      :subject,'ActivityDate':daystring,'Status':'Completed','Description'\
      :comments })

Of course there are many other fields that I used for finding the opportunity as well as creating the tasks. But they are internal fields and have been removed from these examples. For instance the amount of time spent on the task is recorded as well. For emails i used .3 hours as a default and for meetings I used the length of the meeting calculated from endDateTime-startDateTime. This is returned in seconds and then /60/60 for hours.

I probably should have waited to understand all the rules about the gamification before going through all of this but it was fun and didn’t take that long. I’ll probably need to modify the code slightly to ensure the tasks are tagged correctly for the game but that should be simple compared to the rest of it. Again no github link this time. To much internal stuff. I’m happy to share more details if anyone has specific questions just message me.