Power Automate: Export inspection data to Excel Online spreadsheets

Sign in to Power Automate.

Click Create from the menu on the left-hand side and select Scheduled cloud flow.

In the pop-up window, configure the following fields:

• Flow name: Add a name for your flow so you can refer back to it easily.

• Starting: Set the date and time you want the flow to start running.

• Repeat every: Set the frequency to 1 Hour.

Click Create to create the flow and its first step.

Click  below "Recurrence" and select Add an action.

Search and select Get past time under the "Date Time" connector.

Set the "Interval" to 1 and leave the "Time unit" as Hour.

Click  below "Get past time" and select Add an action.

Search and select Search modified inspections under the "SafetyCulture (iAuditor)" connector.

If this is your first time using SafetyCulture on Power Automate, authenticate the connector by configuring the following fields, and then clicking Create New.

• Connection Name: We recommend that you name the connection by the account username or email from which the API token is generated.

• API token: Enter your API token in the format of "Bearer <token>". Make sure there is a space between "Bearer" and the API token.

In the side panel, click Show all for advanced parameters and then configure the following fields:

• Modified After: Click the field, then click and select Past time from the list.

• Filter by template: As inspection data differs between templates, you must filter by a specific template for this flow. Click the field to select the template from the list, or click Enter custom value and enter the template's unique identifier.

• Archived (optional): Click the field and select true if you only want to process archived inspections, or select both for active and archived inspections.

• Completed (optional): Click the field and select false if you only want to process incomplete inspections, or select both for complete and incomplete inspections.

• Ownership (optional): Click the field and select me if you only want to process inspections that are owned by your account, or select other for inspections that are owned by other users in your organization.

• Limit: Keep in mind that each flow run can only process up to 1,000 inspections. If your organization conducts more than 1,000 inspections every hour, you should narrow the frequency in "1. Recurrence" and "2. Get past time".

Click  below "Search modified inspections" and select Add an action.

Search and select Get a specific inspection under the "SafetyCulture (iAuditor)" connector.

In the side panel, click the "Audit ID" field, then click and select Inspection Audit ID from the list.

This should add the action within a "For each" action.

Click below "Get a specific inspection" but within "For each" and select Add an action.

Search and select Filter array under the "Data Operations" connector.

In the side panel, configure the following fields:

• From: Click the field, then click and enter the following expression. If the question response you want to export is on the title page, replace ['items'] with ['header_items'].

  body('Get_a_specific_inspection')['items']

• Filter Query (left): Click the field, then click and enter the following expression:

  item()['item_id']

• Filter Query (center): Leave it as "is equal to".

• Filter Query (right): Click the field and enter the unique identifier (item_id) of the question.

You should see something that resembles the following example. We recommend that you rename "Filter array" on the upper-right of the side panel to something you can refer back to as the question label, such as "Jobsite Hazard Check".

Click below "Filter array" but within "For each" and select Add an action.

Search and select Compose under the "Data Operations" connector.

In the side panel, click the field, then click and enter your expression based on the corresponding question's response type. You can copy and paste the samples we have for each response type. For example, if the response type is text answer, enter the following expression: body('Filter_array')?[0]?['responses']?['text']

Please note that the 'Filter_array' in the sample refers to the label of the action in section "5. Filter array". If you followed our recommendation and renamed the action, you should match the expression with the new name. For example, if the filter array action was renamed to "Jobsite Hazard Check", the following expression should be entered with underscores (_) substituting spaces:

body('Jobsite_Hazard_Check')?[0]?['responses']?['text']

You should see something that resembles the following example. We recommend that you rename "Compose" on the upper-right of the side panel to something you can refer back to as the corresponding question's response, such as "Jobsite Hazard Check Response".

Repeat sections "5. Filter array" and "6. Compose" in combination, for each inspection question data you want to export.

Click below "Compose" but within "For each" and select Add an action.

Search and select Add a row into a table under the "Excel Online (Business)" or "Excel Online (OneDrive)" connector.

If this is your first time using Excel Online on Power Automate, authenticate the connector by signing in to your Excel Online account.

In the side panel, configure the following fields:

• Location: Click the field and select the spreadsheet's location.

• Document Library: Click the field and select the spreadsheet's directory.

• File: Click the folder icon on the right-hand side, then find and select the spreadsheet file.

• Table: Click the field and select the table.

• Column Name: Click Show all for advanced parameters and the columns you prepared as a table in the Excel Online workbook will appear as fields to populate. Click each field, then click and select each "Compose" action's "Outputs" from the list.

Click Save on the upper-right of the page to save the flow.

Click Test on the upper-right of the page to begin testing the flow.

Select Manually and click Test.

Click Run flow.

Click Done to monitor the test flow.