This article shows you how to automatically export inspection data from iAuditor to spreadsheets (columns/rows) or any Customer Relationship Management (CRM) system supported by the Power Automate connectors. This is a great way to populate data into an online database for supervisors or administrators, without users manually sending reports or exporting CSVs, which is prone to error.

Getting Started

This setup assumes an understanding of the JSON data model, as well as the iAuditor API. You also need to have your iAuditor Template ready to go, for information on building a template, please see our template creation guide.

Requirements

  1. iAuditor Premium subscription
  2. iAuditor API Token
  3. Microsoft Office 365 user account
  4. Access to the Premium version of Power Automate (previously Microsoft Flow)
  5. Knowledge of locating inspection item IDs

Steps for setup in Power Automate

This section outlines the steps for populating a Microsoft Excel spreadsheet with a template's inspection responses using Power Automate.

Step 1:

  1. Navigate to Power Automate and login to your Office 365 account
  2. Click "Create" on the left hand side and select "Scheduled flow"
  3. (optional) Name your Flow to help identify the automation
  4. Configure the following:
    Starting: The date and time you want your first flow to start - set this accordingly
    Repeat every: The interval you want the flow to run on - set this as "1 Hour"
  5. Click "Create" notice, the first step is already set
  6. Proceed with "+ New step"

Step 2:

  1. Search and select the “Get past time” action
  2. Configure it to match the “Recurrence” of “Interval” = 1 and “Frequency” = Hour
  3. Proceed with "+ New step"

Step 3:

  1. Search and select the "Search modified audits - iAuditor" action
  2. Set the "Modified after" field to use Past time from the available dynamic content
  3. Enter "Filter by Template" with the template_id you wish to export response data from.
    You can locate template_id by exporting an inspection to CSV and finding the relevant column
IMPORTANT NOTE

If this is your first time using iAuditor on Power Automate, you will be prompted to authenticate the connection.

Authentication:

  1. When the authentication prompt appears, enter “iAuditor” as your “Connection Name”
  2. Within the “API Key” field, type “Bearer” followed by a whitespace, then your API token
  3. Your connection setting should be as below. In the example shown our API token is dnz2xwy...

Step 4:

  1. Search and select the "Get a specific audit - iAuditor" action
  2. Click the Audit ID field and select "Audits Audit ID" from the Dynamic Content on the right
  3. This will automatically nest your action within an "Apply to each" loop
  4. Click the "Get a specific audit (Preview)" step and check that the system selected the "Audits Audit ID" from the previous step
  5. Now "Add an action" within the Apply to each loop

Step 5:

  1. Search and select the "Filter array - Data Operations" action
  2. (optional) Rename the step to the question label by clicking the 3 dots in top right corner of the action and selecting “Rename”
  3. Click on "From" to define the inspection body filter - "items"
  4. Select "Expression" and type in the following:
    body('Get_a_specific_audit')['items']

  5. Proceed with "OK"
  6. Click on the "Choose a value" field to configure the attribute filter -"item_id"
  7. Select "Expression" and type in the following:
    item()[‘item_id’]
  8. Proceed with "OK"
    IMPORTANT NOTE

    Please type in the expressions manually, as copying and pasting the expressions could return error message: The expression is invalid. In this case, retype in the expression manually.

  9. Now enter the "item_id" of the question you wish to export data for into the "Choose a value" field on the right
  10. Proceed with "Add an action" within the Apply to each loop

Step 6:

  1. Search and select the "Compose - Data Operations" action
  2. Click on "Inputs" to discover the corresponding response from inspections
  3. Select "Expression" and type in the expression depending on the type of response.
    For more information on response types - see this support article on Power Automate Flow Expressions
    In this example, the Flow exports response to a text answer question:
    body('Filter_array')?[0]?['responses']?['text']
    IMPORTANT NOTE

    Type in the expression accordingly and the body('Filter_array') part should be replaced by the name of your filter array step if renamed, and use underscore symbol as the space.
    For example, instead of typing "Integrations are the best" you would type "Integrations_are_the_best".

  4. Proceed with "OK"
  5. Then "Add an action" within the Apply to each loop

Step 7:

  1. Search and select the "Add a row into a table - Excel Online(Business/OneDrive)" action
  2. Configure the following:
    Location: Select the site location of where the spreadsheet is stored online
    Document Library: Select the directory where the spreadsheet is stored online
    File: Select the excel file for populating inspection data
    Table: Select the table in the spreadsheet for populating inspection data
    IMPORTANT NOTE

    Please see the steps for setup in Microsoft Excel if you require assistance preparing the spreadsheet for the flow.

  3. Column headers from your spreadsheet will appear as field options once the table is selected
  4. Click on the relevant field to select the "Outputs" from Step 6 as a Dynamic Content
  5. In this example to help identify the inspection, an Inspection ID column has been created to populate with the audit_id
  6. “Save” on the top right corner to turn on your Flow

Testing:

  1. Make sure the Flow is turned on, then conduct a new inspection using the chosen template
  2. Complete the inspection
  3. Select "Test" from the top right corner of the Flow edit page
  4. Choose "I'll perform the trigger action" and "Test"
  5. Confirm the test by clicking "Run flow"
  6. View the excel spreadsheet, you should see the data populated from the recent inspection

Steps for setup in Microsoft Excel

This section outlines the steps for preparing a Microsoft Excel spreadsheet, and populate inspection data from iAuditor via Power Automate. If you do not have an existing spreadsheet on Microsoft Excel Online with table headers, please follow the instructions below.

  1. Navigate to Microsoft Office and login to your Office 365 account
  2. Open Excel and create a "New blank workbook"
  3. Set your column headers as item/question labels
  4. Click and drag to highlight the column headers
  5. Now select "Format as Table" from the Home menu
  6. Check the "My table as headers" box and select "OK"
  7. The spreadsheet is now ready to use for the integration
Was this article helpful?

We love your feedback. Please tell us what you think.


Yes No
Care to share a bit more so we can continue to make improvements for you? Care to share a bit more so we can make this article even better for you? Thanks for your feedback!