OneDrive and Microsoft Office 365 are some of the most popular business software packages and services on the planet. Its reach has expanded 238% since 2014 and 81% since 2016. With the Microsoft OneDrive API, integration has never been easier. OneDrive is excellent for sharing documents with other people without giving them access to your FileMaker system and allows for editing files without the hassle of downloading, editing, and then re-uploading. We will walk through how to use the Microsoft OneDrive API to upload documents, list documents in a folder, and download documents, allowing your documents to be stored and shared easily in the cloud to keep your FileMaker database lean and mean.

What You Need

You must have a OneDrive account to use this article’s integration, which is included in the Office 365 package. Also, make sure you have admin access to your Office 365 subscription if you have a business or school user account.

Creating a Microsoft Graph Application

Make sure you create an Azure AD Tenant first if you are using a personal account and not a work or school account. Similar to other API integrations, you need to create an app first. Go to the application registration portal and add a new registration. Fill in the name of your app and select the supported account types. Select the "Accounts in any organizational directory and personal Microsoft accounts" if you want to allow any valid Microsoft account to log in, the other options are for business accounts generally. Fill in a valid URL for the redirect URI. It doesn't matter what it is as long as it is valid, e.g. https://dbservices.com. Click Register. From the overview screen grab the Application (client) ID.

Client Secret

You'll need to also create a client secret for your app. On the sidebar, go to the Certificates & secrets section and click New Client Secret. You can designate the name and how long you want the secret to last. For this example, we'll set it to never expire. Finally, copy the text in the Value column.

Setting URLs

The Microsoft Azure Application we have created has different URLs and URIs that can be set to allow us to start connections with OneDrive and indicate which web pages users will end on when attempting to connect. In the Branding & Properties section, there is the Home page URL where users can sign in to start using the OneDrive integration. Set this to a valid URL and make sure the demo or your file has the same Home page URL specified. In the Authentication section, we should see our Web Redirect URI set, and below we should also set a valid URL into the Front-channel logout URL. These are both used to define the location where users should end up when completing authentication or sign-outs.

Managing App Permissions

Microsoft OneDrive requires that each user give the app permissions to be able to read/write data from their OneDrive. Before users can give permissions, you need to set up the default permissions on the application registration page. You'll need one delegated permission for this OneDrive Integration. Click API permissions, then click Add a Permission. Then select Microsoft Graph and Delegated permissions and select “Files.ReadWrite.All”.

Authenticating With Microsoft Graph

Once you have your API information, you will need to authenticate your solution with the OneDrive API. The sample file at the end of the article has the calls & web viewers that you can reference to authenticate FileMaker with OneDrive.

When the user clicks the Connect button, they will be asked to log in to their Microsoft account and approve delegated permissions for the application.

After the user logs in, a FileMaker script parses the redirected page’s URL for the client ID and auth codes that will be used for requests to OneDrive.

Making Requests And Parsing The Response

Microsoft OneDrive API supports multiple types of requests, such as HTTP POST, GET, and DELETE. You can perform all actions by using the native Insert from URL[] script step and giving it a valid URL and cURL options, including required fields within the JSON body specified by the documentation.

Here’s a sample request body to download files from a folder. Note how the DateTime element is formatted. For more details, refer to Microsoft OneDrive documentation under the List Children Page.

-X GET https://graph.microsoft.com/v1.0/me/drive/items/E9953196A1CE2F19!28596/children?$expand=thumbnails <br>--header "Authorization: Bearer EwBwA8l6BAAURSN/FHlDW5xN74t6GzbtsBBeBUYAAROJk..."

The response will include an array of documents from the selected folder and URLs to the thumbnails so we can download those in a bit. You'll also notice that some of the object names are prefixed by "@odata." This will not work with FileMaker's built-in JSON functions because FileMaker does not allow periods in an object's name. If you need to read one of these properties, you can simply substitute the entire object to remove the prefix.

Substitute ( $response ; "@odata." ; "" )

Downloading Files

Downloading a file is relatively easy once you know a quirk of the FileMaker cURL library. By default, when you try to download a file, it first authenticates your call and then redirects you to download the file. FileMaker will not follow the redirect and won't return anything unless you include the -L or --location flag in your cURL command. Another tip is that you can pass the --output flag with a URL encoded file name when downloading, to ensure the downloaded file has the correct name.

-X GET https://onedrive.live.com/?id=E9953196A1CE2F19%2129992 --header "Authorization: Bearer EwBwA8l6BAAURSN/FHlDW5xN74t6GzbtsBBe..." -L --output Screen%20Shot%202019%2D01%2D14%20at%204%2E54%2E41%20PM%2Epng

Uploading Documents

Uploading files to OneDrive is a bit more difficult. OneDrive provides 2 API methods for uploading docs, one for files under 4MB and another resumable upload method for larger files. We will strictly be using the resumable upload method as it is more flexible. The first thing you need to do is to create an upload session. The documentation is not very clear on how to do this. You'll need the folder ID you want to upload to and the filename URL encoded. When constructing the URL you'll need colons after the folder and filename, though the documentation makes no mention of this.

-X POST https://graph.microsoft.com/v1.0/me/drive/items/E9953196A1CE2F19!29992:/picture%2Epng:/createUploadSession --header "Authorization: Bearer EwBwA8l6BAAURSN/FHlDW5xN74t6GzbtsBBeBUYAAQScvI1+tUkC1GcS1z+E6..." --header "Content-Type: application/json" -d { "@microsoft.graph.conflictBehavior": "rename", "description": "description", "fileSystemInfo": { "@odata.type": "microsoft.graph.fileSystemInfo" }, "name": "Screen Shot 2019-01-14 at 4.54.41 PM.png" }

This will return an uploadUrl that you'll use to actually upload the data to.

{ "@odata.context":"https://graph.microsoft.com/v1.0/$metadata#microsoft.graph.uploadSession", "uploadUrl":"https://api.onedrive.com/rup/e9953196a...", "expirationDateTime":"2019-01-31T18:16:08.186Z", "nextExpectedRanges":["0-"] }

After you have the uploadUrl you need to calculate the content range of the file so we can tell the API what bytes we are uploading. You can use GetContainerAttribute ( TABLE::container ; "fileSize" ) to get the size of the file in bytes. From there, you can determine the content range. The range starts at zero, while the total starts at one, meaning a 25-byte file will have a content range of 0-24 and a total byte size of 25, thus setting the content range to 0-24/25 is telling the API that we are uploading the entire file. To actually upload the contents of a container you'll first set a variable with the contents of the container and then you'll use that variable with the -T or --upload-file flag.

-X PUT https://api.onedrive.com/rup/e9953196a... --header "Content-Range: bytes 0-24/25" -T $data

Displaying Files and Folders in FileMaker

One thing you'll want to keep in mind when working with the API is that you'll need to store a representation of all the files and folders as records in your FileMaker solution. In the sample file, I have a file table to store the document names, OneDrive ID, size, etc. You might also want to include a container field to store a thumbnail of your files as well, which are quite small at around 100px by 100px. OneDrive will also generate thumbnails of file types that FileMaker doesn't natively support, such as Word and PowerPoint.

Conclusion

The Microsoft OneDrive API can help keep your database size down, make sharing files easier, and allow you to edit files without downloading and re-uploading to FileMaker. Contact us if you need help integrating your FileMaker solution with OneDrive!