WILT: Sharepoint Graph API

Recently I got to do more experimentation with Microsoft's Graph API. I'd been asked to work on a system for storing project proposals - so they weren't projects yet so MS Project / MS Task wasn't appropriate. Using existing tools like Sharepoint Lists to store the proposals meant it could be back ended to PowerBI for reporting. That's a big out of the box win, as I don't have to build/ conceive of reports - that can be left as a user exercise.

Unfortunately the business team that dealt with Sharepoint is, as always, super busy. I know a little on how the PowerApps work and, after a quick play, I couldn't think of a good way to construct all the information into useful datasets and interfaces. So I fell back on making a stand-in: use PHP and Graph API to build the current UI. Then the back end remains Sharepoint Lists and it gains all the benefits of the MS infrastructure.

How to find the damn list ID.

1. Load Graph Explorer
2. Visit '/sites?search=<SearchTerm>' to find your site that matches <SearchTerm>. Copy the <SiteId>
3. Visit '/sites/<SiteID>/lists' to page through the lists to find the list id. Copy the <ListId>

How to use a Person with Multiple selections

With the PersonGroup selector, it brings back an object with PersonID, PersonName and PersonEmail. That's fine, but how to find and save people? For this I created a lookup that AJAXed in another function so you could look people up by Name and it would return the Email address. On save it would run a query against the Site's person list to convert Email to ID.

The trick was PersonGroup selectors can allow for multiple selections. To do this, you need to save an array of PersonId objects with the right fields. And you then need to tell the sharepoint API the correct type of the PersonId field, which is a simple string, but without it the API doesn't know what to do with it ({"@odata.type": "string"}). A trap I found is that the PersonGroup lookup only returned people already in the site, not all people in the organisation. That didn't work in my use case, so I just replaced PersonGroup fields with a multi-line text box and had PHP enforce Email addresses by filter.

How to link in with Power Automate for Approvals.

PowerAutomate has built in approval functionality. But I couldn't see where in a list it processed the approvals etc. I wanted it so that if you saved it with a status of pending approval, it would trigger workflows. I toyed with having a "Previous Status" field so that the workflow would trigger when the statuses were different, but it proved to be flaky. Honestly, the easiest way was to set a flag on a field when it was submitted for approval.

1. Add a Choice with "Yes"/"No" as options called "TriggerApproval"
2. When saved for approval, the "Yes" setting is set.
3. Have a Power Automate flow on Create or Save of a List item. If the TriggerApproval is set as "Yes", then set it to "No" and run the approvals.

Voila, no double-triggers.

Attachments

You're out of luck. Graph API has no ability to manipulate attachments. I know. So instead I've put attachments in a named folder.

1. Create a directory in a nearby drive, and copy the Drive ID
2. When files are part of the upload:

   1. Do a get on the ID of the Sharepoint list on the end of the Drive to see if there's already a directory to save files in '/sites/<SiteID>/drive/items/<DriveId>/children/<ListItemID>

      • If not, make one by posting to the above URL:

        1[
        2    "name" => "<ListItemID>",
        3    "folder" => (object)[],
        4    "@microsoft.graph.conflictBehavior" => "rename"
        5]
        

   2. Create an Upload Session by PUT to '/drives/<DriveID>/root:/<DirectoryName>/<ListItemID>/<FileName>:/createUploadSession' with the body:

      1[
      2    "item" => [
      3        "@odata.type" => "microsoft.graph.driveItemUploadableProperties",
      4        "@microsoft.graph.conflictBehavior" => "rename",
      5        "name" => "<FileName>"
      6    ]
      7]
      

   3. Get the uploadUrl from the body, that's where you actually post the file content

   4. Post to the Upload URL specific sections of the body (or all at once if it's small enough). You have to specify the byte size of each section using Content-Range, note it starts at 0 so the length is -1:

      1[
      2    'headers'         => ['Content-Range' => "bytes 0-<last_index>/<size>", 'Content-Length' => <size>],
      3    'body'            => <contents>,
      4    'allow_redirects' => false,
      5    'timeout'         => 5
      6]