OneDrive and SharePoint

Hong Ooi

Source: vignettes/od_sp.Rmd

Microsoft365R is a simple yet powerful R interface to Microsoft 365 (formerly known as Office 365), leveraging the facilities provided by the AzureGraph package. This vignette describes how to access data stored in SharePoint Online sites and OneDrive. Both personal OneDrive and OneDrive for Business are supported.

See the “Authenticating to Microsoft 365” vignette for more details on authentication if required.

OneDrive

To access your personal OneDrive, call the get_personal_onedrive() function. This returns an R6 client object of class ms_drive, which has methods for working with files and folders.

od <- get_personal_onedrive()

# list files and folders
od$list_items()

# same as list_items()
od$list_files()
od$list_files("Documents")

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

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

To access OneDrive for Business call get_business_onedrive(). This also returns an object of class ms_drive, so the exact same methods are available as for personal OneDrive.

# by default, authenticate with the Microsoft365R internal app ID
odb <- get_business_onedrive()

odb$list_files()
odb$open_files("myproject/demo.pptx")

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")

To obtain a shareable link for a file or folder, use create_share_link():

# default: viewable link, expires in 7 days
od$create_share_link("Documents/myfile.docx", type="view")

# editable link, expires in 24 hours
od$create_share_link("Documents/myfile.docx", type="edit", expiry="24 hours")

# setting a password
od$create_share_link("Documents/myfile.docx", password="Use-strong-passwords!")

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.

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")

You can also retrieve an object of class ms_drive_item representing the file or folder, with get_item(). This has methods appropriate for drive items. Many of the drive methods are actually implemented by calling down to corresponding methods for the ms_drive_item class, with default paths set appropriately.

# rename a file by retrieving it as a drive item and calling its update() method
item <- od$get_item("Documents/myfile.docx")
item$update(name="myfile version 2.docx")

# methods appropriate for a folder
docs_folder <- od$get_item("Documents")
docs_folder$list_files()

# upload a file to the "Documents/New folder" folder
docs_folder$create_folder("New folder")
docs_folder$upload("New folder/newdocument.docx")
newfile <- docs_folder$get_item("New folder/newdocument.docx")

# methods appropriate for a file
newfile$open()
newfile$download("newdocument modified.docx")

There are also convenience methods for working with data frames and other R objects.

# saving and loading data to a csv file
od$save_dataframe(iris, "Documents/iris.csv")
iris2 <- od$load_dataframe("Documents/iris.csv")

# saving and loading an R object
wtmod <- lm(wt ~ ., data=mtcars)
od$save_rds(wtmod, "Documents/wtmod.rds")
wtmod2 <- od$load_rds("Documents/wtmod.rds")

# saving and loading multiple objects
od$save_rdata(iris, wtmod, file="Documents/objects.rdata")
od$load_rdata("Documents/objects.rdata")

SharePoint

To access a SharePoint site, use the get_sharepoint_site() function and provide the site name, URL or ID. You can also list the sites you’re following with list_sharepoint_sites().

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"
)

There are also get_item(), create_item(), update_item() and delete_item() methods for working directly with individual items.

item <- list$create_item(firstname="Mary", lastname="Smith")
iid <- item$properties$id
list$update_item(iid, firstname="Eliza")
list$delete_item(iid)

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.

Currently, Microsoft365R only supports SharePoint Online, the cloud-hosted version of the product. Support for SharePoint Server (the on-premises version) may come at a later stage.