When attempting to update a table in an existing Excel spreadsheet stored on SharePoint, I am able to successfully access the dynamic location of the file, create a table with headers. This being a dynamically placed document, meaning it's path will never be the same but filename will be, I am attempting to update the existing table range with one row of data using Update a Row. So far, my attempts have failed with the status 415; Unsupported media type. Has anyone else encountered and successfully done this? Do I need to convert the value into a different content-type before attempting to update the range?
Below is the Flow result:
Just for troubleshooting purposes, I would replace temporarily the dynamic content from your 'Update a row' item entry and use a dummy static entry value and reexecute the flow. THis way you can figure out of the error came from assigning dynamic content to "File" input or to "item" input.
Please also note maybe there is a mismatch format between your item column and the dynamic content you are trying to assign to it. What is stored inside "Legal entry: M...", I mean which is its format? Integer, array, object, string...
You can easily inspect it by adding a dummy Compose action block before your Update a row, assign this same dynamic content as its input, then reexecute the flow
Hi @Alan_Sanchez ,
I am afraid that this requirement may not be achieved currently.
When we configure Excel connector related action, it is best to pick exist files and tables.
According to your descriptions, you want to dynamic configure Update a row action.
When you configure this action, the table does not yet exist. The table is created only after the above action is executed, so you cannot get the fields and configure them.
Please check the link and get known-issues-and-limitations about this connector:
I created a separate flow copying this one to test static paths and files and can confirm the update works using static methods. This included a compose to confirm the correct value is being pulled from the SharePoint list and passed to Update Row. I am also able to confirm the dynamic file and table is being accessed correctly.
Allow me to give more background on what it is I'm trying to do and that may help with this one.
I will be storing customer intake data in a SharePoint list. In certain scenarios, I need to send back a prepared template XLSX containing two tables: table 1) contains basic customer info taken from the Sharepoint list; call it a 'header' table, and table 2) headers only to be filled out by the customer.
My existing flow creates the Sharepoint list item, copies atemplate XLSX to a newly created customer folder stored on SharePoint. From here, it locates and accesses the newly copied filed before inserting basic customer info in the 'header' table, sourced from the Sharepoint list item created in an earlier step. The final step will be to take the prepared customer template and email it back to the customer.
Shifting gears a bit from the original 415 status code....
Due to this being a dynamic spreadsheet that could potentially be updated at the same time by two submissions, I am choosing to first copy the template to the customer file before updating it. In doing this, the filepath and table are both dynamically filled into the Update Row and/or Add Row to Table steps. In either scenario, however, I keep getting stuck at actually passing the data to be saved in the table.
I suspect the best approach to do this will be via an HTTP request via Sharepoint since the existing connector does not appear to support what I am trying to do. Unfortunately I new to using HTTP requests and are not yet sure how to get that to work. As far as I've come to understand from online reading and videos, I need to use Patch, point to the file in the URI, then send the data in the body. Would you agree with this approach and be able to offer some guidance on how this could be accomplished?
Thanks for that link, I've been looking for documentation that explains what each field was looking for; I was stumped on Item.
Provide the item properties.
Provide the item properties.
So the Excel connector may be limited, but it sounds like an HTTP Request to SharePoint should be capable of such a dynamic edit. Recall from my previous post that despite the file patch changing each time, the workbook contents are identical every time. Therefore I would think if the table name and key ID are provided to the API, it should be able to make the change.
OMG I am truly sorry, I just thought 'File' was the name of an excel column
Let me look again into this
There are two posts from John Liu and Peter Veenstra that might help
Please note Extra tip #2, whch I believe is part of what you are looking for. It's a pity John did not explain it in detail
Hope this helps
I realise this was a while ago, but I didn't spot a solution online, so am adding this here.
This is definitely possible - I spent quite a while trying to get it to work, but it does work.
The problem is not with the dynamic file selection (file id attribute from SharePoint works fine).
The unsupported media type error is solved by sending a JSON record as the item properties for the update a row action.
So add a compose action before the update a row action, with the column name(s) and value(s) you want to update (excluded columns will be ignored):
"Column2": "<dynamic value here>"
"Column3": "<dynamic value here>"
Then use the output of the compose action as the value for "Provide the item properties"
Works for me.
Check out new user group experience and if you are a leader please create your group
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!
Test your skills now with the Cloud Skill Challenge.