5 Ways to Use the Sheets App
March 12, 2020
1. Single Source
The smooth operation of your business may hinge on certain data being 100% accurate – like a restaurant menu, a price sheet, or Key Performance Indicators. When that information is displayed on your digital signs, it’s critical that it’s current and reliable. Across the board changes on your signage network must be easy to execute and hard to get wrong.
This is generally solved by guaranteeing that all references to critical data refer back to a ‘single source’. When the ‘truth’ changes at the source, all references to that data point change simultaneously.
Spreadsheets are excellent tools for managing this sort of data. With the Sheets app, you can link the data on your signage to a single source kept centrally in a spreadsheet. When the spreadsheet values change, all of your signs change automatically.
Not only that, but the confidence level of the average user with spreadsheets is much higher than with almost any other business software. Your team’s ability to confidently update data in a spreadsheet will always be at its highest.
Finally, the single source approach also has the advantage of source control – if you strictly control who has Edit access to the spreadsheet, you can control your data independently of the design and publishing decisions made with your ScreenScape media. You can track changes, see version history, and even revert to previous values where necessary. For example, all of the Marketing department could edit the design of your price list, but only management could edit the spreadsheet source, and Google will track and list all update events.
You operate a tap room that serves craft beer. You have a dozen taps, with frequent swaps as new beers arrive and kegs run empty. Your digital signs have main menu boards which list all current beers. They also rotate through separate slides highlighting each beer one at a time. You might even have slides that highlight the latest beers, top sellers, or kegs that are about to run out.
To avoid editing a dozen slides every time there’s a change, you could manage your menu data from a single sheet. Then you’d connect all your ScreenScape media back to this ‘single source of truth’. As brews are added or removed from your list, as supply changes, or as prices update the edits to this ‘single source’ are reflected across the board.
For an in-depth look at how to use the Sheets App to power your digital sign, check out this video tutorial.
2. Data Transformation
Spreadsheets are the original killer app for data transformation. Edit one cell, and your change magically ripples out to dozens of related cells. This single feature has literally revolutionized the management of entire industries.
The data you’re presenting on your digital sign may need frequent updates. And there may be important variables that modify the final result of the ‘same’ data at different locations (e.g. a base price modified by local taxes, or the same message across multiple languages). You may also want to aggregate, filter, sort, conditionally format, or summarize your data with sophisticated formulas before displaying them (e.g. sort a daily sales leaderboards by region and celebrate the top 3 rows on your signage).
If that’s the case, integrating your sign with a spreadsheet is an excellent method of transforming data before it’s displayed across your screen network. It saves you the effort of copying the same data across many slides, it saves you the mental labor of repeatedly identifying what goes where, and it dramatically decreases the odds of copy&paste errors. Set it up once and forget it.
You regularly broadcast timely messages across your network. Sometimes it’s a weather event, or a holiday message, or a workplace safety update. Your organization is committed to diversity, so you want to broadcast in all the native languages spoken by your audience. (English<->French, Spanish<->English, English<->Chinese, etc).
One often overlooked example of data transformation in Sheets is Translation. Google Sheets offers a powerful built-in translator with the =GoogleTranslate() function. Using the Sheets Translate function you could type in your message in your native language, and have it translated in the sheet to as many target languages as you needed. You’d then combine that with the ScreenScape Sheets app to link your sign media to the translated cells. The result would be that your signage automatically outputs a translation of your initial message to whatever languages you’ve set up in your Sheet.
Original Text: “Due to the severe weather alert issued for the region, all classes have been cancelled today.”
Translated Text: “En raison de l’alerte des phénomènes météorologiques violents émis pour la région, toutes les classes ont été annulées aujourd’hui.”
Your ‘Closed Deals’ are posted regularly to a spreadsheet in Finance. You want to celebrate the biggest deals and top sellers in a live Sales Leaderboard. But the way Finance presents the data doesn’t match the simple 1, 2, 3 “top sellers” you want to present—it’s just a big list of deals. You want a simple, sorted list.
Follow these steps:
- Create a new sheets doc called ‘Sales Leaderboard’.
- Use the built-in ‘ImportRange()’ function to import the current values from the Finance sheet
- Use any other built-in formulas to aggregate, filter, sort, conditionally format, or summarize the sales data, into a simple range of 3 rows with the current top sellers
- Create a ‘Sales Leaderboard’ media item in ScreenScape and link the ‘Sales Leaderboard’ sheet, with the Sheets app.
As new Finance data comes in, your Google formulas take care of transforming the data in the simple structure you need. ScreenScape takes care of securely relaying that information to your digital signs.
3. Data Imports
If you’re trying to get outside data onto your screens, this is often the fastest and most economical way to avoid cumbersome custom development.
You may be in a situation where you already have the data you need, but it’s stuck in a data file on your server. Maybe it’s in an RSS, JSON, or some other XML file. How do you extract the important details to your digital sign? If you can Import that file into a Google Sheet, you can use its built-in functions to parse the file data.
It’s now a simple process to link the extracted cell data to your digital sign.
You operate a chain of 40 medical clinics. Your average patient waiting times are published to your website. The wait times on your site come from a JSON file that’s published online.
A fast and easy way of getting the same wait times published to your digital signs would be to use a Sheet as an intermediary. Use a =ImportJSON() function in your sheet to pull the WaitTimes and LocationName from the file.
Then use the ScreenScape Sheets app to display the wait times from those cells on your screens.
Quick, easy and free, with no disruption to your current business practice.
One of the best things about Google Sheets is its rich ecosystem. While ScreenScape’s list of dedicated integrations is constantly growing, it will only be a fraction of the thousands of services and apps that can be linked to a Sheet.
The Sheets Add-on community has created hundreds of integration tools to serve the needs of various niche markets. In addition, services like Zapier and IFTTT have built their businesses around bridging the gap between a wide array of apps to core services like Sheets.
So in the event that you aren’t able to link your app of choice directly to ScreenScape, odds are much, much higher that you could link it to a Sheet. Then you can link your Sheet to ScreenScape.
For example, your marketing department is looking for a way to get your daily top sales data from your ecommerce site onto your digital sign. Your site’s store is hosted in WordPress and powered by the WooCommerce plugin.
Does ScreenScape have a WooCommerce app? No… BUT. Using Zapier, you can move your latest WC sales into a Sheet, auto-sort by deal size, and link the resulting cells with a ‘Today’s Top 5 deals’ slide on your ScreenScape display using the Sheets app.
As noted previously, using the Sheets app means that your Sheets docs “remain private and secure – no need to ‘Publish to the web’ or ‘make public’ beforehand. As such the app is well suited to internal organizational data, such as price lists, KPIs or financial data, where company guidelines may prohibit general public release.”
While many third party apps provide a ‘Publish to Web’ option, taking this route can be risky. It generates a public URL, which exposes your data to anyone with that address. There is no privacy and no real security when you take this route. In some cases that’s just fine – it’s just a tweet, it was already public, right? In many other cases, it’s most definitely not fine – you’re a publicly traded company and you can’t expose data on your company’s internal sales targets on the open Internet.
For a detailed look at how we secure the information you publish to your ScreenScape sign, refer here. Suffice it to say, we work very hard to keep your data secure, and we have state-of-the-art methods for doing so. But it’s all for naught if the source of that data is public.
This is where authenticated apps like Sheets enter the picture. The initial step in using the Sheets app is to grant ScreenScape an authentication token to securely access your spreadsheets on Google. The scope of the permission is ‘read only’, and it’s restricted to your spreadsheets only (scope = ”spreadsheets.readonly”). This allows ScreenScape to securely access your data at the minimum level required. We can then take that data and wrap it in the standard security protocols that we use for all ScreenScape data.
How does media security work? Here’s a side note for those who want to know more:
Every access request to our CDN network is prefaced by a security checkpoint. Passage through these checkpoints is required not only for all media requests, but for every last resource used by that media: each image, video, font and iframe is locked down. Only authorized actors are permitted access to these resources.
Requests by the object owner, and those designated by the object owner via our sharing features, are the only requests able to access media. This applies to all access requests, by both in the browser by our users and in the field by our player devices. Each access request is accompanied by a unique security token; failure to present an authenticated token results in a denied request.
Our permission framework mediates the competing concerns of security and accessibility with a methodology that combines speed, high availability, scalability and best of all, user transparency.
What does this mean for you? It means that if you can enter or integrate your data (securely!) into your Google Docs spreadsheet, it will stay private all the way to your ScreenScape digital signs. (For more on Integration, see the previous section in this article, “#4 Integration”). You can still have the automation and ‘dynamic’ aspects to your data practice (the data from your ‘System X’ shows up automatically on your screens) without sacrificing security.