Set up Microsoft Excel to import data from WorkZone

WorkZone uses the Microsoft Excel Power Query to manage data transfer from WorkZone to Excel and this means that when exporting data from WorkZone to Microsoft Excel, either exporting the results of a query from WorkZone QueryBuilder or exporting a list, chat or notes from WorkZone Client, each user must first set up the Power Query connection in Microsoft Excel as well as define the privacy levels of the connection and the current spreadsheet.

Define privacy levels

To set up the Excel connection, the user must define the privacy level for the current spreadsheet as well as for the actual Power Query connection used to integrate Excel with WorkZone in the Privacy Level form.

  • Private: Your spreadsheet and connection will not be able to communicate with each other or other spreadsheets.
  • Organization: Your spreadsheet and connection will only be able to communicate with other spreadsheets with the Organization privacy level.
  • Public: Your spreadsheet and connection can communicate with all other spreadsheets.

Once a user has set up the connection, only the privacy level for the current spreadsheet must be specified the next (and every) time WorkZone data is exported to Excel. The connection privacy levels as well as authentication settings are saved for the user in Excel.

To disable future prompts for privacy levels, select the Always ignore Privacy Level settings radio button in the Query Options form > Privacy tab.
The Query Options form is opened in Excel > Data > Get & Transform Data group > Get Data > Query Options.

Define authentication procedure and OData-feed settings

After privacy levels have been defined for the data, the user must define how to authenticate access to the data source.

  1. In the Access Web content form, select Windows and thenUse my current credentials as the authentication procedure
  2. Click Connect to create the connection and open the the OData-feed form
  3. In the OData-feed form, select Windows and thenUse my current credentials as the authentication procedure
  4. Click Connect to create the connection and import WorkZone data.

Updating the spreadsheet

To update refresh an Excel spreadsheet with the latest data from WorkZone , in Excel, click Data > Queries and Connections group > Update all.

Viewing data sources

In Excel, click Data > Queries and Connections to open the Queries and Connections panel and see the data sources used for the export:

  • OdataMetadataAndType: The spreadsheet's own data source - more specifically the data in the hidden Connection (Do Not Delete) worksheet .
  • Source: The actual results of the query.
  • Schema: The Odata metadata specifications, for example the localized labels and data types of the columns (fields).
  • WorkZone OData: The output that is displayed in the spreadsheet once the export has completed. You can right-click the WorkZone OData data source and select Edit to edit and manipulate the displayed columns.

When exporting query results from WorkZone QueryBuilder, the query itself as well as other general query information (for example the language settings used in Querybuilder and the type name used) is stored in the spreadsheet in a hidden worksheet called Connection (Do Not Delete). You can display the worksheet to view the query information.

Deleting or editing the connection settings

Users can edit or delete the connection privacy levels as well as authentication settings in the Data source settings form which is opened in Excel > Data > Get & Transform Data group > Get Data > Data source settings. Note that the WorkZone data sources are Global permissions.