About the IntelligenceBank Data Manager Add-in for Microsoft Excel Online

What does the IntelligenceBank Data Manager Excel add-in do?

The add-in allow users to login to an IntelligenceBank platform and then retrieve Resources metadata, including filters and custom upload fields, as rows in Microsoft Excel. Updates can then be done on the spreadsheet, and pushed back to the platform.

Some key features:

• Create a search query (i.e. retrieve Resources only in specific folders, by file types, etc.). To use this, you will need to be familiar with API search queries.

• Update all Resource Metadata, including Custom Fields.

• Restrict user selections if the field is a multi or single select field.

• Validate mandatory fields locally.

• Validate incorrect entries for key scenarios locally. This includes date formats, character limits or select field/filter values. Invalid cells will be highlighted in red.

• Individual warnings, the process won't stop if a single error occurs.

• Detail error reporting. 

How to use the add-in

After opening the add-in:

• Enter the platform credentials, noting that an assist@ or SSO user are NOT supported.
• Once authenticated, you will see Resources listed as an object. Click to open that section.

• Under Resources, 3 actions are available:

  • Retrieve Resources

    • This is to retrieve the Resources to update from the platform. You can apply filtering queries by updating the Body section, which follows API guidelines as per https://apidoc.intelligencebank.com.

  • Local Data Validator

    • If you have made a lot of changes to the Resource rows and want to Update the changes in IntelligenceBank, it is advised to use the Local Data Validator tool before pushing the changes. The Local Data Validator will quickly identify most potential errors in the metadata provided, such as typos in filter values, or exceeding a character limit. Getting these errors identified and addressed locally will save you a lot of time when running the actual Updates.

  • Update Resources

    • This will push the changes to IntelligenceBank. Make sure your data is correct as any change made cannot be reverted.

Retrieve Resources

To retrieve the resources and place them into rows select Retrieve Resources. If you require all resources, simply click GO.

If you only need specific resources then you can use the Search Parameters box. This is exactly the same as using the search in the API.

An example would be only needing folders from a specific folder structure. Input the parent folder UUID in between the parenthesis of the folder query and make sure “isSearching” is set to true to include sub-folders content. Note that you can separate multiple root folders by a comma. e.g.

"ib_folder_s": "e13a848d30af8699c2c1763424c4349f,53f190474a009b68aa7efba94b2d3763"
	"isSearching": true,

You can click TEST to make sure the query you have constructed works.

When your query is ready, click GO and the resources will populate. This may take some time if there are many resources. Ensure your computer does not go to sleep.

The LOG button can be used to reveal the logs, which are useful for troubleshooting.

For each Resource row, the UUID of each resource is populated in column A, then the folder path, and then the IntelligenceBank Resource Info Fields.

Then depending on individual clients, the Filters and Custom Upload fields are populated.

Updating Resources in the Worksheet

Once the Resources are populated in the rows, you can begin to update them. In most fields, you’ll be able to directly enter text or update the date.

For Select Fields and Filters, the connector will make the cells a drop-down list if Single select and the user may then select from there or from the options selection list which will appear on the right. For multiple select fields, only the options selection list on the right will appear, though you can also input the values manually, separated by a comma.

If you need to add new Select Field and Filter values, these need to be added first in the platform. You will however need to retrieve the Resources again in order to see them as selection options in the add-in.

Local Data Validator

The Local Data Validator will run a broad series of verification checks to make sure your data has no errors (e.g. mandatory fields are inputted, filter values exist as per the platform, dates are in the correct format, etc).

Cells with errors will be highlighted in Red, and details of the error will be visible in the Validator Response column towards the far right of your screen. A summary will indicate the total number of errors.

Update Resources

When ready to update the Resources back to IntelligenceBank, click GO in the Update Resources tab of the connector.

The Resources will get updated in IntelligenceBank. Make sure your computer doesn’t go to sleep for the duration of the upload.

You can track progress on the add-in.

Any errors will be displayed against the row in the Update Error column.

If the update is successful, the Update Response column will display OK.

Important Usage Notes

• Only the rows that have been modified after retrieval will be processed.

• Copying and pasting into the cells does work, however, if you copy identical cells in, it will think it’s a new input and will include it in the Update. For instance, if you exported the Retrieval and gave it to a client, and they only changed a few cells, and you then copied the entire sheet back into the connector. It’ll think all cells have been changed and therefore update all resources. This shouldn’t really be an issue but it’ll just take a longer time to update.

• Do not add additional cell options, do not overwrite header column names and do not delete columns.

• Never Edit the UUID. It is used to map the updated row with the corresponding Resource in IB.

• Folder Paths are for information only. Updating them will NOT move the Resource to a different location, or creating new Folders.

• To re-run additional updates to rows that were previously updated successfully, make sure to clear up the Update Response column from the value “OK”.