How to use Dynamic Data Mapping feature with Microsoft 365 Excel

Follow

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

For example: you have a shop chain, promotions are pretty much the same, but discounts are different for some locations.

Note: this requires a paid account of Microsoft 365, free account does not support this feature.
You can also 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 describe above, to create a Shop Promotion, and map data so we can set discount values for individual locations. We will also can change promotional image to be different by locations.

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 own menu or any other design topic). Give you design a name and save it:

Then Click on an element. In this case we select the sushi 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 element to map later.

Repeat the same process to 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 able to log in and open the folder/file picker.

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

Your Excel Sheets is 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 Excel Sheets

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 screen uniquely and have some convention for location, content to be displayed on the screens, that will help you to identify and manage them easier later.
  • ***ALL*** is a special value that tell OptiSigns that if a screen is not specifically assigned values, it will take value from this row of data.
• Asset Element ID: this is unique value auto-generated by the system when you turned an element to Dynamic Data in the process above. This is how the app know 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 copy and paste the data elements ID, but 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 have thousands of integrations to many apps POS, HRIS, Employee engagement, sales, customer support, project management, etc.

If your application is not supported by Zapier, you can build your own 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 best practice here.

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

Microsoft Excel does not have published SLA on how fast it webhooks will be called. Usually, when the changes is small (affect a few rows) it's 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 be connected all the time? What if the device lost internet?

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 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 or just submit a ticket here.