Answered

Verify if Email Attachment (Excel) has the same content as Excel Sheet on SharePoint

(2) Share

Report

Posted on by Samwise23

Hello all,

I have a flow that is triggered when an email arrives, this periodic email contains an attachment of couple of rows only (most of the time only the headers of the column and one row with data), sometimes the email may be sent with the same data as the previous email, where in SharePoint location there's already a copy of that file from previous emails.

I want to create a condition that compares the newly received attachment with the existing one on SharePoint, if they have the same content then "do nothing", if the attachment contains a different content than the file in SharePoint, then the flow continues and the new attachment is created as a file in SharePoint replacing the old file. (I can't compare 1 row because the two files may have one common row but may have other different rows)

The following is the current flow, I need to put the abovementioned verification before the action 'Create File'

Is this feasible? If yes, I may need some details as I'm new to PA and may not find it easy to understand suggestions.

Many thanks in advance.

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (1)

Samwise23 58 on at

Like (0)

Report
Copy link

Link copied!

Anyone can help with this?

Was this reply helpful? Yes No
Verified answer

Samwise23 58 on at

Like (0)

Report
Copy link

Link copied!
Resolved with the following steps:

Initialized 3 variables after the new email arrives action:
First variable: TempSheet Locked > Name: DocumentLocked / Type: Boolean / Value: True
Second variable: Final file Locked > Name: finalFileLocked / Type: Boolean / Value: True
Third variable: Delay Seconds > Name: delaySeconds / Type: Integer/ Value: 1

After 'Get Attachment (V2)' inserted 'Do until' action (this is to resolve locked file issue, every time the file was still locked by the previous flow run as the email arrives every few minutes), within it:
- SharePoint 'Create file' action for a temporary file to be created. File Content is the 'Get Attachment (V2)' ContentBytes.
- 'Set Variable' - Name: select the variable DocumentLocked from the drop-down list;
Value: ``` if(and(equals(outputs('create_file_TempSheet')?['body']?['status'], 400),contains(outputs('Create_file_TempSheet')?['body']?['message'], 'locked')), true, false) ```
In the Settings of the 'Set Variable' > 'Run after' > under "Create file TempSheet" checked the boxes "Is successful" and "Has failed"
- Another 'Set variable' called "Add additional minute" - Name: Name: select the variable DelaySeconds from the drop-down list; Value: 60
- A 'Delay' action where field Count contains expression: ``` if(variables('DocumentLocked'), variables('delaySeconds'),5) ``` and Unit field: Second

After 'Do until' is closed, inserted 'Create table' from the TempSheet file, where File field refers to the 'Create file TempSheet' within the previous 'Do until', followed by 'List Rows present in TempSheet'

Inserted 'Apply to each' in TempSheet; and within it, a 'List rows present' in the old final sheet.

Inserted another 'Apply to each' in Old Final Sheet followed by a 'Condition' where we compare the value of the rows using a column that contains unique value (in this case a column called 'Number')
``` items('Apply_to_each_in_TempSheet')?['Number'] ``` is not equal to ``` items('Apply_to_each_in_old_Final_Sheet')?['Number'] ```

Under the 'True' path in within the 'Condition', inserted 'Do until' (this is to resolve locked file issue, every time the file was still locked by the previous flow run as the email arrives every few minutes) with the following within it:

- SharePoint 'Create file' action for the final file to be created. File Content is the 'Get Attachment (V2)' ContentBytes.
- 'Set Variable' - Name: select the variable finalFileLocked from the drop-down list;
Value: ``` if(and(equals(outputs('Create_final_file')?['body']?['status'], 400), contains(outputs('Create_final_file')?['body']?['message'], 'locked')), true, false) ```
In the Settings of the 'Set Variable' > 'Run after' > under "Create final file" checked the boxes "Is successful" and "Has failed"
- Another 'Set variable' called "Add additional minute" - Name: Name: select the variable DelaySeconds from the drop-down list; Value: 60
- A 'Delay' action where field Count contains expression: ``` if(variables('finalFileLocked'), variables('delaySeconds'), 5) ``` and Unit field: Second

After 'Do until' is closed, inserted 'Create table' from the final file, where File field refers to the 'Create final file' within the last 'Do until', table range value: ```=OFFSET('Page 1'!A1,0,0,SUBTOTAL(103,'Page 1'!$A:$A),9)``` (I have 9 columns in the table).

'List rows present in a table' action followed the above action, and then followed by further actions that are irrelevant to this question.

Was this reply helpful? Yes No