I have an excel list of employees who have given input via microsoft forms.

I want to check automatically whether those people are still present in the organization. If so, then the data may remain. If not, the data must be removed from the excel sheet because it is used in a canvas app.

Also, if possible, I'd like the response to be removed from microsoft forms.

As far as we know there is no Excel list of persons present in the organization. However, all persons are loaded into, for example, Outlook or MS Teams.

What would be a good way to automate the comparison and then update the Excel file?

CodePudding user response：

Form SharePoint side, you go to Microsoft 365 admin center -> Users -> Active users -> Export users to get csv list of present persons in the organization.

CodePudding user response：

You could retrieve the responders via the FormAPI. You can retrieve the enabled accounts via

2.Use a Select action to get a clean array with only the responder mail addresses

a. Use the expression below in the From Field

outputs('Send_an_HTTP_request_to_SharePoint')?['body']['value']

b. Switch Map field to text mode (with icon on the right side) and add the expression below

item()['responder']

3.Use a Send an HTTP request to SharePoint action with a GET request to the forms.office.com

4.Use a Select action to get a clean array with only the users which have an enabled account in the tenant

a. Use the expression below in the From Field

outputs('Send_an_HTTP_request')?['body']['value']

b. Switch Map field to text mode (with icon on the right side) and add the expression below

item()['mail']

5.Compare the two arrays in a Filter Array

a. Use the Select - Forms action Output in the From Field

b. Use the following expression (in advanced mode) in the Criteria field

@not(contains(body('Select_-_AccountEnabled_Users'), item()))