Problems with rows updating

[George1234a]

Good afternoon!

I have an excel file with daily support incidents, which I receive everyday by my colleague in Outlook, after that I save it in SharePoint and update main excel file with support incidents registry. I want to automate these processes:

1. Saving excel file from Outlook to SharePoint - Done it already;

2. Creating a table in saved file - Done it already;

3. Updating rows in main excel file - Can't do it!

When I try to add this flow, it doesn't show me "future" columns, because file isn't created yet. Tell me, anyone, please, how to that flow? Or maybe you have another way to do it?

Thank you!

[ScottShearer]

@George1234a 

While I don't totally understand the workflow you have described, it appears as though you can simply save what doesn't yet exist and retrieve the rows again that you want to use.

[George1234a]

Yes, but I want to automate these processes, my colleague send me an excel file, it saves automatically on SharePoint folder, after that it creates a table into that saved excel file (because into that file are only columns, but they aren't as a table column), finally that file needs to add new rows in my incidents registery. Is there any other way to automate these processes behind I wrote above?

[ScottShearer]

@George1234a 

Data in Excel must be in a table for Power Automate to retrieve the data - no work arounds.  If I understand you correctly, the issue is that the data is in the Excel file is not in a table.  There is an Excel for Business Create Table action.

The issue with the action is that you must know the cell range in order to create the table.  Do you know the cell range?

[George1234a]

No, there is a data in the table, I know cell range in the file, but in the action "Update a row" it doesn't show me columns name, because the file isn't created in the SharePoint yet (because I don't receive it yet), after I receive it saves in SharePoint->Creating a table in saved file->Updating rows in main excel file (registery)

[George1234a]

[George1234a]

My flow looks like this:

2. Action "Update a Row" looks like this, but there is no column in the left, because file doesn't exist now (not created in SharePoint):

3. But If I write not a dynamic file (I created for example), it shows me columns in the left:

So where can I take column names if the file isn't created but I know what name they will have?

[majorfriend]

Hmm, not sure I am understanding correctly.
Wouldn't the rows you want to update be in the main excel file (and wouldn't this file already exist and is not created by your flow)?
Or is your flow creating the main excel file?

[George1234a]

Main file is already exists and placed in common folder with the file I receive everyday, everything I want - rows in the main file must be updated by received (everyday) file, they have same columns

[neerajsu]

@George1234a : The problem is not because your file isn't created. Its because, you are giving id of the file created in previous steps. This is a generated value, not a static value. Flow has no clue what the File id value can be, because it can be anything and it will only know when the flow runs. So it cannot retrieve the Table headers from the excel sheet.

Now this does not mean you cannot update the row. You still can.

1. Click "Show advanced options"
2. Notice the field "Provide the item properties"
3. Add the following values in there {"ColumnHeader1" : "Value1", "ColumnHeader2" : "Value2", "ColumnHeader3", "Value3".......}
4. Since I don't know Cyrillic I just used ColumnHeader1 2 3 etc, but I hope you get the idea.

Hope that helped.

[majorfriend]

So for your update a row step you should select the file in the common folder, instead of using the Id from the create file step (this would update rows on the new file, not the main file). However, if I'm misunderstanding and you need to update rows on the created file then @neerajsu's solution should do the trick.

[SILVIA-T]

Hi @neerajsu . I'm trying your solution for a similar issue.

I've got to very similar flows for two sharepoint list. For one flow I can see the "Provide the item properties" field and it works fine, but for the other It shows only "body" ando not "item properties", and I can't set it right

I don't know why in this "update a row" action I get a different option than for the other flow, any ideas?

I've tried the same approach in "Body" but I've got an invalid template error Unable to process template language expressions in action

Thanks!

[majorfriend]

Hi Silivia,

It looks like you are using a dynamic file for the file ID.

I would try selecting a file with the table you want to update. This should allow the columns to show and you can enter the update information. After you have done this change the file back to your dynamic ID, enter your custom table value, and test.

[SILVIA-T]

Hi @majorfriend 

The table I want to update is created within the flow (I have an excel template that I populate with some sharepoint item fields after an approval is done; then I copy this file to a folder also created within the flow and I change it's name. So I know in the flow the name of the new file, the folder, the table name and the key but the file isn't really created yet). I need to update these some rows in the new file created because we have several items and sometimes it mixes values because some approbals are done at the same time (and delays are not solving the issue either).

I've tried your approach, with the existent file and then changing it to the dynamic id, but as soon as I changed it the table value is changed and i get the body field againt (and the same error):

I have another very similar flow and when I use the action get rows with a dynamic ID I have the "provide the item properties" field:

But I don't know why in this flow it shows "body" instead of "provide the item properties":

Thanks!!

[majorfriend]

If you leave the File field with the dynamic content, but erase the GUID found in the Table field and enter your table name as a custom value then test your flow again do you still get an error?

[SILVIA-T]

Hi,

Yes, if I change it back It says the table doesn't exists:

"body": {

        "status": 404,

        "message": "No table was found with the name 'Expediente'.\r\nclientRequestId: 5b8d0c04-e0c5-43fb-a320-4746bf6da588\r\nserviceRequestId: a76138db-5125-4cf9-82b4-2ad3d31bf85b",

        "error": {

            "message": "No table was found with the name 'Expediente'."

        },

        "source": "excelonline-we.azconn-we.p.azurewebsites.net"

    }

[SILVIA-T]

Hi @majorfriend 

I've found a work around for this. Afer updating the rows I wanted to convert the file to pdf, and I've found that the only way to do that is in onedrive, not sharepoint.

So since I was copying already my file to onedrive, I've tried to update the rows in onedrive, not in sharepoint, and that works!!

This is the flow, in case it helps anyone:

Then I copy the pdf back to sharepoint, because is the final file I really need to send by email.

[akshaymane_71]

Hello @Neerajuris 

I am also facing the same issue & stumbled upon your post. I tried to query the table by dynamically but failed to load the column names. As you suggested do it by defining using Json in item properties. but it still not loading.

Please help if anyone known this issue

[akshaymane_71]

Hi @SILVIA-T  ,

I followed your tread & just want to ask how do you get table name ? is it custom value or automatically read from excel/ file 

please reply it would be helpful.