I’ve already written a couple of posts about how to sum arrays in Power Automate, but this post is specifically about how to sum a column from a SharePoint list. If you want to sum an array that has not originated from a SharePoint list, check out my other posts:
It has always surprised me that there isn’t an easy way to do this built into Power Automate, but I stumbled across a simple solution. I was working with a list that had a view with a Total applied to it and it occurred to me that if the total is available in a view, then it must also be available via an API.
This same method can be used to perform many different types of aggregations on SharePoint lists.
So the first step in this process is to modify your SharePoint List view so that it has a total applied to it.
Get the Colum Total from the SharePoint List View
Once you have modified your view to have a total you need to collect three bits of information:
- The Site Path.
- The List Name.
- The View ID.
Once you have that information create a compose action called Settings and populate it with the information you collected above. Here is the code from my example:
{
"sitePath": "/sites/PowerAutomateText/",
"listName": "Invoices",
"viewID": "576393ba-5827-4100-a157-7d901b344ca8"
}
and a screenshot of the action:
Next add a Send an HTTP request to SharePoint action, select the site address and set the method to POST, in the Uri field paste the following code:
_api/web/lists/GetByTitle('@{outputs('Settings')['listName']}')/RenderListDataAsStream?View=@{outputs('Settings')['viewID']}
As you can see, I am using the RenderListDataAsStream API action, which you can do a lot with and it is worth reading the documentation.
In the headers section add a header named accept with the value of application/json;odata=nometa
The action should look like this:
This action will bring you back everything that you would see in the browser when looking at the same view. It is important to understand that this will bring view results, not the standard list of items that you would receive with the Get Items action.
The output of this action produces an array called Row which in addition to the contents of the view shows the aggregated total on each row. Here is a sample of the output produced by my action:
{
"Row": [{
"ID": "27694",
"PermMask": "0x7ffffffffffbffff",
"FSObjType": "0",
"UniqueId": "{7CF06512-7444-441F-9D6B-08610D1BB415}",
"ContentTypeId": "0x0100EA3A251D88BC5941A590C41BA4B80EE700F04B73CD6F513D44943FDF3FE121DF77",
"FileRef": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
"FileRef.urlencode": "%2Fsites%2FPowerAutomateText%2FLists%2FInvoices%2F27694%5F%2E000",
"FileRef.urlencodeasurl": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
"FileRef.urlencoding": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
"Attachments": "0",
"SMTotalSize": "218",
"_CommentFlags": "",
"_CommentCount": "",
"InvNum": "1",
"InvNum.": "1.00000000000000",
"Customer": "Ledner Inc",
"Invoice_x0020_Amount": "804.39",
"Invoice_x0020_Amount.": "804.390000000000",
"Date": "6/25/2015",
"Date.": "2015-06-25T07:00:00Z",
"ItemChildCount": "0",
"FolderChildCount": "0",
"owshiddenversion": "1",
"Restricted": "",
"Invoice_x0020_Amount.SUM": "5,027,707.52"
}]
}
As you can see, on line 27 of the output there is a an object element named Invoice_x0020_Amount.SUM which we can get with a simple compose action:
Because the result from SharePoint is an array, the compose action just takes the first result and retrieves the Total from there, here is the code I used as reference. Obviously you will need to update it to reflect whatever your field name is:
float(outputs('Send_an_HTTP_request_to_SharePoint')?['body']['Row'][0]['Invoice_x0020_Amount.SUM'])
Other Possible Uses
This technique can be used in a number of other ways to get results that would otherwise require much more effort. The standard aggregations in a SharePoint view are:
- Count
- Average
- Maximum
- Minimum
- Sum
- Std Deviation
- Variance
But in addition to those aggregations you can also perform group by operations. So if I was to create a view on the same list, but group it by customer name, I could get the Total Invoice value on a customer by customer basis.
Conclusion
I think this is the easiest way to calculate the sum of a SharePoint column. My other post on using the xpath method is also good, but not specific to SharePoint columns
