Use a Google spread sheet as a feed in blocks

The purpose of this application note is to give an example of a feed script application and show the workflow if one want to provide text data to Blocks by editing the cells in a Google Spread sheet that can be shared to users over the internet.

The example is a typical reception setup where today’s visitors get a personal salutation that is rolling over the screen. The data is maintained in a shared Google Sheet and a comma separated text file is automatically published that Blocks in turn read every time the slide show is showed. In blocks we use a slideshow block with a single slide that decides the layout and the style of the content. Blocks is then automatically replicating this single slide for each entry in the data table.

The result can look something like this.

To do this you will need a google account which is free. Go to https://docs.google.com/ and log in to the account you want to use.

Create a new spreadsheet and name it to something describing.

Use row one for headlines and give the columns exclusive names.

It may be a good idea to lock those cells so other users cannot change the name of the columns breaking the connection to blocks. This is done under the menu option Data/Protected sheets and ranges.

Create some data columns and create some sample data.

Save the document if not autosaved.

To make the sheet available for blocks you should use the option File/Publish to the web.

Make sure to select the sheet you are working on (If more than one) and select Comma separated values (.csv) in the dropdown to the right.

Make sure to tick the option Automatically republish when changes are made. Cut out the link to the sheet and paste it into the Blocks feed script.

Accept to publish the selection.

Unzip the archive containing the source file and the built file and copy both files to the feeds folder on your blocks server. visitorstodayfeedscript.zip The locations for this are your default pixilab blocks root/script/feed This application note assumes that you run Blocks 5 or higher. Make sure that you have downloaded and installed the latest script package from GitHub on the server. Unzip the files and merge them into your Blocks script folder. Make sure the folder structure ends up like this:

If this is the first time working with scripts it a good idea to read up a bit on Blocks scripting. https://pixilab.se/docs/blocks/drivers/tools In there you can find a link to how to set up a developers environment and get some general knowledge regarding Blocks scripting.

If you have a code editor set up for Blocks you may go ahead to open the Visitorsfeed.ts there by dragging the Blocks root folder into the editor to open it as a project folder. Browse to the VisitorToday.ts and double click to open the file.

Find the line that points to the Google sheet and replace the example feed with your own retrieved from the google setup above.

 const kSpreadsheetURL = "https://docs.google.com/spreadsheets/d/e/2PACX-1vQVE3v1sikxm1esuNdlW-erWrtA_GemjtKVJtmd0fpXQmux7AS8_7yFgewLKj99IY0NS5qz88NgJ8go/pub?gid=0&single=true&output=csv"

If you used the same headlines as in my example you can just save now and go ahead and set up the Slideshow.

If you want to use other data and different headlines (column names) in your feed you will need to change a few more things in the feed script.

Change the typescript Interface. This is like a mould, the purpose of this is to make sure the code editor gives a warning if you try to use wrong datatypes or names while programming. Change the names and data types here according to your requirements.

Change the class Item to match your requrements. This is the fields and their descriptions that late will be exposed to the user in Blocks. The datatypes should match the datatypes used in the Interface above, the names can be anything you like that describes the data best to the Blocks user.

Edit the constructor and map the data from the csv file to the fields created in the Item class.

Finally, save the file and make sure it's compiled to generate/update a corresponding .js file next to it. Check the last modivied timestamp of the .js file to make sure it's no older than the corresponding .ts file.

If you want to use a different name for your feed script, do not forget that the export class name has to match the name of the file or vice versa, other vice Blocks will not recognise the script.

If you want a different name for the actual data collection that will be visible in the Blocks dropdown menus edit just replace "Visitors" with your own text.

export class VisitorsToday extends feed.Feed {
	constructor(env: feed.FeedEnv) {
		super(env);
		this.establishFeed(new Collection("Visitors"));
	}
}

The example block can be downloaded here. Use the import Block feature to bring it in to your own Blocks system.

Open up the block for edit. The root block is a slideshow with two pages., one is just a general message, in this case a Picture. The other slide is TodaysVisitors slide.

The purpose of this setup is to reload the data from google every time this outer slideshow loops and give the feeds slideshow enough time to load the content and create the replicated slides.

Open the Visitors Today slide for edit. This is a composition containing a background, a Welcome text and the FeedSlideshow.

The FeedSlideShow is where the magic happens. Open it up for edit.

As you can see this slide show has only one single slide. This is our template that Blocks use to automatically replicate all the other slides from. This is where we set up the slide duration and transition timing for the slideshow. Down the bottom we have also selected Indexed Property under Child Replication. This exposes the binding to the feed as seen in the screenshot below.

Open the Slide for edit. This is where we would do create the actual layout, any styling and create all the text bindings to the feed properties.