Turning Google Spreadsheets into a personal or group bookmarking service

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.
Screenshot of Delicious Bookmarkleting [Click to enlarge]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:

Google custom form interface

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

chevron_left
chevron_right

Join the conversation

comment 19 comments
  • Jeroen

    Looks great, but it doesn’t seem to work, with me at least. Button remains grayed out.. can’t submit

    • Martin Hawksey

      Weird what browser are you using?

  • Jeroen

    I Tried Firefox as well as Chrome.

  • Jeroen

    Thanks Martin, it all works like a charme now!
    Great tool

    • Martin Hawksey

      Thanks Jeroen glad it worked in the end.
      (The problem Jereon had was because I’d left a bookmarklet url in the template this was causing confusion – I’ve now editted 😉

  • Luke

    Hi Martin
    I seem to have the same error – it all works up until I try to submit – the submit button is greyed out…
    Can you advise?
    Thanks
    Luke

    • Martin Hawksey

      Test the live form via the spreadsheet menu and make sure the summit button works and the url matches the one in the bookmarklet

  • Luke

    Thanks Martin
    I got it working and it’s great! (the url’s didn’t match)
    Luke

  • Arco

    Nice scripting!
    Is it possible to close the panel automatic after submit?
    Arco

    • Martin Hawksey

      @Arco I looked into this and at the time the answer was no 🙁

  • Argo

    Wow!!! Really glad to have discovered this post.
    This week I have replaced both dropbox and bitly with gdrive at once. so just one login, one place of managment! Really really thanks Martin! I’m still a novice with scripts but now I’ve a reason more to go deeply.

  • lucas

    hi Martin,
    It seems that a google spreadsheet update broke something in the method 2 template, and the URL generated in step 3 of the instructions returns this error message: “Sorry, the page (or document) you have requested does not exist.”
    Thanks for sharing this template,
    Lucas

    • Martin Hawksey

      ahh I’ve updated the template the new equation in cell A9 is ="javascript:(function(){f='"&A8&"?url='+encodeURIComponent(window.location.href)+'&title='+encodeURIComponent(document.title);a=function(){if(!window.open(f,'mightchange','location=yes,links=no,scrollbars=no,toolbar=no,width=450,height=250')){location.href=f}};if(/Firefox/.test(navigator.userAgent)){setTimeout(a,0)}else{a()}})();"
      [I’ve also updated the instructions as well]
      Thanks!
      Martin

  • lucas

    its working now, thanks a lot and congratulations for your excelent work

  • Paddy Moogan

    Thanks for this Martin, was just what I was looking for.
    One question – how do I add new input fields to the form? I’ve gone through the script and duplicated the bits of code that seem to be powering the input form, but I can’t seem to get it to work.
    Any pointers?
    Thanks!
    Paddy

    • Martin Hawksey

      From memory I think you just edit the form from the spreadsheet menu

Comments are closed.

css.php