I have this Logic App that reads some 1000+ items from a SharePoint list and outputs it. For about 2000 items it was taking roughly 30 mins. Initially I thought it must be due to some slowness with the SharePoint connector; this list has some 20,000+ items after all.
Then I noticed that the SharePoint part finishes in a minute. I then have this loop that appends each item to an array. The append step was taking 1 second. Not a big deal in itself, but that’s 2000 seconds :) or about 33.3 minutes! Damn.
Googling bought me to this informative forum post. Replace the for loop + append to array item with a single Select action. The Select action takes as input whatever you give it – in this case the list of items from SharePoint – and then you can create a JSON out of the values you want.
Above I create a property called “EmailAddress” whose value is item()?['EmailAddress']. It item() function iterates through each element and extracts the specified property. The output is a JSON array, neat huh. This brings it down to about 20 seconds rather than 2000 seconds! Madness. :)
You can have the input be any other array too. For instance, I realized that with SharePoint if a particular column doesn’t have a value it is not present in the output of that entry. That is to say, my output from the SharePoint search could be, for instance, something like:
item 1 with Name, Email Address, Telephone Number fields;item 2 with Name, Email Address fields;item 3 with Name, Telephone Number fields
It’s not that the fields without any value simply have a null value, they are not there in the first place.
This throws up Logic Apps and Power Automate. You get errors like: The template language function 'replace' expects its first parameter 'string' to be a string. The provided value is of type 'Null'.
Stupid thing.
So I decided to filter the output from my SharePoint query using Filter Array.
The input is the same list of values I used previously with Select. For the condition I did: empty(item()?['EmailAddress']) and tested that is not true. I had to use the empty() operator as that’s the only one that could handle emptiness. :) Others like length() failed as they couldn’t handle the missing field (as it would be null rather than a string).
I can then pass the output of this to the previous Select operator.
