Developer Tips & Tricks - Creating a Customized Layout for the Google Spreadsheet Gadget

October 19, 2011

A couple of weeks ago, the following question was posed on our forum:

"Has anyone been able to take the contents of a Google spreadsheet and re-arrange it systematically using logic or programming? Like, for example, entering some restaurant menu items into a google spreadsheet and having that displayed in a non-tabular way on the presentation, or doing some math to the numbers before rendering it, or other customized things? Just thinking through different approaches to this other than the google spreadsheet gadget..."

Since nobody had any examples for sshank, we set out to show how this can be done. Moreover, we wanted to show that it can, in fact, be accomplished with our existing Google Spreadsheet Gadget.

Get Your Message Noticed.SEE HOW IT WORKS

The Google Spreadsheet Gadget is one of our most popular Gadgets. By creating your own layout file and accompanying CSS, you can create any number of different looks to override the default table layout. Using sshank's restaurant menu idea as my inspiration, I quickly created some menu data:

 

I then created this simple XML layout:

 

This layout does not use any HTML tables, but instead utilizes div tags and lists. There are a couple of important things to note about this layout:

  • I replaced the value of the url attribute with the URL to my custom CSS file. More on this in a minute.
  • I have assigned one of the div tags a class of repeat. This indicates that this HTML element and all of its children will be repeated for every row in the spreadsheet. This saves you from having to repeat the same markup for every row of the spreadsheet, which would be a maintenance nightmare.
  • The HTML elements that have classes of a, b, c, and d refer to the corresponding column of the Spreadsheet. In our case, the mapping is:
    • a = Type
    • b = Item
    • c = Description
    • d = Price
  • I've applied a class of heading_font-style to the same element with class b. This means that the Item will use the Heading Font as specified by the Gadget setting.
  • I've applied a class of data_font-style to the same elements with classes c and d. This means that the Description and Price will use the Data Font as specified by the Gadget setting.

I'm applying a custom style to all of the HTML elements with a class of a. This means that the Today's Specials, Appetizers and Dinner headings with have this styling applied to them. Since there are really only two font settings that can be controlled by the Gadget's settings (Heading Font and Data Font), I just create a new class here containing the font settings that I want.

Back in the Presentation editor, I check the Use Layout URL checkbox, then paste in the URL to my XML layout file. I also set Scroll By to None since I did not include any markup for scrollbars in my layout:

 

What we end up with is both clean and simple (and not a table!):

 

The XML and CSS files used in this example can be downloaded from here and here. Just right click on the page and select Save As. You can read more about the Google Spreadsheet Gadget on our Wiki. And please do keep using our forum to ask questions and share ideas. You never know when one of your posts might be highlighted here on our blog!