---
title: "Reading and writing to SharePoint Lists"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{sp-lists}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---



<!--
Note: this vignette is pre-computed to utilize the Microsoft Graph login that is only available locally to me as a logged in SharePoint user.

``` r
# Run to update vignette
knitr::knit(
  "vignettes/sp-lists.Rmd.orig",
  "vignettes/sp-lists.Rmd"
)
```
-->


``` r
library(sharepointr)
options(cli.default_handler = suppressMessages)
```

## Overview

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:

- Reading lists and list items
- Creating lists and list items

The package also supports updating and deleting lists and list items which should be added to this article in the future.

## Reading lists and list items

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:


``` r
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:38d6c3c5ffd048d091655fde4e10fa2f@thread.skype
#> 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:78556da3d0db4ddabfb7d8807110cc60@thread.skype
#> 6                                                                        Canvas items for channel 19:1a54f93ccf1244cda3547deae96e306a@thread.skype
#>                                        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
#> 1 531a153d-b9d4-4d04-93cd-0d49d9bac705
#> 2 bdd11473-562b-43bc-b498-0f235aef9e4a
#> 3 0d7df1be-e6de-474b-88ea-0f56689ca3e9
#> 4 7c6b3bac-4962-4285-980e-127e36583ffc
#> 5 9c518bac-ef99-4ae8-ab27-1453f68c87e1
#> 6 d216b2d5-7cae-491f-abf4-1474628a0982
#>   lastModifiedDateTime
#> 1 2026-05-13T11:42:10Z
#> 2 2026-05-22T13:37:34Z
#> 3 2024-10-02T03:22:07Z
#> 4 2026-05-01T14:17:23Z
#> 5 2024-10-02T03:22:07Z
#> 6 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_wiki
```

However, 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.


``` r
sp_site <- get_sp_site(
  "https://bmore.sharepoint.com/sites/DOP-ALL"
)
#> Loading Microsoft Graph login for default tenant

site_lists <- list_sp_lists(site = sp_site)

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_item
```

To get the *items* from a list as a data frame, you need to use the `list_sp_list_items()` function. This function allows you to pass a site and list name, a list URL in place of a list name with no site, or to pass a list object directly:


``` r
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(2:8)])
#>      Title LinkTitle   RoomType
#> 1 08-COM02  08-COM02   CORRIDOR
#> 2 08-COM01  08-COM01   CORRIDOR
#> 3 08-SRV01  08-SRV01    UTILITY
#> 4 08-SRV02  08-SRV02 TELE/COMMS
#> 5 08-SRV03  08-SRV03        MEN
#> 6 08-SRV04  08-SRV04      WOMEN
#>                         Location RoomCategory    UsedBy
#> 1        Hallway to plotter area       COMMON DOP Staff
#> 2                 elevator lobby       COMMON    Public
#> 3 interior hallway west of lobby      SERVICE   Service
#> 4                 public hallway      SERVICE   Service
#> 5                public restroom      SERVICE    Public
#> 6                public restroom      SERVICE    Public
#>            ArchibusAvailability
#> 1 Not available for reservation
#> 2 Not available for reservation
#> 3 Not available for reservation
#> 4 Not available for reservation
#> 5 Not available for reservation
#> 6 Not available for reservation
```

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:


``` r
get_sp_list_item(id = 26, sp_list = room_list)
#> <Sharepoint list item '08-COM02'>
#>   directory id: 26 
#>   web link: https://bmore.sharepoint.com/sites/DOP-ALL/Lists/DOP%20Rooms/26_.000 
#> ---
#>   Methods:
#>     delete, do_operation, get_list_pager,
#>     sync_fields, update
```

## Creating lists and list items

A list name and site (or site URL) are the only requirements to create a list:


``` r
penguins_list <- create_sp_list(
  list_name = "penguins",
  description = "Measurements of Penguins near Palmer Station, Antarctica",
  site = sp_site
)

penguins_list
#> <Sharepoint list 'penguins'>
#>   directory id: 54e0e2ec-8507-4780-bb7d-771916324d3e 
#>   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_item
```

However, a list created using the default `template = "genericList"` has only one column: Title. You can add column to a list before or after it is created. Creating columns required a column definition which can be created using the `create_column_definition()` function or one of the column type-specific variations:


``` r
create_text_column("Text Column", multiple_lines = TRUE)
#> $name
#> [1] "Text Column"
#> 
#> $hidden
#> [1] FALSE
#> 
#> $text
#> $text$allowMultipleLines
#> [1] TRUE
#> 
#> $text$textType
#> [1] "plain"

create_number_column("Number Column", decimals = 0)
#> $name
#> [1] "Number Column"
#> 
#> $hidden
#> [1] FALSE
#> 
#> $number
#> $number$decimalPlaces
#> [1] "none"

create_choice_column("Choice Column", choices = c("Apple", "Pear", "Orange"))
#> $name
#> [1] "Choice Column"
#> 
#> $hidden
#> [1] FALSE
#> 
#> $choice
#> $choice$allowTextEntry
#> [1] TRUE
#> 
#> $choice$choices
#> [1] "Apple"  "Pear"   "Orange"
#> 
#> $choice$displayAs
#> [1] "dropDownMenu"
```

`create_sp_list_column()` takes a column definition and a Microsoft List object and adds a column to the list.

You can specify multiple columns at once using a list or data frame of parameters (leaving blank values where a parameter is not applicable):


``` r
create_column_definition_list(
  definitions = data.frame(
    name = c("Text Column", "Number Column"),
    type = c("text", "number"),
    decimals = c(NA, 0)
  )
)
#> [[1]]
#> [[1]]$name
#> [1] "Text Column"
#> 
#> [[1]]$hidden
#> [1] FALSE
#> 
#> [[1]]$text
#> [[1]]$text$textType
#> [1] "plain"
#> 
#> 
#> 
#> [[2]]
#> [[2]]$name
#> [1] "Number Column"
#> 
#> [[2]]$hidden
#> [1] FALSE
#> 
#> [[2]]$number
#> [[2]]$number$decimalPlaces
#> [1] "none"
```

If you are creating column definitions based on an existing data frame you can alternatively use `data_as_column_definition_list()`:


``` r
penguins_column_defintions <- data_as_column_definition_list(penguins)

str(penguins_column_defintions)
#> List of 8
#>  $ :List of 3
#>   ..$ name  : chr "species"
#>   ..$ hidden: logi FALSE
#>   ..$ choice:List of 3
#>   .. ..$ allowTextEntry: logi TRUE
#>   .. ..$ choices       : chr [1:3] "Adelie" "Chinstrap" "Gentoo"
#>   .. ..$ displayAs     : chr "dropDownMenu"
#>  $ :List of 3
#>   ..$ name  : chr "island"
#>   ..$ hidden: logi FALSE
#>   ..$ choice:List of 3
#>   .. ..$ allowTextEntry: logi TRUE
#>   .. ..$ choices       : chr [1:3] "Biscoe" "Dream" "Torgersen"
#>   .. ..$ displayAs     : chr "dropDownMenu"
#>  $ :List of 3
#>   ..$ name  : chr "bill_len"
#>   ..$ hidden: logi FALSE
#>   ..$ number:List of 1
#>   .. ..$ decimalPlaces: chr "automatic"
#>  $ :List of 3
#>   ..$ name  : chr "bill_dep"
#>   ..$ hidden: logi FALSE
#>   ..$ number:List of 1
#>   .. ..$ decimalPlaces: chr "automatic"
#>  $ :List of 3
#>   ..$ name  : chr "flipper_len"
#>   ..$ hidden: logi FALSE
#>   ..$ number:List of 1
#>   .. ..$ decimalPlaces: chr "none"
#>  $ :List of 3
#>   ..$ name  : chr "body_mass"
#>   ..$ hidden: logi FALSE
#>   ..$ number:List of 1
#>   .. ..$ decimalPlaces: chr "none"
#>  $ :List of 3
#>   ..$ name  : chr "sex"
#>   ..$ hidden: logi FALSE
#>   ..$ choice:List of 3
#>   .. ..$ allowTextEntry: logi TRUE
#>   .. ..$ choices       : chr [1:2] "female" "male"
#>   .. ..$ displayAs     : chr "dropDownMenu"
#>  $ :List of 3
#>   ..$ name  : chr "year"
#>   ..$ hidden: logi FALSE
#>   ..$ number:List of 1
#>   .. ..$ decimalPlaces: chr "none"
```

At present, data frames with character, numeric, datetime, and factor values are supported but list columns are not.

These column definition lists should be provided as the `columns` argument for `create_sp_list()` to create the columns at the same time as the list.

`create_sp_list_items()` also allows users to create a list and populate the list with items in a single function call by setting `create_list = TRUE`:


``` r
create_sp_list_items(
  list_name = "penguins",
  description = "Measurements of Penguins near Palmer Station, Antarctica",
  create_list = TRUE,
  data = penguins,
  site = sp_site
)
```

You can use this same function with `create_list = FALSE` (the default) to add multiple items to a list.
Create individual items using `create_sp_list_item()`.

`create_sp_list_items()` offers special handling for the sfc list columns.
If `data` is an sf object, 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.


``` r
nc <- sf::st_read(system.file("shape/nc.shp", package="sf"))
#> Reading layer `nc' from data source 
#>   `C:\Users\Eli.Pousson\AppData\Local\Programs\R\R-4.5.1\library\sf\shape\nc.shp' 
#>   using driver `ESRI Shapefile'
#> Simple feature collection with 100 features and 14 fields
#> Geometry type: MULTIPOLYGON
#> Dimension:     XY
#> Bounding box:  xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS:  NAD27

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,
  site = sp_site
)
#> ! All column names in `data` must match field names in
#>   the supplied list.
#> ℹ Columns "BIR74", "SID74", "BIR79", and "SID79" dropped
#>   from `data`

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 28 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:
#>                                 @odata.etag  AREA
#> 1  "e05c3fb4-6cda-4145-9a67-c573bc60a84e,1" 0.114
#> 2  "c9a71d83-c08d-4a97-a0d4-0bc90d79ddab,1" 0.061
#> 3  "e4b83774-9603-4806-b99c-51c9a104fd4a,1" 0.143
#> 4  "e2cd1891-9daa-4976-aba3-ad6623cda57a,1" 0.070
#> 5  "f2cb4850-b0e7-49a7-9c22-c453752d7ef9,1" 0.153
#> 6  "80f4c556-132e-4470-bda8-75e32fe07360,1" 0.097
#> 7  "92e451c3-0ffe-4659-a31d-210895c88514,1" 0.062
#> 8  "01db5e8d-4e5a-4f98-bb84-d1ec0659d305,1" 0.091
#> 9  "63b1c806-7530-445f-b0d1-97f29f01e0df,1" 0.118
#> 10 "4270a773-ddc4-4b8e-ba87-fd093f910583,1" 0.124
#>    PERIMETER CNTY_ CNTY_ID        NAME  FIPS FIPSNO
#> 1      1.442  1825    1825        Ashe 37009  37009
#> 2      1.231  1827    1827   Alleghany 37005  37005
#> 3      1.630  1828    1828       Surry 37171  37171
#> 4      2.968  1831    1831   Currituck 37053  37053
#> 5      2.206  1832    1832 Northampton 37131  37131
#> 6      1.670  1833    1833    Hertford 37091  37091
#> 7      1.547  1834    1834      Camden 37029  37029
#> 8      1.284  1835    1835       Gates 37073  37073
#> 9      1.421  1836    1836      Warren 37185  37185
#> 10     1.428  1837    1837      Stokes 37169  37169
#>    CRESS_ID NWBIR74 NWBIR79 id ContentType
#> 1         5      10      19  1        Item
#> 2         3      10      12  2        Item
#> 3        86     208     260  3        Item
#> 4        27     123     145  4        Item
#> 5        66    1066    1197  5        Item
#> 6        46     954    1237  6        Item
#> 7        15     115     139  7        Item
#> 8        37     254     371  8        Item
#> 9        93     748     844  9        Item
#> 10       85     160     176 10        Item
#>                Modified              Created
#> 1  2026-05-27T16:52:04Z 2026-05-27T16:52:04Z
#> 2  2026-05-27T16:52:05Z 2026-05-27T16:52:05Z
#> 3  2026-05-27T16:52:05Z 2026-05-27T16:52:05Z
#> 4  2026-05-27T16:52:05Z 2026-05-27T16:52:05Z
#> 5  2026-05-27T16:52:06Z 2026-05-27T16:52:06Z
#> 6  2026-05-27T16:52:06Z 2026-05-27T16:52:06Z
#> 7  2026-05-27T16:52:06Z 2026-05-27T16:52:06Z
#> 8  2026-05-27T16:52:06Z 2026-05-27T16:52:06Z
#> 9  2026-05-27T16:52:07Z 2026-05-27T16:52:07Z
#> 10 2026-05-27T16:52:07Z 2026-05-27T16:52:07Z
#>    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 ItemChildCount FolderChildCount
#> 1        FALSE                   0                0
#> 2        FALSE                   0                0
#> 3        FALSE                   0                0
#> 4        FALSE                   0                0
#> 5        FALSE                   0                0
#> 6        FALSE                   0                0
#> 7        FALSE                   0                0
#> 8        FALSE                   0                0
#> 9        FALSE                   0                0
#> 10       FALSE                   0                0
#>    _ComplianceFlags _ComplianceTag
#> 1                                 
#> 2                                 
#> 3                                 
#> 4                                 
#> 5                                 
#> 6                                 
#> 7                                 
#> 8                                 
#> 9                                 
#> 10                                
#>    _ComplianceTagWrittenTime _ComplianceTagUserId
#> 1                                                
#> 2                                                
#> 3                                                
#> 4                                                
#> 5                                                
#> 6                                                
#> 7                                                
#> 8                                                
#> 9                                                
#> 10                                               
#>    AppAuthorLookupId AppEditorLookupId
#> 1                 61                61
#> 2                 61                61
#> 3                 61                61
#> 4                 61                61
#> 5                 61                61
#> 6                 61                61
#> 7                 61                61
#> 8                 61                61
#> 9                 61                61
#> 10                61                61
#>                          geometry
#> 1  MULTIPOLYGON (((-81.47276 3...
#> 2  MULTIPOLYGON (((-81.23989 3...
#> 3  MULTIPOLYGON (((-80.45634 3...
#> 4  MULTIPOLYGON (((-76.00897 3...
#> 5  MULTIPOLYGON (((-77.21767 3...
#> 6  MULTIPOLYGON (((-76.74506 3...
#> 7  MULTIPOLYGON (((-76.00897 3...
#> 8  MULTIPOLYGON (((-76.56251 3...
#> 9  MULTIPOLYGON (((-78.30876 3...
#> 10 MULTIPOLYGON (((-80.02567 3...
```

Creating or updating large numbers of items can be very slow.
If you are creating a large number of items, you can use the parallelization feature built-in to the pakage via `purrr::in_parallel()`.
Set the number of daemons, or persistent background processes, to handle the requests in parallel using `mirai::daemons()` before calling `create_sp_list_items()` and the process should speed up.


