How to bulk insert insert images into Sharepoint Lists using Power Automate

Ever get really annoyed with how hard it was to find the solution to a simple problem that you want to write a LinkedIn article about it? No? Just me then

I seem to be using Sharepoint lists more and more these days as an easy way to build tools to replace spreadsheets. One of the great features of Lists is the ability to inset images so that you can spruce up your Gallery or Kanban views. Often when I'm setting up these types of lists I need to bulk upload some initial data, sometimes in the thousands of records.

Unfortunately unlike competitors in this space; like Airtable or Coda the API for Sharepoint lists is pretty poorly documented and has been through so many revisions it's hard to tell why something isn't working. So without further ado, after hours of pain this is how to bulk import images into Sharepoint Lists

Start with a Table in Excel

First up you'll want to get your data set up, for ease I've gone with a Table in Excel saved on Sharepoint, it looks a bit like this:

You might notice I'm hosting my images on Azure blob storage, there's also lots of clever tricks if you want to use a SERP and grab images directly from google image results based on a search query.

Set up Sharepoint

Sharepoint works best if the files you are trying to attach to a List item are already on Sharepoint, so you'll need to do a few things here:

• Set up your List in Sharepoint
• Set up a landing folder for your images (I like to use "Site Assets/List Data")
• Get the Entity Type name of your new list, this is important if you have used spaces in your list title. You can do this by visiting https://{sharepointDomain}.sharepoint.com/sites/{sharepointSite}/_api/web/lists/getbytitle('{listName} ')

Create your flow

Your flow will follow these steps:

1. Get your Excel Table
2. For each row in your table, create an item in your List
3. Get the content of the image using HTTP GET
4. Create your image in Sharepoint (into the landing folder detailed in the first section), using the filename from your spreadsheet to make life easier

So far so good, all of this is using out of the box steps, you can even set the concurrency on the "For Each" loop so that it can run up to 20 records simultaneously.

Now for the final step, the horrible bit that caused me no end of problems, you need to run a "Send HTTP request to Sharepoint" node that will look like this:

Let's break that down, the URL should be as below, replaceing the "getbytitle" value with your own List Name. As you can see the ID is carried over from the step where we created the record.

_api/web/lists/getbytitle('Example List')/items(@{body('Create_item')?['ID']})         

Now for the headings, for those that have done this before you'll know that headings in Sharepoint HTTP requests can make or break your mood, reading error messaging is like talking to someone in riddles. So for this request we need the following:

{ 
"content-type": "application/json;odata=verbose",
"accept": "application/json;odata=verbose",
"X-HTTP-Method": "MERGE",
"If-Match": "*"
}        

One or more of these may not be essentially, but all I know is it's working and I don't want to break it.

Finally onto the body, it's very important to follow the following notes:

• metadata.type is "SP." followed by the Entity Asset name from before, followed by "Item". I have the word List repeated twice as my list is called "Example List"
• The "Image" object that follows is called "Image" because that it the name of my column, it is also repeated in the "fieldName" inside the stringified Json.
• serverUrl should be your root sharepoint domain
• The two instances of {filename} below should be your file name from the intial spreadsheet
• You can set this step up to run even if the previous step of uploading your image fails, that way if it is a duplicate filename the original will be attached.

{ 
"__metadata": 
  { 
    "type": "SP.Data.Example_x0020_ListListItem" 
  },
"Image": "\"type\":\"thumbnail\",\"fileName\":\"{filename}\",\"fieldName\":\"Image\",\"serverUrl\":\"https://shiftconstruction.sharepoint.com\",\"serverRelativeUrl\":\"/sites/ListTest/SiteAssets/List%20Data/{filename}\"}"
}        

Any voila, you should see all of your images pop up inside your list (put it in Gallery view to look extra swish)

If you have any questions, please don't message me. I hate the Sharepoint API and don't want to look at it any more than I already have.