How to use Dynamic Data Mapping feature with Google Sheets

Follow

With Pro Plus and Enterprise plan, you can dynamically map data from Google Sheets to your each of your screens. 

For example: you have sushi restaurant chain, menu are pretty much the same, but prices are different for some locations.

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 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 describe above, to create a sushi menu, map data so we can set prices 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 Sushi 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 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. The 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 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 Google 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 City Center Location, and all other location use default value ***ALL***:

 

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

 

This example have 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 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 Google Sheets API, it's popular and supported by Google.

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

Google 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 less than 1 min, 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 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 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 or just submit a ticket here.