Digital Signage: Manage Content Using a Google Sheet

Overview

Before You Begin

A delimited file must be created and placed somewhere accessible by the sign auto-login account (dsw-autologin-<unit>, where <unit> is the specified unit prefix). This can be any type of delimited file, including Excel, tab-delimited or CSV. It can also be a published Google spreadsheet.

If the sign is to have dynamic images, a folder repository must be created to hold these images. This folder must be accessible to the sign auto-login account (dsw-autologin-<unit>, where <unit> is the specified unit prefix).

Guided Simulation

Simulation: Add a Google Sheet Live Data Content Item

Step-by-Step

The same information from the simulation above is explained in a step-by-step format.

Create and Populate the Spreadsheet

  1. Decide what type of format you want to use for the Live Data source. In this example, a published Google sheet will be used.
  2. Give each column a header containing information about what you wish to display on the sign. Some examples of possible headers include:
    • Title
    • Description
    • Start Date
    • End Date
    • Location
    • Time
    • Image
  3. google sheet image
  4. For each item you wish to display, populate the spreadsheet with information.

    Important

    • Do not skip lines or leave blank lines
    • Do not put any data outside of the pre-existing columns

  5. google sheet image
  6. If you choose to have an image on your sign, be certain the image name in the spreadsheet matches the file name exactly.
  7. Copy the key from your Google Sheet and save it for later use.

    Note The key is the portion of the URL after "/d/".

    network folder
  8. The Google Sheet must have the following settings:
    • Sharing: Anyone with the link can view
    • Publishing: Published

    Note Click File > Publish to publish the Google Sheet.

Create a Template and Region

  1. Log in to Content Manager.
  2. Create a template with the same resolution as your digital sign (1920x1200 is typical).
    • Add one region.
    • Place the region at 0,0
    • Resize the region to match the size of the template.
    • Save the template.

Add Live Data Content to Your Sign

  1. Right-click on the top content folder and select New Content in this Category > Live Data content-folder
  2. Enter the spreadsheet properties
    • Name
    • Description optional
    • Suggested recurrence
    • Duration

      Note This is how long the content will play if you have more than 1 piece of content in in the region on your sign

  3. Click the dynamic URL button.
  4. Enter the Path using the following format: https://docs.google.com/spreadsheets/d/<key>/export?format=csv&id=<key>&gid=<sheet number>

    Note You will need to overwrite the two <key> portions of the Path with the key from your Google Sheet URL. Overwrite <sheet number> with 0.

  5. Select Separated text... from the Data format drop-down list.
  6. Click OK.
  7. excel properties
  8. Move the new content type to the region on your template.

Design the Layout

  1. Right-click the content object and select Edit Source Content Layout.
  2. excel properties
  3. In the layout, define layout dimensions to match the region. No title or footer will be needed. Make the row height and width match the region dimensions.
  4. excel properties
  5. Pull in the various fields from the data fields above to the layout below.
  6. excel properties

    For images

    • Drag down a markup text object to the layout.
    • Delete the words markup text in the markup text field.
    • Enter the path of the image folder created in the preparatory steps above. The path should be in UNC form: \\server\folder(s)\
    • Append the name of the image column to the end and enclose it in curly brackets – example:\\server\folder\{image}
    • Press Enter to confirm the text.
    • Right click on the image region and choose formatting.
    • Select This field contains a path or URL…
    • Select Fill container while maintaining aspect ratio from the drop-down list

    excel properties
  7. Pull the start date and end date fields to the filter tab on the right.
    • Choose Date/Time from the drop-down list and select the appropriate options.
    • Start date should be on or before Now
    • End date should be on or after Now
  8. excel properties

Example

example layout Layout
example display Display
Last Updated: 
Wednesday, February 15, 2017