Using Power Automate, Forms and Excel together

[nikaso]

I've dipped my toe into the Power Automate world and have several flows setup and working well but I wanted to do something a bit more advanced and have reached the limit of my capabilities!

 

What I want to do is have a spreadsheet with student name, form, year group, their email and their parent's email. I want to use an MS Form to allow teachers to select a child from dropdown boxes and then power automate to match this selected child against the spreadsheet, pull the student and parent email from the relevant row and send a template email (in this case a 'well done' postcard).

 

The bit I am stuck on is how to match the form response with the row and pull out the email? Anyone anything similar they have done?

[Rob_D]

I think you want the "list rows present in table" option. Then under advances use

student eq 'FormResponse'

Where "student" is column header in your table and "FormResponse" is the input student name added through the dynamic content option.

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

 

Then you should be able to pass the data read from the table into your "send email" option as dynamic content.

[nikaso]

I think you want the "list rows present in table" option. Then under advances use

student eq 'FormResponse'

Where "student" is column header in your table and "FormResponse" is the input student name added through the dynamic content option.

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

 

Then you should be able to pass the data read from the table into your "send email" option as dynamic content.

 

 

Thanks for this, I'm going to try and spend some time on it this afternoon and see if I can work it out with this info.

[nikaso]

I think you want the "list rows present in table" option. Then under advances use

student eq 'FormResponse'

Where "student" is column header in your table and "FormResponse" is the input student name added through the dynamic content option.

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

 

Then you should be able to pass the data read from the table into your "send email" option as dynamic content.

 

I've managed to get it working now with some straightforward test data which is great.

 

I'm trying to work out how best to present the student names for a teacher to select, I don't really want to do a dropdown list with 1200 plus names on it but I find if I break it into year groups and have a different response for each year group feeding into automate I can't have a neat

"student eq 'FormResponse1' OR student eq 'FormResponse2'" etc. in the ODATA query.

 

Maybe there is a way I can produce a single response from the form but break it down so teachers aren't having to scroll forever, another method I suppose might be some Condition steps with Yes/No branching i.e. is there a response in the Year 7 name field, if so proceed to email, if not proceed to check the Year 8 name field and so on?

[Rob_D]

You should be able to use or in the Odata.

If you have a year group question in your form, you could work around the problem by creating a "case" object with your year-group question as the value and case statements for each year-group and in each case statement, use "set variable" to put the student name (from the year-grouped name question ) into a variable (you'll need to use "initialize variable" at the start of the flow to create the variable). Then you can use the variable in Odata statement.

[nikaso]

You should be able to use or in the Odata.

If you have a year group question in your form, you could work around the problem by creating a "case" object with your year-group question as the value and case statements for each year-group and in each case statement, use "set variable" to put the student name (from the year-grouped name question ) into a variable (you'll need to use "initialize variable" at the start of the flow to create the variable). Then you can use the variable in Odata statement.

 

Thanks, this has worked a treat.

 

Now I just need to explain to SLT why they can't have animated GIFs in the email template they want to use!