SharePoint Lists (also known as Microsoft Lists) can be added to a SharePoint site or created for a single user. This package assumes you are working with lists within a SharePoint site. This article covers:
Loading the Microsoft365R::ms_site object in advance is
optional for most functions. list_sp_lists() takes a site
URL and returns a data frame with information about all site lists:
site_lists <- list_sp_lists(
site_url = "https://bmore.sharepoint.com/sites/DOP-ALL"
)
#> Loading Microsoft Graph login for default tenant
head(site_lists[, c(2:7)])
#> createdDateTime
#> 1 2023-11-27T18:12:43Z
#> 2 2026-04-02T17:54:46Z
#> 3 2021-04-09T15:21:43Z
#> 4 2023-10-25T20:38:22Z
#> 5 2020-12-16T15:50:27Z
#> 6 2022-12-05T14:55:38Z
#> description
#> 1 A reference list of Division-level, inter-agency, and program specific recurring meetings.
#> 2
#> 3 Canvas items for channel 19:[email protected]
#> 4 A list for tracking issues, initiatives, and projects identified by members of the Department of Planning Technology Committee and agency staff.
#> 5 Canvas items for channel 19:[email protected]
#> 6 Canvas items for channel 19:[email protected]
#> eTag
#> 1 "531a153d-b9d4-4d04-93cd-0d49d9bac705,63"
#> 2 "bdd11473-562b-43bc-b498-0f235aef9e4a,12"
#> 3 "0d7df1be-e6de-474b-88ea-0f56689ca3e9,15"
#> 4 "7c6b3bac-4962-4285-980e-127e36583ffc,22"
#> 5 "9c518bac-ef99-4ae8-ab27-1453f68c87e1,15"
#> 6 "d216b2d5-7cae-491f-abf4-1474628a0982,15"
#> id lastModifiedDateTime
#> 1 531a153d-b9d4-4d04-93cd-0d49d9bac705 2026-10-07T19:07:23Z
#> 2 bdd11473-562b-43bc-b498-0f235aef9e4a 2026-10-13T08:44:34Z
#> 3 0d7df1be-e6de-474b-88ea-0f56689ca3e9 2024-10-02T03:22:07Z
#> 4 7c6b3bac-4962-4285-980e-127e36583ffc 2026-10-07T19:07:24Z
#> 5 9c518bac-ef99-4ae8-ab27-1453f68c87e1 2024-10-02T03:22:07Z
#> 6 d216b2d5-7cae-491f-abf4-1474628a0982 2024-10-02T03:22:06Z
#> name
#> 1 DOP Internal Interagency Meetings
#> 2 DOP Grants Management Resources
#> 3 1938d6c3c5ffd048d091655fde4e10fa2fthreadskype_wiki
#> 4 Technology Committee Priority Issue Tracker
#> 5 1978556da3d0db4ddabfb7d8807110cc60threadskype_wiki
#> 6 191a54f93ccf1244cda3547deae96e306athreadskype_wikiHowever, loading the site and using it when needed is recommended to
avoid duplicate API calls. Use get_sp_site() to load the
site and pass that object to list_sp_lists() for multiple
lists or get_sp_list() to return a single list.
sp_site <- get_sp_site(
"https://bmore.sharepoint.com/sites/DOP-ALL"
)
#> Loading Microsoft Graph login for default tenant
room_list <- get_sp_list(list_name = "DOP Rooms", site = sp_site)
room_list
#> <Sharepoint list 'DOP Rooms'>
#> directory id: dd07e874-8b01-4e0d-a7cb-d7e709d580e9
#> web link: https://bmore.sharepoint.com/sites/DOP-ALL/Lists/DOP%20Rooms
#> description: A list of offices and other rooms occupied by the Baltimore City Department of Planning on the 8th Floor of the Benton Building.
#> ---
#> Methods:
#> bulk_import, create_item, delete,
#> delete_item, do_operation, get_column_info,
#> get_item, get_list_pager, list_items,
#> sync_fields, update, update_itemTo get the items from a list as a data frame, use
list_sp_list_items(). You can pass a list name and site, a
list URL, or a list object:
rooms <- list_sp_list_items(list_name = "DOP Rooms", site = sp_site)
rooms <- list_sp_list_items(
"https://bmore.sharepoint.com/sites/DOP-ALL/Lists/DOP%20Rooms/AllItems.aspx"
)
#> Loading Microsoft Graph login for default tenant
rooms <- list_sp_list_items(sp_list = room_list)
head(rooms[, c(1:7)])
#> Title LinkTitle _ColorTag ComplianceAssetId RoomType
#> 1 08-COM02 08-COM02 NA NA CORRIDOR
#> 2 08-COM01 08-COM01 NA NA CORRIDOR
#> 3 08-SRV01 08-SRV01 NA NA UTILITY
#> 4 08-SRV02 08-SRV02 NA NA TELE/COMMS
#> 5 08-SRV03 08-SRV03 NA NA MEN
#> 6 08-SRV04 08-SRV04 NA NA WOMEN
#> Location RoomCategory
#> 1 Hallway to plotter area COMMON
#> 2 elevator lobby COMMON
#> 3 interior hallway west of lobby SERVICE
#> 4 public hallway SERVICE
#> 5 public restroom SERVICE
#> 6 public restroom SERVICEUse select to return only some columns and
display_nm = "replace" to use the column display names (the
names shown in SharePoint) in place of the internal column names.
Use filter to return only matching items. A filter is an
OData
$filter expression passed to the Microsoft Graph
API:
fields/ and use the internal
column name, e.g. fields/RoomCategory (not the display
name).fields/RoomCategory eq 'SERVICE'.eq, ne,
gt, ge, lt, le),
combine conditions with and or or, and match
text with startswith().service_rooms <- list_sp_list_items(
sp_list = room_list,
filter = "fields/RoomCategory eq 'SERVICE' and startswith(fields/Title, '08')",
select = c("id", "Title", "RoomType", "RoomCategory")
)
head(service_rooms)
#> Title RoomType RoomCategory id
#> 1 08-SRV01 UTILITY SERVICE 28
#> 2 08-SRV02 TELE/COMMS SERVICE 29
#> 3 08-SRV03 MEN SERVICE 30
#> 4 08-SRV04 WOMEN SERVICE 31
#> 5 08-SRV05 ELECTRICAL SERVICE 32
#> 6 08-SRV06 TOILET SERVICE 33Filtering on a column that isn’t indexed works on smaller lists, but
may fail on lists with more than 5,000 items, so index any columns you
filter on often (e.g. with indexed: true in a YAML
definition). See the Microsoft Graph documentation on listing
items for more examples.
You can get a single list item as a
Microsoft365R::ms_list_item object using
get_sp_list_item(). The id values are not visible by
default when looking at a Microsoft List online but they are included in
the list item data frame:
A list name and site (or site URL) are the only requirements to
create a list. However, a list created using the default
template = "genericList" has only one column: Title.
Columns can be added when the list is created or afterwards with
create_sp_list_column().
Creating a column requires a column definition. Create one using
create_column_definition() or one of the column
type-specific variations:
create_text_column("TextColumn", multiple_lines = TRUE)
#> $name
#> [1] "TextColumn"
#>
#> $text
#> $text$allowMultipleLines
#> [1] TRUE
create_number_column("NumberColumn", decimal_places = "none")
#> $name
#> [1] "NumberColumn"
#>
#> $number
#> $number$decimalPlaces
#> [1] "none"
create_choice_column("ChoiceColumn", choices = c("Apple", "Pear", "Orange"))
#> $name
#> [1] "ChoiceColumn"
#>
#> $choice
#> $choice$choices
#> [1] "Apple" "Pear" "Orange"If you are creating a list for an existing data frame,
data_as_column_definition_list() creates a column
definition for each column. Data frames with character, numeric,
datetime, and factor values are supported but list columns are not.
penguins_columns <- data_as_column_definition_list(penguins)
str(penguins_columns[1:2])
#> List of 2
#> $ :List of 2
#> ..$ name : chr "species"
#> ..$ choice:List of 1
#> .. ..$ choices: chr [1:3] "Adelie" "Chinstrap" "Gentoo"
#> $ :List of 2
#> ..$ name : chr "island"
#> ..$ choice:List of 1
#> .. ..$ choices: chr [1:3] "Biscoe" "Dream" "Torgersen"Pass the column definitions to the columns argument of
create_sp_list() to create the columns with the list:
penguins_list <- create_sp_list(
list_name = "penguins",
description = "Measurements of Penguins near Palmer Station, Antarctica",
columns = penguins_columns,
site = sp_site
)
penguins_list
#> <Sharepoint list 'penguins'>
#> directory id: 0c3cdaca-4a96-4931-b093-e7e203b20f59
#> web link: https://bmore.sharepoint.com/sites/DOP-ALL/Lists/penguins
#> description: Measurements of Penguins near Palmer Station, Antarctica
#> ---
#> Methods:
#> bulk_import, create_item, delete,
#> delete_item, do_operation, get_column_info,
#> get_item, get_list_pager, list_items,
#> sync_fields, update, update_itemA list and its columns can also be defined in a YAML file (an
experimental feature). The YAML format uses the same property names as
the Microsoft Graph API list
and columnDefinition
resources, and a custom key can hold any other metadata you
want to keep with the definition. The package includes an example:
example_yaml <- system.file(
"extdata", "example-list.yaml",
package = "sharepointr"
)
writeLines(readLines(example_yaml))
# Example SharePoint list definition for sharepointr.
# See ?read_sp_list_yaml for the format.
format_version: 1
displayName: Example Projects
description: Example list of projects.
list:
template: genericList
custom:
owner: Planning
columns:
- name: Title
displayName: Project Name
required: true
text: {}
- name: ProjectID
displayName: Project ID
description: Project reference ID
required: true
enforceUniqueValues: true
indexed: true
text:
maxLength: 20
custom:
form_order: 1
section: Summary
- name: ProjectNotes
displayName: Project Notes
text:
allowMultipleLines: true
custom:
form_order: 2
section: Summary
- name: ProjectStatus
displayName: Status
choice:
choices:
- Planning
- Active
- Closed
displayAs: radioButtons
custom:
form_order: 3
- name: StartDate
displayName: Start Date
dateTime:
format: dateOnly
- name: Budget
displayName: Budget
currency:
locale: en-us
custom:
decimals: none
- name: Phases
displayName: Number of Phases
number:
decimalPlaces: none
minimum: 0
- name: IsActive
displayName: Active
boolean: {}
- name: BudgetText
displayName: Budget ($)
calculated:
formula: "=IF(ISBLANK([Budget]),\"\",USDOLLAR([Budget],0))"
outputType: textread_sp_list_yaml() reads and validates the file. Use
as.data.frame() to get a table with one row per column:
projects_definition <- read_sp_list_yaml(example_yaml)
as.data.frame(projects_definition)[, c("name", "displayName", "type", "required")]
#> name displayName type required
#> 1 Title Project Name text TRUE
#> 2 ProjectID Project ID text TRUE
#> 3 ProjectNotes Project Notes text NA
#> 4 ProjectStatus Status choice NA
#> 5 StartDate Start Date dateTime NA
#> 6 Budget Budget currency NA
#> 7 Phases Number of Phases number NA
#> 8 IsActive Active boolean NA
#> 9 BudgetText Budget ($) calculated NAPass the definition (or the path to the file) to the
definition argument of create_sp_list(). The
definition sets the list name, description, template, and columns:
projects_list <- create_sp_list(
definition = projects_definition,
site = sp_site
)
projects_list
#> <Sharepoint list 'Example Projects'>
#> directory id: d0c14ebe-eba8-4dbc-9223-d98937f997cc
#> web link: https://bmore.sharepoint.com/sites/DOP-ALL/Lists/Example%20Projects
#> description: Example list of projects.
#> ---
#> Methods:
#> bulk_import, create_item, delete,
#> delete_item, do_operation, get_column_info,
#> get_item, get_list_pager, list_items,
#> sync_fields, update, update_itemYou can also go the other way: get_sp_list_definition()
returns the definition for an existing list, and
write_sp_list_yaml() writes it to a file. This is a
convenient way to start a YAML file for a list you created in
SharePoint.
Keeping a list definition in a YAML file makes it possible to track
changes to a list in version control. After you edit the definition (in
the file or, as in this example, in R), compare_sp_list()
shows the differences from the existing list:
# Add a choice and a new column
projects_definition$columns[[4]]$choice$choices <- c(
"Planning", "Active", "On Hold", "Closed"
)
projects_definition$columns <- c(
projects_definition$columns,
list(create_text_column("ProjectLead", display_name = "Project Lead"))
)
projects_changes <- compare_sp_list(
projects_definition,
sp_list = projects_list
)
projects_changes[, c("object", "name", "action", "property", "method")]
#> object name action property method
#> 1 column ProjectStatus update choice.choices graph
#> 2 column ProjectLead add <NA> graphsync_sp_list() makes the changes. By default, it only
reports the planned changes (dry_run = TRUE) and doesn’t
delete columns or views that are missing from the definition
(delete = FALSE). Changes that may cause data loss (such as
switching a column from multiple lines of text to a single line) are
skipped unless allow_data_loss = TRUE, and changes that
SharePoint doesn’t allow (such as changing a column type) are reported
as blocked.
sync_sp_list(
projects_definition,
sp_list = projects_list,
dry_run = FALSE
)
#> ✔ Updated "Example Projects": 2 columns, 0 views, and 0
#> folders.A definition can also include list views using an optional
views key. See read_sp_list_yaml() for details
on the format.
create_sp_list_items() adds the rows of a data frame (or
a list of records) to a list. Column names must match the internal
column names for the list (or set allow_display_nm = TRUE
to also match display names):
projects <- data.frame(
Title = c("Main Street Plan", "Harbor Park", "Library Renovation"),
ProjectID = c("P-001", "P-002", "P-003"),
ProjectStatus = c("Planning", "Active", "On Hold"),
StartDate = as.Date(c("2026-01-15", "2025-06-01", "2026-03-01")),
Budget = c(250000, 1200000, 800000),
IsActive = c(FALSE, TRUE, FALSE)
)
create_sp_list_items(projects, sp_list = projects_list)
project_items <- list_sp_list_items(
sp_list = projects_list,
select = c("id", "Title", "ProjectID", "ProjectStatus")
)
project_items
#> Title ProjectID ProjectStatus id
#> 1 Library Renovation P-003 On Hold 1
#> 2 Harbor Park P-002 Active 2
#> 3 Main Street Plan P-001 Planning 3To update items, include the item id in an id column (or
set .id to use another column name). If you use
select when reading items, include "id" to
keep the id column. Columns that aren’t included are left unchanged, and
NA values are skipped unless
na_fields = "replace":
project_updates <- project_items[, c("id", "ProjectID", "ProjectStatus")]
project_updates$ProjectStatus[project_updates$ProjectID == "P-001"] <- "Active"
project_updates$ProjectStatus[project_updates$ProjectID == "P-003"] <- "Active"
update_sp_list_items(
project_updates[, c("id", "ProjectStatus")],
sp_list = projects_list
)delete_sp_list_items() takes a vector of item ids, a
data frame with an id column, or a filter:
delete_sp_list_items(
project_items[project_items$ProjectID == "P-002", ],
sp_list = projects_list,
confirm = FALSE
)
list_sp_list_items(
sp_list = projects_list,
select = c("id", "Title", "ProjectStatus")
)
#> Title ProjectStatus id
#> 1 Library Renovation Active 1
#> 2 Main Street Plan Active 3create_sp_list_items() can also create a list and
populate it with items in a single function call by setting
create_list = TRUE. The column definitions are created from
the data with data_as_column_definition_list():
create_sp_list_items(
list_name = "penguins",
description = "Measurements of Penguins near Palmer Station, Antarctica",
create_list = TRUE,
data = penguins,
site = sp_site
)By default, create_sp_list_items(),
update_sp_list_items(), and
delete_sp_list_items() send items with Microsoft Graph $batch
requests that combine up to 20 items in each request. This is much
faster than sending a request for each item. If you set the number of
daemons (persistent background processes) using
mirai::daemons(), the batch requests are also sent in
parallel with purrr::in_parallel():
If the Graph API throttles a request, it is sent again after the requested delay. If some items fail, the other items are still created, updated, or deleted and the error lists the items that failed.
Items created in the same batch request may not get ids in the same
order as the rows in your data. If the order of ids matters, set
.batch = FALSE (or
options(sharepointr.batch = FALSE)) to send a request for
each item.
create_sp_list_items() offers special handling for sf
objects: the geometry column is converted to well-known text using
sf::st_as_text(). The CRS is not stored as part of the list
so the CRS must be specified when reading the data back into a sf
object.
nc <- sf::st_read(system.file("shape/nc.shp", package = "sf"), quiet = TRUE)
create_sp_list_items(
list_name = "North Carolina SIDS data",
description = "Sudden Infant Death Syndrome (SIDS) sample data for North Carolina counties, two time periods (1974-78 and 1979-84).",
create_list = TRUE,
data = nc[, c(1, 2, 3)],
site = sp_site
)
nc_sp_list <- get_sp_list(
list_name = "North Carolina SIDS data",
site = sp_site
)
nc_list_items <- read_sharepoint(nc_sp_list)
sf::st_as_sf(
nc_list_items,
crs = sf::st_crs(nc),
wkt = "geometry"
)
#> Simple feature collection with 100 features and 27 fields
#> Geometry type: MULTIPOLYGON
#> Dimension: XY
#> Bounding box: xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS: NAD27
#> First 10 features:
#> Title _ColorTag ComplianceAssetId AREA PERIMETER CNTY_
#> 1 <NA> NA NA 0.070 2.968 1831
#> 2 <NA> NA NA 0.063 1.000 1881
#> 3 <NA> NA NA 0.153 2.206 1832
#> 4 <NA> NA NA 0.143 1.663 1840
#> 5 <NA> NA NA 0.081 1.288 1880
#> 6 <NA> NA NA 0.109 1.325 1841
#> 7 <NA> NA NA 0.124 1.428 1837
#> 8 <NA> NA NA 0.062 1.547 1834
#> 9 <NA> NA NA 0.091 1.284 1835
#> 10 <NA> NA NA 0.097 1.670 1833
#> id ContentType Modified Created
#> 1 1 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 2 2 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 3 3 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 4 4 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 5 5 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 6 6 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 7 7 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 8 8 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 9 9 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> 10 10 Item 2026-10-15T02:19:24Z 2026-10-15T02:19:24Z
#> AuthorLookupId EditorLookupId _UIVersionString
#> 1 280 280 1.0
#> 2 280 280 1.0
#> 3 280 280 1.0
#> 4 280 280 1.0
#> 5 280 280 1.0
#> 6 280 280 1.0
#> 7 280 280 1.0
#> 8 280 280 1.0
#> 9 280 280 1.0
#> 10 280 280 1.0
#> Attachments Edit LinkTitleNoMenu LinkTitle DocIcon
#> 1 FALSE NA NA NA
#> 2 FALSE NA NA NA
#> 3 FALSE NA NA NA
#> 4 FALSE NA NA NA
#> 5 FALSE NA NA NA
#> 6 FALSE NA NA NA
#> 7 FALSE NA NA NA
#> 8 FALSE NA NA NA
#> 9 FALSE NA NA NA
#> 10 FALSE NA NA NA
#> ItemChildCount FolderChildCount _ComplianceFlags
#> 1 0 0
#> 2 0 0
#> 3 0 0
#> 4 0 0
#> 5 0 0
#> 6 0 0
#> 7 0 0
#> 8 0 0
#> 9 0 0
#> 10 0 0
#> _ComplianceTag _ComplianceTagWrittenTime
#> 1
#> 2
#> 3
#> 4
#> 5
#> 6
#> 7
#> 8
#> 9
#> 10
#> _ComplianceTagUserId _IsRecord AppAuthorLookupId
#> 1 NA 61
#> 2 NA 61
#> 3 NA 61
#> 4 NA 61
#> 5 NA 61
#> 6 NA 61
#> 7 NA 61
#> 8 NA 61
#> 9 NA 61
#> 10 NA 61
#> AppEditorLookupId geometry
#> 1 61 MULTIPOLYGON (((-76.00897 3...
#> 2 61 MULTIPOLYGON (((-76.48053 3...
#> 3 61 MULTIPOLYGON (((-77.21767 3...
#> 4 61 MULTIPOLYGON (((-78.74912 3...
#> 5 61 MULTIPOLYGON (((-81.80622 3...
#> 6 61 MULTIPOLYGON (((-78.8068 36...
#> 7 61 MULTIPOLYGON (((-80.02567 3...
#> 8 61 MULTIPOLYGON (((-76.00897 3...
#> 9 61 MULTIPOLYGON (((-76.56251 3...
#> 10 61 MULTIPOLYGON (((-76.74506 3...