15 January 2011

Workaround for HTML in Apps Script UIApp

I've come across a frustrating issue in Google Apps Script in that it does not support HTML in UIApp widgets. Often, a simple task such as displaying some multi-paragraph help text can be an extremely frustrating issue.

A quick and easy work-around is to utilise a function to convert a formatted string, with new-lines and indents into a VerticalPanel with a series of Labels to represent each line. Here is one such function:
/** * Returns a widget formatted with the text. */ function formatTextPanel(a, text) { var app = a ; var panel = app.createVerticalPanel(); var lines = text.split("\n"); for (var i=0; i<lines.length; i++) { var cleaned = removeLeadingWhiteSpace(lines[i]); var label = app.createLabel(cleaned[1]); if (cleaned[1].length == 0) { label.setText("-"); label.setStyleAttribute("visibility", "hidden"); } if (cleaned[0] > 0) { var margin = cleaned[0] * 6; // 6 px per char label.setStyleAttribute("margin", "0px 0px 0px "+margin+"px"); } panel.add(label); } return panel; } /** * Remove whitespaces from start and report how many. */ function removeLeadingWhiteSpace(text) { var i = 0; var res = []; while (i < text.length && text[i] == ' ') { i = i+1; } res[0] = i; res[1] = text.substr(i); return res; }
To use, just copy and paste into your script and call formatTextPanel passing a UiInstance and the text that you want turned into a panel. Here is an example:

function renderHelpDialog() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var app = UiApp.createApplication(); app.setTitle("My Help Dialog"); app.setHeight(480); var helptext = "" + "This is a line.\n" + "A blank line will follow this one.\n\n" + " This line will be indented.\n" + " This will be indented some more.\n" ; var helpPanel = formatTextPanel(app,helptext); app.add(helpPanel); ss.show(app); }
There is scope to easily modify the formatting function to add styles to individual labels to, for example, offer bold formatting, larger font sizes, wiki-like syntax, and more.

06 September 2010

Show eBay Auctions in Google Apps Calendar

This is a great demonstration of how Google Apps Script makes some really useful things quite trivial to implement.

The following script uses the provided UrlFetchApp and XML parsing API to fetch and interpret an eBay sellers listings. It then uses the Calendar service to mark the events. The script is designed to run as a time-driven event. To avoid creating duplicate events, we make use of the CalendarEvent tagging feature.

So, here is the code:

function getFeedDoc() {
  var url = ScriptProperties.getProperty("feed_url");
  var feed_txt = UrlFetchApp.fetch(url).getContentText();
  var doc = Xml.parse(feed_txt, false);
  var items = doc.rss.channel.getElements("item");
  for (var i in items) {
    var endDateMs = items[i].getElement("urn:ebay:apis:eBLBaseComponents",
    var endDate = new Date(parseInt(endDateMs));
    var guid = items[i].getElement("guid").getText();
    createEvent(endDate, items[i].getElement("title").getText(), guid);
function createEvent(endDate, title, guid) {
  var cal_id = ScriptProperties.getProperty("cal_id");
  var cal = CalendarApp.getCalendarById(cal_id);
  var events = cal.getEventsForDay(endDate);
  for (var e in events) {
    if (events[e].getTag("guid") == guid) {
  cal.createEvent(title, endDate, endDate).setTag("guid", guid);

Create a new spreadsheet and open up the script editor. Copy and paste the code above. Make sure you set the following Script Properties

URL of the RSS feed of the seller's items
ID of the calendar that you wish to update

You will then need to set the trigger. Go to Triggers, Current script's triggers and add a new Time-driven trigger to run getFeedDoc() every hour or so.

Run the function getFeedDoc() to confirm that it works.