Uploading Documents to SharePoint via Cloud Flows in Power Platform: A Deep Dive

Uploading documents from Power Platform to SharePoint might seem straightforward, but scratch beneath the surface, and you'll uncover nuances that demand a deeper understanding. If you've ever scratched your head wondering why your Cloud Flows aren't saving files seamlessly, this guide is for you.

The Common Misstep

Power Platform is heavily leaned upon for generating documents using Cloud Flows (Power Automate). More often than not, these documents find their resting place on a SharePoint site, harmoniously integrated with our Power Platform environment.

But here’s the catch: storing these documents isn’t as simple as a one-action cloud  flow to copy a file to SharePoint. To preserve the inbuilt integration, you'll need to mimic the native methodology employed by Power Platform. And let’s face it, Power Platform hasn't exactly rolled out the red carpet for us in this regard.

Unearthing the Mechanics

Let's pull back the curtains and shed light on the integral components of this intricate machinery:

1. The SharePoint Site Setting: Upon activating SharePoint document management within a Power Platform environment, the selected SharePoint site's location nests itself in the sharepointsite table.
2. Entity Management: For every entity you enable in document management, a new entry is added to the sharepointdocumentlocation table. These records all refer back to the very same sharepointsite through the parentsitelocation lookup column.

3. The User Upload Sequence: Imagine an end user choosing to upload a document via a model-driven form, say, for an account. Power Platform then springs into action, carving out a unique folder identified by <name> + <id>. This is not just a one-time affair; all ensuing uploads for that entity accumulate here. Notably, Power Platform doesn’t maintain a log of each file. Instead, any files inhabiting that folder get showcased within the model-driven forms.

This unravels a dual-fold mission for your Cloud Flows:

• Location Identification: Your flows should be adept at pinpointing the exact storage location by skimming through existing records.
• Record Creation: First-time uploads for any entity mean your flow should be ready to create the requisite records.

Dissecting the Flow

With a broad understanding in place, let's embark on a granular expedition of our Cloud Flow!

Action: List Appointment DocumentLocations
- Table name: Document Locations
- Select columns: relativeurl
- Expand query: parentsiteorlocation_sharepointdocumentlocation($select=relativeurl;$expand=parentsiteorlocation_sharepointsite($select=absoluteurl))

Condition: If DocumentLocation Not Found
- length(outputs('List_Appointment_DocumentLocations')?['body/value']) is less than 1

If no:
- Set SharePointLocation to existing location
 - Name: SharePointLocation
 - Value: {
 "sitecollectionurl": "@{first(outputs('List_Appointment_DocumentLocations')?['body/value'])['parentsiteorlocation_sharepointdocumentlocation/parentsiteorlocation_sharepointsite/absoluteurl']}",
 "folderpath": "@{concat('/', first(outputs('List_Appointment_DocumentLocations')?['body/value'])['parentsiteorlocation_sharepointdocumentlocation/relativeurl'], '/', first(outputs('List_Appointment_DocumentLocations')?['body/value'])['relativeurl'])}"
}

If yes:
- Action: List Parent DocumentLocation
 - Table name: Document Locations
 - Select columns: sharepointdocumentlocationid,_parentsiteorlocation_value
 - Filter rows: relativeurl eq 'appointment'

- Action: Get SharePointSite
 - Table name: SharePoint Sites
 - Row ID: @{first(outputs('List_Parent_DocumentLocation')?['body/value'])['_parentsiteorlocation_value']}
 - Select columns: absoluteurl

- Action: Add a new DocumentLocation
 - Table name: DocumentLocations
 - Name: @{outputs('Get_Appointment')?['body/subject']}
 - Service Type: SharePoint
 - Location Type: General
 - Regarding (Meetings): appointments(@{triggerBody()['text']})
 - Relative URL: @{outputs('Get_Appointment')?['body/subject']}_@{toUpper(replace(triggerBody()['text'], '-', ''))}

- Action: Create new folder
 - Site Address: Stakeholders Relationship Management - DEV - https://esm.sharepoint.com/sites/StakeholdersRelationshipManagement_DEV
 - List or Library: Meeting
 - Folder Path: @{outputs('Get_Appointment')?['body/subject']}_@{toUpper(replace(triggerBody()['text'], '-', ''))}

- Action: Set SharePointLocation to new location
 - Name: SharePointLocation
 - Value: {
 "sitecollectionurl": "@{outputs('Get_SharePointSite')?['body/absoluteurl']}",
 "folderpath": "/appointment/@{outputs('Get_Appointment')?['body/subject']}_@{toUpper(replace(triggerBody()['text'], '-', ''))}"
}

Conclusion

Harmonizing Power Platform with SharePoint might demand some heavy lifting, but with the insights above and a sprinkle of patience, your Cloud Flows will be up and running in no time. As always, happy automating!