Flow to calculate value from Forms input and send it by mail (using excel/sharepoint)

(0) Share

Report

Posted on by XI-19080853-0

Hi all,

first time posting, hope you can help me out.

I'm building a flow from a MS Forms that would calculate an estimated value from the inputs given in the form and return it by mail to the requestor.

The calculations are a bit complex, with conditionals, lookups... not simple operations (otherwise i could try directly in sharepoint).

I had the idea to conect the form to excel for the calculations and send a mail with the value when the file is modified, or update a sharepoint from excel with the calculated values, that would trigger the mail notification.

What i have done so far:

1- Created a Form online connected to an excel sheet (in onedrive for business)

2- For every new answer in forms a new row is created in excel, a set of formulas calculates then the final value on the last column of each row. Another sheet of the excel contains a matrix with the information for the calculations wich is working properly.

The sheet that gets the rows from forms and has the formulas is formatted as a Table.

Up to this point it's working properly.

Where i get problems is on the next step:

OPTIONS:

3a- Make a flow to send a mail directly with the excel values. Problem is it sends a mail for each row of the excel table instead of only the last one, so it will spam the requestor with a lot of mails. excel 1.jpg

3b- Make a flow to create new items in sharepoint while the row is also sent to excel (calculation will be done in excel). After that, update the sharepoint with the values from excel. Then send mail notification when sharepoint is updated. On this one I am unable to relate the row in sharepoint with the one in excel to update only the new one. excel 1.jpg excel 2.jpg excel 3.jpg

Can anyone help me find a solution for either point 3a or 3b, any of them would solve the problem.

Attached some screenshots, probably i'm doing something wrong as i'm not an expert but i can't find the solution.

Thanks!

Categories:

Building flows

I have the same question (0)

All responses (14)

Answers (1)

LeeHarris 1,026 on at

Like (0)

Report

Hi @Xavier_I

Try adding a new column to your Excel table called "ISLAST" and populate it with the following formula (where Table1 is the name of your table).

=IF(ROW()-ROWS(Table1)=1,"TRUE","FALSE")

You should then be able to remove the step in your flow that lists all table rows and just use the Get a Row action. Change your KEYCOLUMN to 'ISLAST' and the KEYVALUE to 'TRUE' in order to locate the last row each time. This should also remove some of the Apply to Each loops you currently have as they will no longer be necessary.

Was this reply helpful? Yes No
v-zhos-msft on at

Like (0)

Report

Hi Xavier_I,

Accroding to your discription, you want to get the last Column value together.

Then send them in one email,right?

I‘m afraid Flow can't get an entire column right now.

You can send the table via the create csv Table action.

Best Regards,

Zhongys

Was this reply helpful? Yes No
XI-19080853-0 17 on at

Like (0)

Report

Hi LeeHarris,
thanks a lot for your help.
I did some testing with your suggestion:
The formula works nice in excel but when the flow runs i get an error that no value "true" was found on the file.
If i look at the excel, ony the last row contains "true" on that column.

I tried to add more rows but still no result, then i manually modified the excel file and added a "True" value in the column created for this on the second row out of 10.
The flow worked and sent the mail with the correct cell value for row 2.

It's like the flow would not recognize the last row added to the excel table, any ideas why this might happen?
Is this because of the timing?

Was this reply helpful? Yes No
XI-19080853-0 17 on at

Like (0)

Report

Hi v-zhos-msft,
thanks for your comment, maybe my first description was a bit complex.
I don't need the entire column, what i need is one cell from one column from the last row that was modified.

Was this reply helpful? Yes No
LeeHarris 1,026 on at

Like (0)

Report

Hi @Xavier_I

Are you updating and reading the Excel in the same Flow? I have had timing issues in the past where the calculations/update of the spreadsheet don't happen quick enough and don't get picked up by the process. You could try adding in a Delay action. This would help you confirm whether or not it is due to timing.

Was this reply helpful? Yes No
XI-19080853-0 17 on at

Like (0)

Report

Hi,
the excel file is upgraded from Forms directly (created a MSform from excel online and it connected them automatically).
What should happen is:
1- New form entry--> new row in excel (calculating all values with formulas) -- Working OK
2- Flow triggers when excel file is modified--> get the value of one cell from the last row -- -Not working
3- Send that value by mail

I cleaned the flow to make it simpler, see screenshot.

I changed the value on the formula you provided from "TRUE"-->"Good_value"

The flow provides the following error:
{
"status": 404,
"message": "No row was found with Id 'Good_value'.\r\nclientRequestId: b3cec8db-15e9-48d4-a290-2af5d8868b9f",
"source": "excelonline-wus.azconn-wus.p.azurewebsites.net"
}

When i manually change this value it works (writing manually the text "Good_value" in the proper column).
Delay did not make a difference, i tried with up to 1 minute, also if i open the excel it gets updated in like 2-3 seconds from the submission of the form.

I thought formulas were supported for Excel (business) connectors, can this be the problem?
or the formatting of the excel cells? (i'm a bit tied there as the table autoformats the new rows)
Any help or ideas will be appreciated.

Was this reply helpful? Yes No
Verified answer

LeeHarris 1,026 on at

Like (2)

Report

Hi @Xavier_I

My guess is that the issue here is with the submission of the values from MS Forms into Excel. Perhaps this is not triggering the formula calculations correctly.

I have previously set up a similar process, but rather than link the Form direct to Excel I used the new Form submission trigger in Flow. I inserted the new Excel row using an action step in the Flow, added a delay to allow it to Save, then retrieved the row back out from Excel with the Get Row action, at which point the calculations had occured. Is this something you are able to test in your environment?

Was this reply helpful? Yes No
XI-19080853-0 17 on at

Like (0)

Report

Hi Leeharris,
you're 100% right.
I reworked the flow capturing the values from forms with FLOW instead of creating the form from excel online and everything worked.
(Also i could clean the excel file of rows created by that "link")

Thanks a lot for you support!!

Was this reply helpful? Yes No
LeeHarris 1,026 on at

Like (0)

Report

Hi @Xavier_I

No problem, glad you got it working.

As you are now editing and reading the excel in the same Flow I would suggest changing the degree of parallelism in the trigger settings so that there can only be one running instance of the Flow at any time. This prevents issues occuring if you have multiple Form submissions within a short period of time. The Flow will run to completion before the next instance is triggered.

Was this reply helpful? Yes No
XI-19080853-0 17 on at

Like (0)

Report

That's a really nice advice!
Changed the settings already, probably the form will not get to that point as service will not be used that much but you never know...

Was this reply helpful? Yes No