HTTP request to SharePoint - Files and Items

☝

TL;DR - Sometimes built-in actions for SharePoint just don’t cut it. You need more options and more performance. This is why directly using the REST API from SharePoint just makes sense with files and items. Here are some of my most used commands.

Before you get started

Just like the filter queries in built-in actions, you will always have to use a columns internal names to create, select, filter or update its values
Responses directly from the REST API look different from the built-in connector responses. You will need to parse the responses from that action either by parsing manually or with a “Parse JSON” action. Take your time to understand the response schema.

//object outputs (where the action only returns one thing)
body('YOURACTION')?['d']
//array outputs (where the action returns a list of results)
body('YOURACTION')?['d']?['results']

Read more about the REST API

Getting items or files

Method: GET
//This gets you all items
Uri: _api/web/lists(guid'LIST GUID')/items
//returning a specific item by its ID
_api/web/lists(guid'LIST GUID')/items(1)
Headers: 
{
  "Accept": "application/json; odata=verbose",
  "Content-Type": "application/json; odata=verbose"
}

Returning only specific columns

//Only select a specific properties
_api/web/lists(guid'LIST GUID')/items?$select=FileLeafRef,Author,Editor,CustomField
//If you only need to return one property
_api/web/lists(guid'LIST GUID')/items/FileLeafRef

Filtering the items or files

//text
_api/web/lists(guid'LIST GUID')/items?$filter=intItemName eq 'Desk'
//number
Number: _api/web/lists(guid'LIST GUID')/items?$filter=intItemPrice eq 999
//lookup, for all lookups there is a column that is called ColumnNameId
Lookup Column: _api/web/lists(guid'LIST GUID')/items?$filter=LookupColumnId eq 2
//not null
?$filter=cmsVertragsleserId ne null
//using starts with on texts
_api/web/lists(guid'LIST GUID')/items?$filter=startswith(textColumn,'Alex')
//using starts with content ype
_api/web/lists(guid'LIST GUID')/items?$filter=startswith(ContentTypeId,'0x0120')

Returning more data

The REST API does a much better job return large results. Its faster and more reliable. I managed to return up to 20.000 items with one action.

//using top to return as much data as possible
_api/web/lists(guid'LIST GUID')/items?$top=20000
//using RenderListDataAsStream
_api/web/GetListUsingPath(DecodedUrl=@a1)/RenderListDataAsStream?@a1=%27%2Fsites%2FQMS%2FSitePages%27&View={VIEWID}&FilterField1=qmsProcessOwner&FilterValue1={USER DISPLAY NAME}&FilterType1=UserMulti

Ordering the results

_api/web/lists(guid'LIST GUID')/items?$filter=startswith(ContentTypeId,'0x0120')&$orderby=Id

Creating or updating items or files

Before you start

Creating list items as well as one of the update methods require you to use the lists unique entity type. It includes the lists name.

Get the ListItemEntityTypeFullName

Many times, this specific name is used to update files or list items. Depending on the name of your list or library this name will change.

Method: GET
Uri: _api/web/lists(guid'LIST GUID')/ListItemEntityTypeFullName
Headers:
{
  "Accept": "application/json; odata=verbose",
  "Content-Type": "application/json; odata=verbose"
}
//From here you can either use Parse JSON or directly paste this expression to get the entity name. 
//Make sure to adjust the name accordingly to the actions name.
body('Send_an_HTTP_request_to_SharePoint_-_List_Item_Entity')?['d']?['ListItemEntityTypeFullName']

Create list items

Method: POST
Uri: _api/web/lists(guid'LIST GUID')/items
Headers:
{
  "Accept": "application/json; odata=verbose",
  "Content-Type": "application/json; odata=verbose"
}
Body:
{
  "__metadata": {
    "type": "ListItemEntityTypeFullName"
  },
  "SinglelineText": "Text",
  "MultilineText": "Text",
  "DateTime": "2024-06-28T12:00:00",
  "Boolean": true,
  "ChoiceSingle": "Auswahl 1",
  "ChoiceMultiple": {
    "results": [
      "Auswahl 2",
      "Auswahl 3"
    ]
  },
  "NumberWhole": 4,
  "NumberFloat": 0.5,
  "Hyperlink": {
    "Description": "Microsoft Blog",
    "Url": "https://robertheep.de"
  },
  "PersonSingleId": 9,
  "PersonMultipleId": {
    "results": [
      13,
      9
    ]
  },
  "LookupSingleId": 1,
  "LookupMultipleId": {
    "results": [
      1,
      2
    ]
  },
  "ManagedMetadataSingle": {
    "Label": "DEPARTMENT A",
    "TermGuid": "d032fdd4-e577-4fac-ba2f-46cfd71691cc",
    "WssId": -1
  },
  //This is ManagedMetadataMultiple
  "g54f214daf9b4708baf975b5d6ff5d12": "-1;#DEPARTMENT A|d032fdd4-e577-4fac-ba2f-46cfd71691cc;-1;#DEPARTMENT B|691ead56-98ad-49ae-85da-067e24842f72"
}

Information regarding PersonFields and ManagedMetadataMultiple

For Person fields you will need the internal SharePoint User or group Id, this is not the Entra Id. SharePoint will also only give people or groups an Id, if they have been previously used in a person column. Otherwise, you will not get any id. A workaround can be a seperate hidden list, where you quickly add that person to a person column to get the id.

Creating (and updating) data in Managed Metadata columns that accept multiple values is an absolute nightmare to work with. SharePoint expects its internal name for your managed metadata column(s) and the values as a string. Which is not at all similar to the single choice managed metadata.

Update list items or file properties

There are two ways to update an item or a file.

	Syntax from create list item	Syntax with ValidateUpdateListItem()
PRO	1. Same syntax as the create list item request 2. DateTime columns have a reasonable format 3. Numbers and boolean values do not need “” to work	1. Only uses the columns internal name (no looking up the “real” internal names for managed metadata) 2. Uses claim based values for Person and Group columns 3. lets you update without adding a new version (bNewDocumentUpdate = true)
CONTRA	1. Uses Ids for person and group columns 2. requires you to get “special internal names” for all 3. managed metadata columns that allow multiple values 4. requires the EntityTypeFullName	1. Values for columns are more complicated to build 2. DateTime values are not norm 3. Just because it was successful doesn't mean everything was updated accordingly. (If the input is right, it will work well)

‣

Option 1: Update list or file properties with create schema

Option 2: Update list or file properties (recommended)

The boolean value for “bNewDocumentUpdate” creates a new item version, when false and doesn’t when set to true. If no new version is added, you wont see any changes when looking at the version history. Even though this parameter doesn’t create a new version, it will still trigger automated flows like “When an item is created or modified”.

⚠️

This issue has been around for a while now, for some people it somehow works, for other it doesn’t. See here:

Method: POST
Uri: _api/web/lists(guid'LIST GUID')/items(ID)/ValidateUpdateListItem()
Headers:
{
  "Accept": "application/json; odata=verbose",
  "Content-Type": "application/json; odata=verbose"
}
Body:
{
      "formValues": [
            {
                  "FieldName": "SinglelineText",
                  "FieldValue": "Text"
            },
 {
                  "FieldName": "MultilineText",
                  "FieldValue": "bla bla"
            },
 {
                  "FieldName": "DateTime",
                  "FieldValue": "2/23/2012 2:25 PM"
            },
 {
                  "FieldName": "Boolean",
                  "FieldValue": "false"
            },
 {
                  "FieldName": "ChoiceSingle",
                  "FieldValue": "Auswahl 6"
            },
 {
                  "FieldName": "ChoiceMultiple",
                  //The first value is always without #, the following values are seperated by ;#CHOICE
                  "FieldValue": "Auswahl 8;#Auswahl 10"
            },
 {
                  "FieldName": "NumberWhole",
                  //Numbers need to be put in double quotes aswell
                  "FieldValue": "69"
            },
 {
                  "FieldName": "NumberFloat",
                  //Numbers need to be put in double quotes aswell
                  "FieldValue": "0.69"
            },
 {
                  "FieldName": "Hyperlink",
                  "FieldValue": "https://www.google.com, Link to Google"
            },
{
                  "FieldName": "PersonSingle",
                  //see note below
                  "FieldValue":"[{'Key':'i:0#.f|membership|user1@domain.com'}]"

            },
{
                  "FieldName": "PersonMultiple",
                  //see note below
                  "FieldValue":"[{'Key':'i:0#.f|membership|user1@domain.com'},{'Key':'i:0#.f|membership|user2@domain.com'}]"

            },
 {
                  "FieldName": "LookupSingle",
                  //Numbers need to be put in double quotes aswell
                  "FieldValue": "2"
            },
 {
                  "FieldName": "LookupMultiple",
                  //The first values is always without #, the following values are seperated by ;#;#NUMBER
                  "FieldValue": "1;#;#2;#;#3"
            },
 {
                  "FieldName": "ManagedMetadataSingle",
                  "FieldValue": "DEPARTMENT B|691ead56-98ad-49ae-85da-067e24842f72"
            },
 {
                  "FieldName": "ManagedMetadataMultiple",
                  "FieldValue": "DEPARTMENT B|691ead56-98ad-49ae-85da-067e24842f72;DEPARTMENT A|d032fdd4-e577-4fac-ba2f-46cfd71691cc"
            }
      ],
      "bNewDocumentUpdate": true
}

Notes about claim based person and group columns

There is two major differences with claim based columns. For this you will need specific values to either update the column with:

a Person

i:0#.f|membership|user@domain.com

a Microsoft 365 group (Teams or SharePoint)

c:0o.c|federateddirectoryclaimprovider|GROUP-ID

a Security group from Entra ID

c:0t.c|tenant|GROUP-ID

Deleting files or items

Method: DELETE
Uri: _api/web/lists(guid'LIST GUID')/items(ID)
Headers:
{
"If-Match":"*"
}

Delete file or item without it going to the recycle bin

The file or item wont show in the first or second stage recycle bin.

Method: DELETE
Uri: _api/web/lists(guid'LIST GUID')/items(ID)
Headers:
{
"If-Match":"ETAG of the file or item"
}

Copying files and pages

Moving files or pages isn’t always available with the UI. You can easily use this HTTP request with Power Automate to move or copy files from one site collection to another.

And the good part about this, is that is also works for site pages. Remove the // comments when pasting the code in your actions.

https://learn.microsoft.com/en-us/sharepoint/dev/apis/spod-copy-move-api


POST
_api/site/CreateCopyJobs

{  
   "exportObjectUris":[  
      "https://yourcompany.sharepoint.com/sites/QMS/SitePages/de/Home.aspx"
   ],
   "destinationUri":"https://yourcompany.sharepoint.com/sites/QMS/SitePages",
   "options":{  
      "IgnoreVersionHistory":true, //version history will not be moved if set to false
      "IsMoveMode":true, //if false, its going to copy, otherwise its a move (copy and delete)
      "AllowSchemaMismatch": false, //if two lists have a different set of metadata, or one of the lists doesnt have any
      "IncludeItemPermissions":true, //if there is unique item permissions that need to be kept, only works for files
      "NameConflictBehavior":1, //only on files or pages, NOT folders, 1 = REPLACE and 2 = KEEP BOTH
      "BypassSharedLock ":true //bypasses any shared use locks
   }
}

Checking copy job progress

In case you are copying folders or multiple files, you want to check the progress before continuing in your flow. The request to copy is basically fire and forget as long as there are not major request errors.

Method: POST
Uri: _api/site/GetCopyJobProgress

Moving files or folders by path

Technically uses the copy job API in the backend. You can only copy or move one item with one request. The request above allows you to move multiple items to a single destination.

Method: POST
Uri: _api/SP.MoveCopyUtil.MoveFileByPath(overwrite=@a1)?@a1=true
// also works for copying: _api/SP.MoveCopyUtil.CopyFileByPath()

Body: {
    "srcPath": {
        "__metadata": {
            "type": "SP.ResourcePath"
        },
        "DecodedUrl": "https://tenant.sharepoint.com/Shared Documents/FileName.docx"
    },
    "destPath": {
        "__metadata": {
            "type": "SP.ResourcePath"
        },
        "DecodedUrl": "https://tenant.sharepoint.com/Shared Documents/FolderName/FileName.docx"
    }
}

Checkout site page

This method only works for site pages and only when checking out.

Method: POST
Uri: _api/SitePages/Pages(PAGEID)/CheckoutPage
Headers: 
{
  "Accept": "application/json; odata=verbose",
  "Content-Type": "application/json; odata=verbose"
}

Check in/out document

Can also be done with built-in actions.

0 = Minor version

1 = Major version

2 = Overwrite file

Method: POST
Uri: _api/web/lists(guid'LISTGUID')/items(ITEMID)/CheckIn(comment='Comment',checkintype=0)
Headers: 
{
  "Accept": "application/json; odata=verbose",
  "Content-Type": "application/json; odata=verbose"
}

Method: POST
Uri: _api/web/lists(guid'LISTGUID')/items(ITEMID)/CheckOut
Headers: 
{
  "Accept": "application/json; odata=verbose",
  "Content-Type": "application/json; odata=verbose"
}