Condition Error Regarding Length Output

[Kavita_6032]

Hi,

When I was creating a automate flow for adding and updating sharepoint list from excel data with the help of below reference -

https://www.c-sharpcorner.com/blogs/addupdate-excel-data-into-sharepoint-list-using-power-automate

But I was getting the below error :-

Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'The template function 'lengthoutputs' is not defined or not valid.'.

Can someone provide insights into this issue?

[SamLed]

Hi @Kavita_6032,

According to the error, I guess you missed expression syntax:

length(outputs('Get_items')?['body/value'])

Please share you action in design mode.

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[v-yetonggu-msft]

Hi @Kavita_6032 ，

I found that the link reference information you gave is a bit wrong.

There must be a primary foreign key between the excel table and the SharePoint list to connect the two data sources.

Only when the primary and foreign keys are equal can the items in the SharePoint list be updated, thus ensuring that each item in the list has a unique corresponding value.

I assume that the ID column in the excel table is the primary key, then the ID in the SharePoint list serves as the foreign key.

Please ensure that the primary key and the foreign key correspond one to one.

My Excel Table:

My SharePoint List:

I did a test for your reference.

ID eq '@{items('Apply_to_each')?['ID']}'

When length(outputs('Get_items')?['body/value']) is greater than 0, it proves that the item already exists in the SharePoint list. Just update the item in SharePoint.

If it is not greater than 0, it proves that the item does not exist in the SharePoint list, create a new item.

length(outputs('Get_items')?['body/value'])

Best Regards,

Sunshine Gu

[Kavita_6032]

Thank you for the solutions.

I attempted to incorporate the length(outputs('Get_items')?['body/value']) into the process, but it seems to be causing entries to be duplicated.

Regarding the ID in the SharePoint list, is it necessary to create a new one, or can I use the default ID column?

Let me explain my scenario:

I have an Excel sheet with no unique columns, but I've added an ID column with values 1, 2, 3, and so on. The Excel sheet has 1268 rows. On the other hand, I have a SharePoint list with 1268 rows as well, and it also doesn't have any additional ID column. I'm referring to the default ID column of the SharePoint list.

Now, I want a flow where if any data in the Excel sheet is updated, it should update the corresponding entry in SharePoint. If there's a new entry in the Excel sheet, it should be added to SharePoint. Could you please help me with the steps I should take and suggest a flow based on this scenario or any other reference you think would be helpful?

I would greatly appreciate your time and assistance with this issue.

[v-yetonggu-msft · Accepted solution]

Hi @Kavita_6032 ，

Please use the default ID column of the SharePoint list.

Currently, the Power Automate platform does not have triggers for modifying rows or creating new rows for an excel file. I suggest you use planned flow to create a flow, check the excel file once a day, update rows and create new rows.

My Flow:

The default return value of List rows present in a table is 256 rows. Considering that the current return value in your excel is 1286 rows, we set the threshold of list rows present in a table to 5000.

Please set the return threshold of the Get items control to 5000.

Note that 'Apply to each 2' is in the yes branch and is nested outside the ’Create item‘, not outside the ’condition‘.

length(outputs('Get_items')?['body/value'])

Best Regards,

Sunshine Gu

[SamLed]

Hi @Kavita_6032,

@v-yetonggu-msft's flow is exactly what you need, I would just use another trigger to monitor file change:

with following trigger condition (available in trigger settings) to ensure flow will be triggered when only this file has been modified:

Expression: 

@equals(triggerOutputs()?['body/{FilenameWithExtension}'], 'Projects.xlsx')

This expression check if modified file name equals 'Projects.xlsx' change according to your need.

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[Kavita_6032]

Thank you for your solution.

It worked perfectly in one of my Demo List.

But somehow when I tried to my original list it is throwing the below error.

Is it happening because I have some of the Columns of SharePoint list removed for view and excel sheet have all the columns?

[v-yetonggu-msft]

Hi @Kavita_6032 ,

Please expand 'Apply to each2' and post a screenshot of the error message in detail.

Could you please post a screenshot of your 'Apply to each2'  configuration?

Best Regards,

Sunshine Gu

[Kavita_6032]

Below is the Apply to each 2 actions.

Let me show my full flow :-

 please help me with this?

[v-yetonggu-msft]

Hi @Kavita_6032 ,

Please expand the 'Update item' control and let me see the configuration inside.

Note the sources of these dynamic content.

Best Regards,

Sunshine Gu

[Kavita_6032]

Thank you for the Solution.

The flow is working fine but whenever it is getting any empty cell in the excel it is showing error? how to handle that can someone please help me?