How to use Dynamic Data Mapping feature with Microsoft 365 Excel

With Pro Plus and Enterprise plan, you can dynamically map data from Microsoft 365 Excel on your OneDrive to each of your screens. 

For example: You have a chain of shops, where promotions are mostly the same, but discounts vary for certain locations.

Note: this requires a paid account of Microsoft 365, a free account does not support this feature.
Alternatively, you can use the data mapping feature with Google Sheets free version, learn how to here.

What does it do:

You can use our Designer to turn the price into a Dynamic Data Mapping, then map it to a Microsoft Excel Sheets on your OneDrive, where you set the name of the screens you want the prices to be different (in this case it's the Heights) and what's the discount value you want it to be on that screen. That's it.

Let's jump in and get started:

We will follow the example described above, to create a Shop Promotion, and map data so we can set discount values for individual locations. We will also change the promotional image to be different by location.

First log in to your account https://app.optisigns.com/

Go to Files/Assets → Apps → Designer

And start creating a menu

Use Templates and select the Shopping menu (of course you can create your menu or any other design topic). Give your design a name and save it:

Then click on an element. In this case, we select the toothbrush promo price.

Then Click Data Source → Make Data Mapping.

Give this data element a name. This is optional, but it's a good idea to do so, so you will know what it is when you have multiple data elements to map later.

Repeat the same process for other objects. Yes, you can change images as well.

Next, click "Generate Data Source", this will generate a Microsoft Excel Online and pre-populate data for you. 

Sign in to your Microsoft account where you want to save the Excel Sheets.

Important Note: Please allow “popup” on your browser. You will be able to log in and open the folder/file picker.

Select the Folder where you want the Excel Sheets to be created and click Select.

Your Excel Sheets are created. Click Close.

Notice that the Data Source is now attached to your Design.

You can click the ... to drop-down menu. Then click Open in Microsoft Excel Online

This will open the newly created Excel Sheets in a new tab.

Notice the data have been generated for you. Important Note: Data must be within a table in the Excel Sheets.

Explanation of data column:


  • Screen Name: Name of the screens that you have in your account.
    • The app will identify the screens by name and adjust the value based on what you enter in this Microsoft Excel Sheets
    • If you change your screen name, you will have to come back and update this spreadsheet later.
    • It's a good practice to name each of your screens uniquely and have some convention for location, and content to be displayed on the screens, which will help you to identify and manage them more easily later.
    • ***ALL*** is a special value that tells OptiSigns that if a screen is not specifically assigned values, it will take value from this row of data.
  • Asset Element ID: this is a unique value auto-generated by the system when you turn an element to Dynamic Data in the process above. This is how the app knows where the data is to map
  • Asset Element Name: name that you entered in the process above, this is for description, easy to read-only, it does not affect data mapping
  • Value: the value you want the screen to display. It's either text or URL to an image.

You can create the Microsoft Excel Sheets yourself by copying and pasting the data elements ID, but the Generate Data Source method made this much easier.

Modify the data to your need, in this case, we add data for Heights Location, and all other locations use default value ***ALL***:

Now all the screens with "Shop Menu with Dynamic Data" will have a Discount Value of 40%, and the Heights Location will have a Discount Value of 50%.

This example has demonstrated how you can use OptiSigns' Designer to map Dynamic Data to Microsoft Excel 365 Sheets. The example is about mapping promotion prices, but you can apply the same concept in many ways: Retail, Hospitality Promotion, Employee Birthday, recognition, etc.

Some frequently asked questions about Dynamic Data Mapping:

1) Can I map data from Application XYZ?

Yes, you can use Zapier to automate data from your application to update the Microsoft Excel Sheets generated by OptiSigns. Zapier has thousands of integrations to many apps such as POS, HRIS, Employee engagement, sales, customer support, project management, etc.

If your application is not supported by Zapier, you can build your integration to Microsoft Excel Sheets API, it's popular and supported by Microsoft Excel.

For Employee Birthday use case, we have a dedicated article with the best practice here.

2) How quickly the screens will be updated from the time data is changed in Microsoft Excel Sheets?

Microsoft Excel does not have a published SLA on how fast webhooks will be called. Usually, when the changes are small (affect a few rows) it takes a few minutes, but sometimes, it can take longer. Especially if you are making a lot of changes to the Microsoft Excel Sheets, it can take a while for Microsoft Excel to send updates.

3) Does it require my device to be connected all the time? What if the device lost internet connection?

An Internet connection is required to push the changes through, but if the device does not have internet, it will just keep playing old data.

4) What's the easiest way to get an image URL?

You can upload your image to Files/Assets just like any other files and copy the URL to use in your mapping.

That's all! Congratulation!

You have learned how to dynamically update your screen with data from Microsoft Excel Sheets.

Now you have another tool to manage complex, and location-based data.

 

If you have any additional questions, concerns, or any feedback about OptiSigns, feel free to reach out to our support team at support@optisigns.com