Introducing the Google Spreadsheet Component for Digital Signage!

  • 3
  • Article
  • Updated 2 years ago
  • (Edited)
The Google Sheet Component is an experimental Web Component from Rise Vision that enables web designers with the ability retrieve data from a published Google Spreadsheet and to style the data using JavaScript and CSS. The source files for this walkthrough can be found in the rise-google-sheet-demo repository here.

Step 1: Install the Component
Install the Component You should first navigate to the folder where you want to install the Component. In this example we will install it in a folder named rise-google-sheet-demo. You can install the component and the necessary dependencies through Bower using the following command:
bower install https://github.com/Rise-Vision/rise-google-sheet.git
After running this command you should see that a folder named src has been downloaded. Inside this folder is the _bower_components folder which contains all of the files we will need to set up our page.

Step 2: Setting up the Google Sheet
Before we start to build our page we need a Google Spreadsheet. To do this, simply create a new Google Spreadsheet or use an existing, then Publish it by selecting File > Publish to the web.

Step 3: Setting up the HTML
Once you have set up the spreadsheet it is now time to set up the HTML. For this demo we will set up our index.html file in the root of our demo folder. You should include webcomponents-lite.min.js before any code that touches the DOM. Then load the web component using HTML imports.  The head section of the HTML should look like this:

<head>
 <script src="src/_bower_components/webcomponentsjs/webcomponents-lite.min.js"></script>
 <link rel="import" href="src/_bower_components/rise-google-sheet/rise-google-sheet.html">
</head>

You can now add the Component to the body of your HTML. This demo shows how the Component can be used to retrieve a range of data from a Google Spreadsheet. Attributes for the Component are defined between <rise-google-sheet> tag. For this demo we just need to set two attributes, key and refresh.

The key attribute is the unique identifier for the Google Spreadsheet and is the string of characters between /d/ and /edit# as shown in the image below.

spreadsheet-keypng

The refresh attribute determines how often the component will check the spreadsheet for changes. For this demo we can set it at ‘30’ which represents 30 seconds.

The code within the <script> tags initializes the Component and tells it to log the content of the spreadsheet cells to the console, if the cells are populated. The body of your HTML should look like this. 

<body>
<rise-google-sheet
   key="<your spreadsheet key>"
   refresh="30">
</rise-google-sheet>
<!-- Initialize the Component -->
<script>
   var sheet = document.querySelector('rise-google-sheet');
   // Respond to events it fires.
   sheet.addEventListener('rise-google-sheet-response', function(e) {
        if (e.detail && e.detail.cells) {
         console.log(e.detail.cells); // Array of cell objects
         }
     });
// Executes a request.
sheet.go();
</script>
</body>


Step 4: Check that the data is being returned
Once the basic HTML is in place we should check that everything is working. In order to view the page, we will need to have it running on a local server. If you are using a Mac you can set up a local server by entering the following command:
python -m SimpleHTTPServer
This will set up a local server at localhost:8000. If you have set up your index.html file in the root of your directory you can view it by going to localhost:8000/index.html.

The page should still be blank, but don’t panic! If you open up the developer tools and select ‘console’ you should see the following output:
[Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object]
You should see an object being returned for each cell that was populated in the spreadsheet. In the example sheet there was 15 cells with text in them, so the component has returned 15 objects. Awesome, it works!

If you leave the console open, you should see these objects being logged every 30 seconds, which shows the Component’s refresh attribute works too!

Step 5: Setting up the HTML Table
With the Component set up and returning the contents of the spreadsheet successfully, you are free to use or display this data in any way you want. For this demo we will grab this data and display it in an HTML table.

We can add the structure of the table to the HTML within the <rise-google-sheet> tags. We will give our table the id of sheetTable and add in the classes pure-table and pure-table-bordered. We will come back to these classes shortly.

The table structure should look like this:
<table id="sheetTable" class="pure-table pure-table-bordered">
<thead>
<tr>
<!-- dynamically add column header titles -->
</tr>
</thead>
<tbody>
<!-- dynamically populate table cells -->
</tbody>
</table>

The column titles and cells will be dynamically populated by the JavaScript in the next step.

Step 6: Setting up the JavaScript and displaying the table
In this step we will set up the JavaScript that will take the data collected by the Component and display it in the table.

Create a new file in the root of your folder. In our example we have named it demo.js. You should include a link to this file within your HTML just after the closing </rise-google-sheet> and before the script that initializes the Component, like this:
<script type="text/javascript" src="demo.js"></script>
Please see the demo.js file in the repository and copy and paste the code from there. All of the code is annotated so you can see the role of each function in relation to taking the data collected by the Component and displaying it on the page.

You will also need to add two lines of code between the <script> tags in your HTML file. These should go after the sheet.go(); command to initialize the function in the demo.js file . The two lines are:

// new instance of demoSheet object
var demo = new demoSheet();
// initialize the content of the demo table
demo.init();

Now when you refresh the page you should see the data you entered in the spreadsheet appearing in a rough table form like this:


Congratulations, you used the Component to fetch the data and you used JavaScript to display that data in your HTML!

We can’t wait to see what you come up with!
Photo of Alan Clayton

Alan Clayton, Official Rep

  • 8,788 Points 5k badge 2x thumb

Posted 3 years ago

  • 3
Photo of Nick Mortensen

Nick Mortensen

  • 470 Points 250 badge 2x thumb
You should go back through this and ensure that the file names referred to are the file names that are in the git repository -- as well as the files having the same content as suggested in the tutorial.  I did this as a walk along and there was really quite a bit of flotsam and jetsam.  I'm sure it makes sense to you as you are familiar with the code and the structure of your folders, but I found it a bit confusing.  Nothing I couldn't plow through, but stuff that definitely would've turned me back a year or two ago.
Photo of Nick Mortensen

Nick Mortensen

  • 470 Points 250 badge 2x thumb
example: there is a demo.js file referred to, but you cannot find a demo.js file in the repository.  There is a demo-playlist.js file and I took that to be what you meant.  Correct me if I am wrong on this.

also, I can't see where any demoSheet() object is created, yet the tutorial asks to create a new instance of demoSheet() and assign it the name 'demo'.   I searched the whole repository using sublimetext and I cannot find the term 'demoSheet()' in any of the nearly 70 files.

I'm not trying to nitpick here, but there is an accuracy responsibility inherent in creating documents.  It reflects on the platform when the accuracy isn't there.  

Please correct me if I am off base in this case.  It just looks to me like the repository has undergone some changes since documentation and the changes aren't reflected at present.  
(Edited)
Photo of Alan Clayton

Alan Clayton, Official Rep

  • 8,788 Points 5k badge 2x thumb
Nick, thanks for the feedback! I'm going to have one of my colleagues take a look and get back to you. Look for a response shortly. 

Thanks!
Photo of Peter Cameron

Peter Cameron, Employee

  • 1,418 Points 1k badge 2x thumb
Hi Nick,

Thank you very much for your feedback. I think that what looks to have happened is that the repository which accompanies this walkthrough (https://github.com/Rise-Vision/rise-google-sheet-demo) does not appear as a link in this version of the documentation. This repository has some simplified demo files, including demo.js, to which the walkthrough refers. It looks like you were working through the steps while referring to the main component repository, and I am very sorry for that!

As you will have seen, the demo in the main component repository contains a lot of extra files, dependencies and even other components, which are not required to use the spreadsheet component by itself. We thought that it would be overly complicated for someone with less experience than yourself to have to work through all these files, and so created a trimmed down demo repository with just the key files needed to get the component up and running. Unfortunately however, the link to the demo repository was missed from the above documentation and we will correct this asap.

Thank you very much again for taking the time to contact us about this, and thank you for trying out the web components. 

Thanks!

Peter
Photo of Wilson Center

Wilson Center

  • 634 Points 500 badge 2x thumb
So, this feature is definitely beyond my expertise, but we really need this functionality, so I am going to press forward.

Where exactly does this web component get installed?  To the media players?  A local device?  A web server?

Thanks!
Photo of Peter Cameron

Peter Cameron, Employee

  • 1,418 Points 1k badge 2x thumb
Hi, thanks for your comment!

A new version of the google sheet component has recently been released so we are in the process of updating the documentation. You can find the new component and a walkthrough of how to set it up here: https://github.com/Rise-Vision/rise-google-sheet

In terms of where a web component is installed, it is designed to work with a custom HTML page that you would use to display your presentation. You would upload the HTML, CSS and the web component files to your Rise storage, or other storage location,  and then add the link to the HTML file to your presentation.

Can I ask what the functionality is that you are looking for? We also have a google sheet widget that is free from the Rise store which you can add to your presentations to display data from a google sheet. This is a lot easier to set up and can be configured through the widget itself.  If that isn't a good fit for your project, or you are looking to do something more advanced and interactive, feel free to contact the Creative team at Rise Vision here and they will be happy to talk to you about what you need: https://www.risevision.com/creative/

Thanks!
Photo of Wilson Center

Wilson Center

  • 634 Points 500 badge 2x thumb
Thanks Peter, very helpful. We are in the process of developing signage for a Cafe we operate at our facility. As best I can tell the standard Google Sheets Widget displays a simple spreadsheet. We are looking to use the data from a spreadsheet in a design, but not the standard linear display of a spreadsheet. I'd love to hear I am wrong about the widget!
Photo of Peter Cameron

Peter Cameron, Employee

  • 1,418 Points 1k badge 2x thumb
No problem at all! The widget can be customized to break away from the standard linear display - for example you can target different columns to give them unique text sizes and colours.

If you have a more complex layout in terms of the position of elements you can try using multiple instances of the spreadsheet widget and set the range to only target specific cells, that way you could have all the prices in one place and the descriptions in another.

If you have anything that remains constant or wouldn't need changed as much you can try adding in text widgets for things like titles etc. and these can be positioned independently to again break out of the column format, e.g. centred above two columns.
Photo of Wilson Center

Wilson Center

  • 634 Points 500 badge 2x thumb
Thanks so much for this Peter, this is great news.  I just want to confirm that it will work for our purposes.

We are currently in the process of re-purposing your "Elementary Day of the Week" template for use with our internal Cafe.  As I understand it, this template uses overlapping text boxes each with unique schedules to accomplish the dynamic content.  It is a smart way of handling things.  Unfortunately for us, our Cafe staff are not likely to learn Rise Vision well enough to not cause huge amounts of trouble.  They are already using a Good Sheet document to manage the weekly menu.

We would like to be able to use Google Sheet data to populate the main text block (one for breakfast, once for lunch) each day, maybe even to manage the graphic elements, and the text box at the bottom.

Do you think this is possible using the widget?  Are there any good resources that might help me get started or a good template that shows how this works?

Thanks!
Photo of Peter Cameron

Peter Cameron, Employee

  • 1,418 Points 1k badge 2x thumb
You're welcome. There are a couple of free templates that make use of the spreadsheet and text widgets to show menus that you can find in the store and there are some useful tips on using the new spreadsheet widget in this community thread: https://community.risevision.com/rise_vision_inc/topics/early-preview-of-google-spreadsheet-widget-a...

In terms of what you are trying to achieve, the spreadsheet widget sounds like it will be a good fit, however for the graphic elements and text boxes I would go with the image and text widgets respectively. As you noticed, other displays are put together with multiple text and images widgets that are timed to change as needed. You can achieve this effect within the timing schedule of the widget itself, or if you want to change the entire presentation (e.g. one for breakfast, one for lunch, one for dinner) you can schedule 3 different presentations to appear at certain times of the day.

If you do run in to any issues feel free to check out the community as this is a great source of information, tips and tricks when it comes to all the widgets you can use. If you do wish to expand on these options, add some more advanced styling, or introduce some interactivity, Rise Creative will be happy to assist.

Thanks!
Photo of Wilson Center

Wilson Center

  • 634 Points 500 badge 2x thumb
Thanks Peter.  My concern with using the text widgets is that requires us to manually enter the information.  Rise Vision is a great product but it is dense and complicated to the uninitiated.  I can guarantee you that my team operating the Cafe will not be able to manage a complex presentation,  On the other hand, they are more than capable of entering information into a Google Sheet document.

What I am trying to confirm is whether or not the data from a Google Sheet can actually be used to populate or even replace one of those text boxes.  Or again, if it only function as a spreadsheet with the basic linear structure of a spreadsheet.

I appreciate you many efforts to explain this, but I am still not picking up what you're putting down, as they say.

Also, I cannot find a any temple that uses the spreadsheet widget, free or otherwise, menu or otherwise.  This would certainly be a huge help in understanding how everything comes together.  Can you point me in the direction of the templates you had in mind?

Thanks!
(Edited)
Photo of Peter Cameron

Peter Cameron, Employee

  • 1,418 Points 1k badge 2x thumb
No problem, sorry I wasn't clearer in the earlier response. The google sheet widget can indeed be used to replace a text box - for example if you set the cell range on the component to only target cell A1 of the spreadsheet you will have created a defacto text box the contents of which can be updated via the google sheet, rather than going into the platform and editing the text box directly. The widget does not need to be used to output a direct copy of the google sheet. 

In terms of templates there are 2 premium templates, Restaurant Menu Board and Coffee Shop Menu Board which use the widget, as well as Menu Landscape and Menu Portrait which use the text widget to create a similar effect.

Hope this helps!

Peter