Dynamically Updating Excel Rows

Like (0) Share

Report

Posted on 22 Dec 2017 21:28:55 by WiggityZwiggity

356

I am ultimately trying to read content from an excel table (after it has been created which is the trigger), create a SharePoint Item from that row (storing the file locator and row ID in each sharepoint item to retrieve it later) then write back the ID of the created sharepoint item to the excel row.

To do this I used a template of an excel file so I could use the designer to fill in the correct columns to the the sharepoint item fields. Then I changed the file path of the excel file get rows action to dynamically pull from a file when it is created (again the trigger).

The problem I am running into is in trying to use the powerappsid (rowID) from the table in any fashion (i.e. if I write the rowid to the SharePoint item then then the table will not have a powerappsid column created, but if I don't use the rowid field in the sharepoint item the powerappsid column is created).

When I try to write back to the excel row what the SP ID is within a foreach loop (from the get rows action values) I get a 400 value must be provided.)How I initially Get RowsFor Each Loop Using Output form Get Rows to Create SP Items then Update Initial Rows form the Get Rows ActionError Received When Trying to Update Row Using the Row ID previously specifiedUse of Output from Get Rows in Creation of SP ITemTrying to Update from Output of Initial Get Rows

All responses (11)

Answers (0)

manuelstgomes 6,625 on 22 Jan 2020 at 11:29:44

Like (2)

Report

Re: Dynamically Updating Excel Rows

Hi @Eickhel, @WiggityZwiggity, @Anonymous, @GabrielStJohn, and @Eric_Hu

I created a template with a workaround for this issue. You can find the explanation and the template here:

https://manueltgomes.com/microsoft/flow/how-to-access-dynamic-path-excel/

Hope it helps you

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel
Eickhel 374 Most Valuable Professional on 13 Jun 2018 at 09:32:12

Like (0)

Report

Re: Dynamically Updating Excel Rows

Today I came acros this article that might help you with those intermitten issues:

https://veenstra.me.uk/2018/06/11/microsoft-flow-advanced-error-handling-recover-from-failure/

Hope it helps
Community Power Pla... on 07 Jun 2018 at 23:45:13

Like (0)

Report

Re: Dynamically Updating Excel Rows

This workaround doesn't reliably work for me. I delete all the records in the table and then repopulate with new values. It works intermittently - it either fails when deleting the record or when it's inserting. It also takes about10 minutes to delete 700 records and then another 10-15 minutes to insert records. This is with parallelism in the For-Each loop cranked up all the way to 50.
GabrielStJohn on 07 Jun 2018 at 22:00:17

Like (0)

Report

Re: Dynamically Updating Excel Rows

Hello, @WiggityZwiggity!

Have you had a chance to apply @Eickhel‘s recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thank you for being an active member of the Flow Community!

-Gabriel

Flow Community Manager
Eickhel 374 Most Valuable Professional on 04 Jun 2018 at 16:55:55

Like (2)

Report

Re: Dynamically Updating Excel Rows

Today we had the same issue, the only workaround we could find was to copy the file to an specific location in OneDrive and then use that file in that specific location as a template to process it. This way, Flow could get the file's metadata and then show the required fields in the update row action:

Hope this helps
Community Power Pla... on 25 Apr 2018 at 12:32:53

Like (0)

Report

Re: Dynamically Updating Excel Rows

also a big issue for me..
I need to select Excel Files on Sharepoint dynamically and read out the rows that have been modified in order to copy that data to another excel file on my sharepoint site..
Eric_Hu 12 on 07 Mar 2018 at 01:53:53

Like (0)

Report

Re: Dynamically Updating Excel Rows

I have voted, too.
I hope the feature can be work.
I have over 100 systems and I need to create 100 flow which the same.
It is big problem when I change the flow.
WiggityZwiggity 356 on 28 Dec 2017 at 15:26:53

Like (0)

Report

Re: Dynamically Updating Excel Rows

Thanks for looking @v-yamao-msft Voted.
v-yamao-msft on 28 Dec 2017 at 08:33:25

Like (2)

Report

Re: Dynamically Updating Excel Rows

Hi @WiggityZwiggity,

Thanks for the screenshots.

I made a test on my side. When using a dynamic content for the File Name field, you could enter the Table name manually, but for the Row Id field, it will return an error “Cannot evaluate the parameter 'dataset' at design-time; it is empty or has a runtime expression”.

I am afraid that dynamic file name is currently not supported for this action.

There is a similar request on Flow Ideas Forum, please vote and comment the idea at here:
https://powerusers.microsoft.com/t5/Flow-Ideas/Cannot-use-EXCEL-file-ID-on-action-Insert-Row/idi-p/9701

Best regards,
Mabel Mao
WiggityZwiggity 356 on 27 Dec 2017 at 14:56:12

Like (0)

Report

Re: Dynamically Updating Excel Rows

Thanks @v-yamao-msft that is what I am wondering too. Attached is a screenshot of full workflow context (had to creak it into 2)Part 1Part II