Turning Google Spreadsheets into a personal or group bookmarking service

Martin Hawksey

13 years ago

Recently Yahoo announced that it was going discontinue/sell their Delicious bookmarking service. This led to a swath of messages in my networks by people looking for alternatives and strategies for getting their stuff out.
This isn’t a post about how you can get your Delicious bookmarks into something different (most of the other bookmarky sites like Diigo and Google Bookmarks have these options), instead I using this post to look at two ways you can capture bookmarks into Google Spreadsheets.
Why Google Spreadsheets? Other than continuing my personal journey with Google Spreadsheets you could also argue this solution gives you more flexibility and control over the data.
At the core of this idea is the use of ‘bookmarklets’. Most bookmarking services provide users with a bookmark that when clicked on launches a form for confirming and collecting details about the link you want to save. The background process is fairly basic, the bookmark runs a script which opens a popup browser window, passing some details about the page you were viewing to the form.

Method 1 – using a vanilla Google Form

So if all the Delicious bookmarklet is doing is directing you to a form why not redirect it to your own form, a Google Form! Below if the JavaScript Delicious use (expanded to make more readable):

javascript: (function () {
    f = 'http://delicious.com/save?url=' + encodeURIComponent(window.location.href) + '&title=' + encodeURIComponent(document.title) + '&v=5&';
    a = function () {
        if (!window.open(f + 'noui=1&jump=doclose', 'deliciousuiv5', 'location=yes,links=no,scrollbars=no,toolbar=no,width=550,height=550')) location.href = f + 'jump=yes'
    };
    if (/Firefox/.test(navigator.userAgent)) {
        setTimeout(a, 0)
    } else {
        a()
    }
})()

The interesting bit is line 2 which builds the url for the Delicious save form including your current window location (i.e. the link you want to save) and corresponding page title. To use a Google Form you can just enter your form url pre filling fields by adding &entry_1=, &entry_2=. For example if you created a form with a title field and url field (1st and 2nd), your url would look something like this:
f = ‘http://spreadsheet.google.com/viewform?formkey=randomformidentifierkjflsj44&entry_2=” +encodeURIComponent(window.location.href) +”&entry_1=”+encodeURIComponent(document.title);
If you don’t want to mess with bits of code I’ve made this template spreadsheet for you to copy which has a readymade form and also generates a custom bookmarklet for you to use.

Method 2 – creating a custom Google Spreadsheet form interface

Method 1 was about piecing together bits I already knew, method 2, which uses the ~~Google Apps Script User Interface (UI) services~~, (Update: As this is deprecated switched to using HTMLService) was about using something new. If you don’t want to read about the ‘journey’ you might want to just jump straight to grabbing a copy of the spreadsheet below. Before you disappear some things to think about:

You can share your bookmarlet with other people turning it into a group bookmarking account
I’ve just pulled the name and location of the bookmarked page, you could scrape additional page content

*** Googlicious – Google Spreadsheet Bookmarker ***

How-to Method 2

Update: UI Service is no longer part of Google Apps Script and the above example has been migrated to HTML Service. The information below is kept for historic reference only.

Previously in Collecting any data in a Google Spreadsheet using GET or POST by publishing as a service I highlighted how you can publish a spreadsheet as a service which allows you a different way to put data into a Spreadsheet. As part of this you can use the UI Services to build a custom interface around this. Using this method means I can programmatically create my own form interface like the one shown below:

Unfortunately I haven’t found a tool with a graphical interface which can help with designing custom interfaces (although as the UI Service is based on the Google Web Toolkit, there might be something I can use there). So to generate the interface above required all of this coding:

    var app = UiApp.createApplication().setTitle(
      "Save bookmark to Google Spreadsheet");
    app.setStyleAttribute("padding", "10px");
    var titleLabel = app.createLabel("Save Bookmark");
    titleLabel.setStyleAttribute("text-align", "left");
    titleLabel.setStyleAttribute("font-size", "135%");
    titleLabel.setStyleAttribute("font-weight", "bold");
    var noteWin =  app.createLabel("Bookmark saved. Please close this window.");
    noteWin.setId("noteWin");
    noteWin.setStyleAttribute("text-align", "center");
    noteWin.setStyleAttribute("font-weight", "bold");
    noteWin.setStyleAttribute("background", "#FFFF00");
    noteWin.setStyleAttribute("color", "#FF0000");
    noteWin.setVisible(false);
    var pageTitleLabel = app.createLabel(
      "Title");
    var pageTitleText = app.createTextBox();
    pageTitleText.setName("title");
    pageTitleText.setWidth("100%");
    pageTitleText.setText(e.parameter.title);
    var urlLabel = app.createLabel(
      "URL");
    var urlText = app.createTextBox();
    urlText.setName("url");
    urlText.setWidth("100%");
    urlText.setText(e.parameter.url);
    var tagsLabel = app.createLabel(
      "Tags");
    var tagsText = app.createTextBox();
    tagsText.setName("tags");
    tagsText.setWidth("100%");
    var notesLabel = app.createLabel(
      "Notes");
    var notesText = app.createTextArea();
    notesText.setName("notes");
    notesText.setWidth("100%");
    var saveHandler = app.createServerClickHandler("saveBookmark");
    var saveButton = app.createButton("Save Bookmark", saveHandler).setId("saveBut");
    var listPanel = app.createGrid(4, 2);
    listPanel.setStyleAttribute("margin-top", "10px")
    listPanel.setWidth("100%");
    listPanel.setWidget(0, 0, pageTitleLabel);
    listPanel.setWidget(0, 1, pageTitleText);
    listPanel.setWidget(1, 0, urlLabel);
    listPanel.setWidget(1, 1, urlText);
    listPanel.setWidget(2, 0, tagsLabel);
    listPanel.setWidget(2, 1, tagsText);
    listPanel.setWidget(3, 0, notesLabel);
    listPanel.setWidget(3, 1, notesText);
    // Ensure that all form fields get sent along to the handler
    saveHandler.addCallbackElement(listPanel);
    var dialogPanel = app.createFlowPanel();
    dialogPanel.add(titleLabel);
    dialogPanel.add(noteWin);
    dialogPanel.add(listPanel);
    dialogPanel.add(saveButton);
    app.add(dialogPanel);

Why oh why oh why?

So why go to all this extra effort for a very similar result. For a start it means I don’t have to use the styling of traditional Google Forms which I don’t like. More importantly it opens the possibility of making the form more dynamic by using existing data from the spreadsheet. So for example I could pull in commonly used tags or even just indicate if a url has already been bookmarked before.
As well as form creation Google Apps Script could be used to do more with the submission, like automatically tweeting it or sending it somewhere as an email or even choosing which sheet it should go to (i.e. you might have a public and private sheet). I’ll let you imagine the possibilities