Power Automate flow, creating list item via http request using more complex columns than single-line strings

Question

Working on a Sharepoint/Power Automate situation that is causing some issues. I have an automation that copies multiple of a certain file type to a list for easier processing. The users upload a main Excel file, and a bunch of other files for reference. A list of just Excel files is the end result so my colleague doesn't have to click through dozens of folders to get the main files. Also the list itself uses folders so the only way to get the creation of items to work is via "Send an HTTP request to SharePoint," or so I've read. The gist of the code is as follows.

{
  "listItemCreateInfo": {
    "FolderPath": {
      "DecodedUrl": "https://<sharepoint domain>/sites/<sharepoint site>/lists/<list name>/<Folder>"
    },
    "UnderlyingObjectType": 0
  },
  "formValues": [
    {
      'FieldName': 'Title',
      'FieldValue': '<Variable for filename>'
    },
    {
      'FieldName': 'LinkBackToOriginalFile',
      'FieldValue': '<URL/Description Object>' # This part doesn't work
    }
  ],
  "bNewDocumentUpdate": false
}

That's basically copied from multiple sources on the Internet. Everyone stops at columns that are strings. Including here, and the official Microsoft Docs. Problem is, I have complex columns, including the LinkBackToOriginalFile URL column. The basics are all working. But anything other than a simple string in that spot, returns an error.

When doing an update, it all works... because the formValues/FieldValue aren't used. The fieldName and fieldValue are simply explicit keys/values in JSON. And it looks like the formValues structure is locked to simple strings. Because when you use something other than a string in the fieldValue you get:

An unexpected 'StartObject' node was found when reading from the JSON reader. A 'PrimitiveValue' node was expected.

I understand it's looking for something like a basic string. But I can't imagine that's the only data that can be passed. So perhaps I'm missing something else? An alternative syntax, a proper way to "stringify" the JSON (my experiments with that result in different errors)?

I'm sure it's something silly, but I haven't found any breadcrumbs toward a solution. There are even fun things, like how Power Automate does some things for you, X-RequestDigest being auto included, for example. So sometimes solutions for regular API requests don't work as expected in Power Automate.

For now I've left the URL field out of the create "branch" and just insert it on subsequent "updates" when the script is run again and potential metadata updates are copied over. Any insights would be welcome. Thanks!

Answer 1

This is how I was able to update a URL field using the "send an HTTP request to SharePoint" action:

HTTP Action parameters

• Method: POST
• URL: _api/web/lists/getByTitle('LIST TITLE')/getItemById(ITEM_ID)

Headers

• content-type: application/json;odata=verbose
• X-HTTP-Method: MERGE
• IF-MATCH: *

Body

{
 "__metadata": { "type": "SP.Data.LIST_x0020_NAMEListItem" },
  "FlowHistoryFieldNAME": {
                    '__metadata': { 'type': 'SP.FieldUrlValue' },
                    'Description': 'Hyperlink to google',
                    'Url': 'https://www.google.com'
  }
}

Note the SP.Data.LIST_x0020_NAMEListItem you need to replace this with the correct Item type Entity name for your list.

How to get Item type Entity name

To get the List Item type Entity name (ListItemEntityTypeFullName), you can

• take the list's URL and replace all spaces with x0020
• Alternatively, you get the ListItemEntityTypeFullName by calling the following URL: /api/web/lists/getbytitle('LIST TITLE')/ListItemEntityTypeFullName