How to use Dynamic Data Mapping feature with Google Sheets

With OptiSigns' Pro Plus and Enterprise plans, map dynamic data from Google Sheets to individual screens. Perfect for businesses with varying prices across locations, ensuring synchronized and up-to-date information effortlessly.

 

Important Note (5/28/24): We've released a new data mapping feature called OptiSync, which you can view in the support article here. 

Note: You can also use the data mapping feature with Microsoft Excel 365 learn how to here.

You can use our Designer to turn the price into a Dynamic Data Mapping, then map it to Google Sheets, where you set the name of the screens you want the prices to be different (in this case it's the City Center Location) and what's the price you want it to be on that screen. That's it.

If you want to know how to use the data mapping feature with Microsoft Excel Sheets, you can click here.

Read more below for how to step by step.

Let's jump in and get started:

We will follow the example described above, to create a sushi menu, and map data so we can set prices 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 Sushi menu (of course you can create your own 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 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 elements to map later.

Repeat the same process to make the 2nd promo price and the sushi image dynamic data.
Yes, you can change images as well.

Next, click "Generate Data Source", this will generate a Google Sheets and pre-populate data for you. 

Sign in to your Google account where you want to save the Google Sheets

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

Your Google Sheets is created. Click Close.

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

You can click the ... to drop-down menu. Click Open in Google Sheets

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

Notice the data have been generated for you.

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 Google 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 easier 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.

Important: Please note that updates to your data mapping spreadsheet will be reflected on live screens, but the values in the designer tool itself are placeholders and will only update upon manual refresh.

You can create the Google 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 the City Center Location, and all other locations use default value ***ALL***:

Now all the screens with "Sushi Menu with Dynamic Data" will have a Promo price of $10.99, and the Citi Center Location will have the price of $11.99

This example has demonstrated how you can use OptiSigns' Designer to map Dynamic Data to Google 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 Google Sheets generated by OptiSigns. Zapier has 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 Google Sheets API, it's popular and supported by Google.

2) How quickly the screens will be updated from the time data changes in Google Sheets?

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

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

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 Google 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