Microsoft365R: an R interface to the Microsoft 365 suite

I’m very happy to announce Microsoft365R, a package for working with the Microsoft 365 (formerly known as Office 365) suite of cloud services. Microsoft365R extends the interface to the Microsoft Graph API provided by the AzureGraph package to provide a lightweight yet powerful interface to SharePoint and OneDrive, with support for Teams and Outlook soon to come.

Microsoft365R is now available on CRAN, or you can install the development version from GitHub with devtools::install_github("Azure/Microsoft365R").

Authentication

The first time you call one of the Microsoft365R functions (see below), it will use your Internet browser to authenticate with Azure Active Directory (AAD), in a similar manner to other web apps. You will get a dialog box asking for permission to access your information.

Microsoft365R is registered as an app in the “aicatr” AAD tenant. Because it needs read/write access to groups and SharePoint sites, you’ll need an admin to grant it access to your tenant. Alternatively, if the environment variable CLIMICROSOFT365_AADAPPID is set, Microsoft365R will use its value as the app ID for authenticating; or you can specify the app ID as an argument when calling the functions below. See also this issue at the GitHub repo for some possible workarounds.

OneDrive

To access your personal OneDrive, call the personal_onedrive() function, and to access OneDrive for Business call business_onedrive(). Both functions return an R6 client object of class ms_drive, which has methods for working with files and folders. Note that OneDrive for Business is technically part of SharePoint, and requires a Microsoft 365 Business subscription.

od <- personal_onedrive()
odb <- business_onedrive(tenant="mycompany")

# use the device code authentication flow in RStudio Server
od <- personal_onedrive(auth_type="device_code")

# list files and folders
od$list_items()
od$list_items("Documents")

# upload and download files
od$download_file("Documents/myfile.docx")
od$upload_file("somedata.xlsx")

# create a folder
od$create_folder("Documents/newfolder")

You can open a file or folder in your browser with the open_item() method. For example, a Word document or Excel spreadsheet will open in Word or Excel Online, and a folder will be shown in OneDrive.

od$open_item("Documents/myfile.docx")

You can get and set the metadata properties for a file or folder with get_item_properties() and set_item_properties(). For the latter, provide the new properties as named arguments to the method. Not all properties can be changed; some, like the file size and last modified date, are read-only. You can also retrieve an object representing the file or folder with get_item(), which has methods appropriate for drive items.

od$get_item_properties("Documents/myfile.docx")

# rename a file -- version control via filename is bad, mmkay
od$set_item_properties("Documents/myfile.docx", name="myfile version 2.docx")

# alternatively, you can call the file object's update() method
item <- od$get_item("Documents/myfile.docx")
item$update(name="myfile version 2.docx")

SharePoint

To access a SharePoint site, use the sharepoint_site() function and provide the site URL or ID.

site <- sharepoint_site("https://myaadtenant.sharepoint.com/sites/my-site-name")

The client object has methods to retrieve drives (document libraries) and lists. To show all drives in a site, use the list_drives() method, and to retrieve a specific drive, use get_drive(). Each drive is an object of class ms_drive, just like the OneDrive clients above.

# list of all document libraries under this site
site$list_drives()

# default document library
drv <- site$get_drive()

# same methods as for OneDrive
drv$list_items()
drv$open_item("teamproject/plan.xlsx")

To show all lists in a site, use the get_lists() method, and to retrieve a specific list, use get_list() and supply either the list name or ID.

site$get_lists()
lst <- site$get_list("my-list")

You can retrieve the items in a list as a data frame, with list_items(). This has arguments filter and select to do row and column subsetting respectively. filter should be an OData expression provided as a string, and select should be a string containing a comma-separated list of columns. Any column names in the filter expression must be prefixed with fields/ to distinguish them from item metadata.

# return a data frame containing all list items
lst$list_items()

# get subset of rows and columns
lst$list_items(
    filter="startsWith(fields/firstname, 'John')",
    select="firstname,lastname,title"
)

Finally, you can retrieve subsites with list_subsites() and get_subsite(). These also return SharePoint site objects, so all the methods above are available for a subsite.

Future plans

Currently, Microsoft365R supports OneDrive and SharePoint Online; future updates will add the ability to post to Teams channels and send emails via Outlook. You can also provide feedback and make feature requests by opening an issue at the repo, or by emailing me at hongooi73 (@) gmail.com.