Monday, 24 June 2013

Confusion about Apps Script Projects in Spreadsheets.


I'm in some confusion about how Apps Script projects work when embedded in a Google Spreadsheet. In my current spreadsheet, when I choose "Script Editor", I see this...



As you can see, there are multiple projects in the spreadsheet. I don't get this. It always happens that when I copy a spreadsheet too... that I end with a "Copy of XXX" and "XXX" Apps Script projects inside the spreadsheet. 

I can add extra projects via the "Create a new project" link but I can't remove projects from spreadsheets. This gets more confusing if both "Copy of Web App n Stuff" and the "Term Week Dates Booking Project" have a doPost () function in them. Which function gets called? 

Why would I want more than one Apps Script project in a spreadsheet?

Why can't I flip a project out to be a standalone Apps Script project?

When making copies of spreadsheets - why do I end up with multiple projects in a spreadsheet?

How might I remove an Apps Script project from a spreadsheet?



Blogger's Identity Issues

Google want you to be you. They sort of insist. And they'd really prefer it if you, the real you had a Google+ profile. And when creating a new blog on Blogger they try and make you link it to a real person's Google+ profile.

Except there's a problem. If you create blog posts with a regular Google identity when at the University of York ( or anywhere ) then if you leave, all the images from your blog posts disappear and the blog posts look as if they are written by "Unknown". Hardly ideal, in fact, pretty rubbish.

I've written about this issue before. It's caused because Blogger is sort of "held in stasis"... it is not a good fit with Google's other tools and yet it is so popular it would be difficult for them to close it down. And given Google's track record with social tools, I imagine they've been working out how to close it down for a while now.

The Problem


A department wanted to avoid this strange behaviour of Blogger because they were setting up a new blog and knew they'd be handing it over to someone else in the department in six months time.

So we decided to use what we call a Non Personal Account (NPA). These are departmental accounts like chemisty@york.ac.uk or big-project@york.ac.uk that lots of people may be able to answer. Normally the person who had the login details for NPA would delegate email access to other people. This means a team of people might share the responsibility for answering emails ( that would come from big-project@york.ac.uk rather than their personal email account ).

An issue with using an NPA for shared Blogger accounts is that you can't delegate access like you can with email accounts. You have to share the username and password which is something Google frown upon. You have to be a real person remember and shared accounts break all that, horribly.

More Problems...

We, or rather the department I was working with found that even before they'd got started blogging, simply registering with Blogger had issues.

The first hurdle was choosing a first and second name for the account. Reasonably they chose an acronym of their department and "Admin" as names. They were then told that these weren't real names and that their account would be suspended if the account didn't have real names.

When choosing a "birthday" for the account, they chose, the start date for their project which as it turns out made the account younger than 13 years old, locking their account and requiring assistance to get it re-instated.

You also have to remember to change the NPA password every time somebody leaves.

And the problems that arise from being logged in to two accounts ( their York one and the NPA ) are hilarious. If you like laughing at pain.

Google's Response...

During all these fun and games, Google helpfully suggested that "what we really need is a Google+ Business Page"... like this.  I'm not convinced.

The thing about posts in Google+ is that there is no idea or feeling of drafting. Posts aren't something you can save and come back to. You can't share editing with a colleague. 

You can't add multiple images. You can't alter layout. It's like comparing DTP to a text message. 

You can't easily collect "posts" together into collections... or collate stuff. The post tags don't really work very well either. With Google+ it feels like you're throwing something into the information stream, something quick and ephemeral, but blogs have always had a more permanent feel, a feeling that a post from a few years ago can still be relevant and part of a larger whole.



The Elephant in The Room...


...is of course that Blogger is pretty much dead to Google and they'd love everyone to start using Google+. I don't mind this strategic coercion, I like Google+ ( a lot ) but when people want a blog, you know maybe they actually want a blog. They want posts and pages with navigation. And they might want it to look a certain way too. People are fussy like that - when they're scrumping for apples onions won't do.


Not A Great Place To Be...

... not having a blogging solution at York that I just don't feel comfortable with...

  • Using a Blogger blog with an @york.ac.uk account isn't really tenable. Old blog posts get shredded if you leave.
  • Using a Google+ Business Page instead of a blog isn't remotely realistic.
  • Using a Blogger blog with an NPA account isn't very secure (shared password) and has a number of set up and usage issues. I tend to advise people to use two separate browsers ( i.e Firefox and Chrome ).
  • Using a Blogger blog with a consumer gmail account is probably best. It's long term, in that it won't blow up if you leave York.

... and the worst part is that Google have all they need in terms of technology sitting right there. It just need a little re-wiring.

Google could spruce up Google Sites - which has a woeful Announcements page feature, or make Google+ pages and posts just a shade more configurable, or even make collections of Google Docs be presentable as a blog. It's all there - there's nothing new. It's only blogging. And in terms of our needs, corporate or organisation blogging isn't even very demanding. We don't need the latest far out technologies, we just need a blog... for a department... that doesn't do evil.









When Do You Have Your Weekly Team Meetings?

I met with a department recently and they'd made a spreadsheet where they could tell each other when their preferred time to have a weekly meeting would be. When you have lots of people working different hours as they do, it's difficult to choose a single time that doesn't always exclude someone.

They created a sheet for everyone that looks like this.... You can see how complex things can be schedule-wise for just one person.



The problem was that when they tried to calculate when the best time to meet would be, they got this...


...and discovered that there's never a good time to have a Team Meeting - except for Friday breakfast and over lunch on Tuesday.

I think the problem may have been our inability to imagine how to go about solving the problem, mine especially. Using lots of embedded arcane formulas in spreadsheets has always worried me. I imagine this is simple for a spreadsheet expert. 

What we needed to was work out all the available times ( making them numerical rather than "IF-NEED-BE" ) and then rank those times based on percentages of all the times. This ( below ) took a few lines in Apps Script.




And we discovered that the BEST time to have a weekly meeting, based on the data collected, was Friday at 4PM.

... crazy eh?


Wednesday, 19 June 2013

Converting a Google Document to simple HTML

This article looks at creating an application that converts a Google Document to HTML, saves it in a folder on your Google Drive and then shares that folder on the web.

This tool is meant for those situations where you have to work in HTML but you'd prefer it if you could use Google's fantastic collaboration features on the content first.

What This App Doesn't Do

It doesn't try to be too clever. I quite like HTML from back in the 90s when it was simple. Most other systems where you can add HTML don't like you getting too fancy either.

It doesn't try to size the images or even get ALL the document elements like Tables of Contents or Page Breaks, it just does the absolute basic needed to copy the source HTML into Blogger or into your CMS and you will need to do more than a little "fixing up" along the way.

What is really useful about this app is that all the images are now hosted and so you don't need to go through the painstaking process of copying the images into new images, saving them online somewhere, getting the URL of those images and replacing the right image src in your new HTML source. This was a pain for me.

Also, the app doesn't HTML encode the "<" and the ">" so you can add HTML tags like the preformatted tag or whatever, knowing that they'll still be there in the outputted HTML. I use the font Courier New to tell it that I don't want the converter code to touch it, but one of the problems is that ANY HTML that is lurking in "courier new" code is shown, as HTML.

The results can be hilarious. Ah well. I said it would need "fixing" up. Here's an example of the HTML it generates.

The application itself looks like this.


The simple application lets you select which Google Doc you want to convert to HTML and then loops through the document elements and creates a html page.

Any images found are added to Google Drive hosting ( their URLs changed to the new location ) letting you copy the HTML from the "index.html" file and paste it anywhere you need.

And Finally

You can try it on your documents here: https://script.google.com/macros/s/AKfycbyRt8FCEDMtZELx7-E_c3Hjktaaf1qJwRmitfRx48UocIP8ViBA/exec

Or if you'd like to rummage around the code please feel free to improve it.
https://script.google.com/a/york.ac.uk/d/1BWQRHrM589TnsXviuB3Zzws_ShFDF6sEdaO4sg4Eht0kuZx1iou4Xxn2/edit

Inserting A Google Doc link into a Google Spreadsheet

This article looks at using Apps Script to add new features to a Google Spreadsheet.

At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form.

Spreadsheets can be better than Forms for data collection because:

  • The spreadsheet data saves as you are editing.
  • If you want to fill in half the data and come back later, your data will still be there.
  • The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessary
  • The commenting features are brilliant - especially the "Resolve" button in comments.

One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often all look too similar and you don't know what you are getting until you have clicked it.

For example, one part of your spreadsheet might ask "Do you have any supporting files?" and it would be handy to be able to insert a link to any number of Google Docs which might include a project plan Google Document, a PDF letter of support and a budget spreadsheet.

Like this.


When you select "A file from Google Drive" a dialog is presented showing you your Google Drive files, like this.


In this example, I am selecting the "What goes where: Action Plan" file. A =HYPERLINK() formula with the name and link of this Google Document is then inserted into spreadsheet.

Adding More Abilities

Once I'd created the ability to attach a Drive document, I knew that people would ask for other obvious features.

1. Uploading a file directly from your computer

You may have noticed that the menu also allows you to "Upload a file from your computer". This simply uploads the document into your Google Drive and then links to that uploaded file.

2. Attaching a regular link

There is also the ability to add a simple "regular hyperlink" - we often have documentation in our wiki or on web servers that need linking to as well as GDrive documents. This tool provides a simple interface


Conclusions: Keeping It Simple

This tool doesn't try to make sure that the permissions on the attached Drive file match those of the spreadsheet, but that is a good thing and anyone without access to a particular file can request access the usual way.

I experimented with showing an icon of the file type, or a link icon if linking to an external site, but that made the code need to look in adjacent cells to see if the data was empty.

This code doesn't save the uploaded files into a particular folder because it would make the code less "copy and pastable" but you could easily add that feature.

I also thought it might be nice to add a cell comment to say who uploaded the file. Again, that wouldn't be hard to add yourself.

There is a little interface clunkiness with the UiApp which means that the Google Drive chooser dialog window is a bit cramped, but all the functionality works well enough to add the extra dimension of collections of related files to your spreadsheets. Hopefully by publishing this article I may get some help with this.

You never know, maybe Google will roll some of these features into the main applications, but until then you can use this addition to easily collect together references to various Google Docs in a spreadsheet.

Also, I wonder when Google will add a Script Editor, or the ability to add menus to Google Documents? ( Update: Since I wrote this, Google have now added this. Cool. )


How To Add This Menu To Your Spreadsheet

The code to add an "Attach..." menu to your spreadsheet is in this spreadsheet called "Inserting GDrive docs into a spreadsheet" here:

https://docs.google.com/spreadsheet/ccc?key=0Ajnu7JgRtB5CdGtoUmM1YnlHaS1KWVowVkxtMnFzWFE#gid=0



1. Make a copy of the spreadsheet.
2. Go to menu Tools >> Script Editor
3. Copy and paste the code into the Script Editor of another spreadsheet of your choice.
You will have to run the onOpen() function to get the menu to appear.

Creating a Restricted Multiple Choice Form With Apps Script

I was contacted and asked if I could create a Google Form where the user can only check 8 out of 16 checkbox choices. Google Forms can't do this sort of restriction and so I whipped together a quick web application to do this.
I created an Apps Script in Google Drive and then added this code below.

function doGet(e) {
  var app = UiApp.createApplication().setTitle("Restricted Multiple Choice").setHeight(250).setWidth(500)
  
  // Define the grid layout
  var grid = app.createGrid(16, 3 ).setStyleAttribute(3, 2, "width", "420px").setCellPadding(5)
  grid.setStyleAttribute("margin-left", "auto")
  grid.setStyleAttribute("margin-right", "auto")
  grid.setStyleAttribute("margin-top", "100px")
  
  // Create the text at the top
  var html = ''
  html += "Restricted Multiple Choice"
  html +=  "You can only choose 3 of the items below."
  var html_widget = app.createHTML(html, false)
  grid.setWidget(1, 2, html_widget)
  
  // Create the checkboxes
  var select = app.createServerHandler('selectHandler').addCallbackElement(grid)
  
  var checkbox_1 = app.createCheckBox("Item 1").setId("item_1").addValueChangeHandler(select).setName("item_1")
  grid.setWidget(3, 2, checkbox_1)
  
  var checkbox_2 = app.createCheckBox("Item 2").setId("item_2").addValueChangeHandler(select).setName("item_2")
  grid.setWidget(4, 2, checkbox_2)
  
  var checkbox_3 = app.createCheckBox("Item 3").setId("item_3").addValueChangeHandler(select).setName("item_3")
  grid.setWidget(5, 2, checkbox_3)
  
  var checkbox_4 = app.createCheckBox("Item 4").setId("item_4").addValueChangeHandler(select).setName("item_4")
  grid.setWidget(6, 2, checkbox_4)
  
  var checkbox_5 = app.createCheckBox("Item 5").setId("item_5").addValueChangeHandler(select).setName("item_5")
  grid.setWidget(7, 2, checkbox_5)
  
  var checkbox_6 = app.createCheckBox("Item 6").setId("item_6").addValueChangeHandler(select).setName("item_6")
  grid.setWidget(8, 2, checkbox_6)
  
  var checkbox_7 = app.createCheckBox("Item 7").setId("item_7").addValueChangeHandler(select).setName("item_7")
  grid.setWidget(9, 2, checkbox_7)
  
  // Create the "convert" button
  var handler2 = app.createServerHandler('submitHandler').addCallbackElement(grid);
  var convert_button = app.createButton('Submit Selection', handler2).setId("btn")
  grid.setWidget(10, 2, convert_button)
  
  // Create the message at the bottom
  var msg = app.createHTML("Please make only three choices.", false).setId("msg")
  grid.setWidget(11, 2, msg)
  
  
  app.add(grid);
  return app
}
function submitHandler(e){ 
  var app = UiApp.getActiveApplication( )
  
  var msg = app.getElementById("msg")
  msg.setVisible( true )
  msg.setText( "This doesn't do anything yet" )
  
  return app
  
}
function selectHandler(e){
  var count = 0
  if ( e.parameter['item_1'] == 'true' ){
    count = count + 1 
  }
  if ( e.parameter['item_2'] == 'true' ){
    count  = count + 1 
  }
  if ( e.parameter['item_3'] == 'true' ){
    count  = count + 1 
   }
  if ( e.parameter['item_4'] == 'true' ){
    count  = count + 1 
  }
  if ( e.parameter['item_5'] == 'true' ){
    count  = count + 1 
  }
  if ( e.parameter['item_6'] == 'true' ){
    count  = count + 1
  }
  if ( e.parameter['item_7'] == 'true' ){
    count  = count + 1 
  }
  
  
  var app = UiApp.getActiveApplication( )
  var msg = app.getElementById("msg")
  var btn = app.getElementById("btn")
  if ( count > 3 ){
    btn.setVisible(false)
    msg.setText( "You have chosen more than three items" )
  }else{
    btn.setVisible(true)
    msg.setText( "You have chosen " + count + " items." )   
  }
  return app
  
}



The application itself looks like this, the submit button is only clickable once you've clicked three checkboxes.
The resulting application is here, give it a whirl.
https://script.google.com/a/macros/york.ac.uk/s/AKfycbwczyQumF8qax2HGoZt9K-RzzY7ItWdKSvyH_aQq2p-PQ9vXNiF/exec


If you'd like to make a copy of the application's code to figure out how it works, the Apps Script code is here:
https://script.google.com/d/1h3PsyWZ2qNxyTDpDPP4__f-_0LjhysLti9NmHMRUCB6ktB2nrcPgvxYT/edit?usp=sharing


In Conclusion

This unusual little app is one of many that I'm currently exploring. Lots of people believe that what they want is a simple form for gathering data, but are finding out that they want the data to be "intelligent" and dependant on other items selected.

Just because it walks like a form, looks like a form and quacks like a form, doesn't meant it's a form.




Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems.

A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it.

The QR app looks like this.



The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this.


The Web Application

The web application, which only checks items in or out and should be used on a phone in conjunction with a QR code scanner iphone app, like Scan.
The application is written in Apps Script looks like this below. It shows the current status of that particular camera and lets you check a camera out.


Building This Application

There's a strange "chicken and egg" situation about building this application. In order to create a QR code that would lead to an individual camera, you first need to "Publish" your web app so that we have the URL to your application. Once published we can use the URL for our application and start populating the spreadsheet with cameras.

Populating the spreadsheet

First, I added some URLs to pictures of cameras, and titles and descriptions to my spreadsheet.
Next, in the spreadsheet in cell C2 you can see the formula...

=CONCAT("https://script.google.com/a/macros/york.ac.uk/s/YOUR_URL_GOES_HERE/exec?id=",A2)

… this formula creates the URL that is appended with "?id=231" etc and will be used to generate a unique QR code image.
In cell D2 I have the formula …

=make_QR(C2)

… this formula calls an Apps Script function that looks like this...

function make_QR( url ) {
  /*
  Note: If you add a number to the domain, as per the documentation, the https starts erroring. I think.
  Note: URLs are limited to 2K in length 
  From: https://google-developers.appspot.com/chart/infographics/docs/overview
  */
  
  var size = 150 // The height and width needed.
  var encoded_url = encodeURIComponent( url )  
  var image_url = "http://chart.googleapis.com/chart?chs=" + size + "x" + size + "&cht=qr&chl=" + encoded_url
  return image_url
}

… this takes our unique URLs and uses Google Visualisation Tools to create an image for the QR code, one of which looks like this.

We can display the image in the spreadsheet using the formula...

=Image(D2,1)

Deprecation Warning!

Although the Charting service is being deprecated, it's probably good to use until April 2015. See https://developers.google.com/chart/terms. I imagine it would be easy to generate and download all the QR code images you need, finding an alternative QR creation API in a few years time.

Creating The Web Application

The first part of the application gets the data from our spreadsheet, finding the relevant row. It then builds an application using UiApp objects for the values we want to display.
function doGet(e) { 
  if (typeof e.parameter.id  == 'undefined'){
    return no_id(e) // The URL doesn't have an ?id=345 on the end! 
  }
  
  var id  = parseInt( e.parameter.id ) // This is the id of the row in the spreadsheet.
  
  // Get the data from the spreadsheet and get the row that matches the id
  var this_spreadsheet_id = ScriptProperties.getProperty('this_spreadsheet_id')
  var ss = SpreadsheetApp.openById(this_spreadsheet_id)
  var sheet = ss.getSheetByName("Sheet1")
  var range = sheet.getDataRange()
  var last_row = range.getLastRow()
  var last_column = range.getLastColumn()
  
  for(i = 2; i <= last_row ; i++){
    var this_row = sheet.getRange(i,1 , 1, last_column)
    var values = this_row.getValues()[0]
    var row_id = parseInt( values[0] )
    if ( row_id == id){
      var title = values[5]
      var details = values[8]
      var status_txt = values[7]  
      Logger.log( "STATUS: " + status )
      var image_url = values[4]
      }
  }
  
  // Create an application
  var app = UiApp.createApplication().setTitle("Check in/out").setHeight(250).setWidth(400)
  
  // Create the layout
  var grid = app.createGrid(8, 3 ).setStyleAttribute(3, 1, "width", "420px").setCellPadding(5).setBorderWidth(0).setId('grid')
  grid.setStyleAttribute("margin-left", "auto").setStyleAttribute("margin-right", "auto")
  grid.setStyleAttribute("margin-top", "100px")
  
  
  var image = app.createImage(image_url).setWidth(100).setHeight(100).setStyleAttribute("margin-left", "auto").setStyleAttribute("margin-right", "auto")
  grid.setWidget(0, 1, image)
  ... and so on...  and so on ... 


We then need to create the buttons, like so...

  // Check in button
  var handler = app.createServerHandler('check_in').addCallbackElement(grid)
  var check_in_button = app.createButton('Check in', handler).setStyleAttribute("font-size", "24px")
  check_in_button.setId("check_in_button")
  grid.setWidget(4, 1, check_in_button)


… Later in the code we create a function ( or handler ) for the button. It updates our spreadsheet with CHECKED IN or CHECKED OUT, and also updates a few interface elements like this....
function check_out(e){
  var id = parseInt(e.parameter.id)
  Logger.log( "id: " + id )
  
  try{
    // Update the QR spreadsheet
    var this_spreadsheet_id = ScriptProperties.getProperty('this_spreadsheet_id')
    var ss = SpreadsheetApp.openById(this_spreadsheet_id)
    var sheet = ss.getSheetByName("Sheet1")
    var range = sheet.getDataRange()
    var last_row = range.getLastRow()
    var last_column = range.getLastColumn()
    
    for(i = 2; i <= last_row ; i++){
      var this_row = sheet.getRange(i, 1 , 1, last_column)
      var values = this_row.getValues()[0]
      var row_id = parseInt( values[0] )
      
      if ( row_id == id){
        var title = values[5]
        //var status_txt = values[7]
        var range = sheet.getRange(i, 8)
        range.setValue("CHECKED OUT")
        Logger.log( "Spreadsheet: CHECKED OUT")
        break
      }
    }
    
    var app = UiApp.getActiveApplication()
    
    //Update infoBox
    var infoBox = app.getElementById("infoBox")
    infoBox.setVisible( true ).setText( title + " has been checked out")
    
    var status = app.getElementById("status")
    var status_css = {'background': 'red', 'color': 'white'}
    status.setText("CHECKED OUT").setStyleAttributes(status_css)
    
    var grid = app.getElementById("grid")
    
    grid.setStyleAttribute(3, 1, 'background', 'red')
    
    grid.setStyleAttribute(5, 1, 'visibility', 'hidden')
    grid.setStyleAttribute(4, 1, 'visibility', 'inherit')
    
    
  }catch(e){
    Logger.log(e)
  }
  
  return app
  
}


… I used CSS to show and hide the non-relevant button. It's a bit clunky, but it does the trick ( please make better code available if you know how, .setVisible(false) didn't seem to work ).

And To Finish...

I added a listing application, if somebody might accidentally view the app without using one of the unique URLs in the spreadsheet. It looks like this. It displays all the cameras and might need some pagination if there were too many to display easily.



Generating a Google Document To Print Off the QR Codes

It was easy to take the QR codes and make a Google Document to easily format and print off onto labels. Like this...
function make_a_document(){
  var this_spreadsheet_id = ScriptProperties.getProperty('this_spreadsheet_id')
    var ss = SpreadsheetApp.openById(this_spreadsheet_id)
    var sheet = ss.getSheetByName("Sheet1")
    var range = sheet.getDataRange()
    var last_row = range.getLastRow()
    var last_column = range.getLastColumn()
    
    var doc = DocumentApp.create("QR Example Printable Document")
    var body = doc.getBody()
    //var table = body.appendTable()
    
    for(i = 2; i <= last_row ; i++){
      var this_row = sheet.getRange(i,1 , 1, last_column)
      var values = this_row.getValues()[0]
      
      var id = parseInt( values[0] )
      var title = values[5]
      var image_url = values[4]
      var qr_code = values[3]
      
      // Get the images of the cameras and the QR codes
      var response = UrlFetchApp.fetch(qr_code)
      var image_blob = response.getBlob()
      
       var response = UrlFetchApp.fetch(image_url)
      var qr_blob = response.getBlob()
      
      var paragraph = body.appendParagraph('')
      var text = paragraph.appendText(id)
      text.setFontSize(24)
      
      var image_paragraph = paragraph.appendInlineImage(image_blob)
      image_paragraph.setHeight(150).setWidth( 150 )
      
      var qr_paragraph = paragraph.appendInlineImage(qr_blob)
      qr_paragraph.setHeight(150).setWidth( 150 )
      
      var paragraph = body.appendParagraph(title)
      paragraph.setFontSize(12)
      
      body.appendHorizontalRule()
      
      
      }
 }


… The Google document with QR codes in looks like this.


… and the application on my iPhone looks like this...




Using Spreadsheets INSTEAD of Forms

Google Forms are a great way to quickly collect data into a spreadsheet but what if the data you are collecting is a bit too complex for a simple form to handle?

We've been experimenting with using a Google Spreadsheet, instead of a Form to gather information and finding that this approach has many advantages. We still use a Form to "initiate" the process, and the data gathered from the form is saved in a "central" spreadsheet.

When the form is submitted, the central spreadsheet makes a copy of a "template" spreadsheet. This spreadsheet is more "human readable" than a regular spreadsheet. When the form is submitted, the data is saved as normal, but it also fills in certain values in the copied template sheet, sets the right permissions and mails all the people who need to know about it a link to edit the template copy.

This template sheet has a "form-like" layout, including help ( shown at the right hand side ) and additional tools added with Apps Script. Tools include the ability to add a link to another Google Drive file. There are times when the data you want already exists ( as a document ) and you don't really want people to copy and paste that data into the form.

The copied template looks like this. Each of the fields has been sized ready to fit the data we expect. Some are even "colour bordered" to help different departments find their bits.

The "Central" Spreadsheet

The central spreadsheet, or the one that receives the input from the initial Form keeps a track of which files have been created, saving links to them in each row. It also has a "status" column that the copied spreadsheets know how to update as they get completed. This makes it really easy to add a simple "Status listing" web app to a Google Site ( shown below with dummy data ).

This means you can give nice "live" summaries to certain groups of people without scaring them with the prospect of looking a huge and hairy spreadsheet.

When Might I Use A Spreadsheet Instead of a Google Form?

If Your Data Will Be Very Textual and Complex

If you want to gather lots and lots of long bits of text, a Google Form might not be the best way for people to enter that data, mainly because a Form expects you add your data in one sitting. ( You can allow people to be able to edit their responses, but somehow this still doesn't feel right ).

We've found that when data is long and textual, even viewing it in a standard spreadsheet if difficult, and so have created lots of scripts to render someone's form data into a Google Document in a more readable format.

If Your Data Needs To Be Filled In Collaboratively

It's much easier to work on a shared spreadsheet that looks like a form than in a per-row spreadsheet.
Additional benefits of using spreadsheets, as opposed to Forms, include:

  • all the changes are saved in the file's "Revision History". You can see who added or deleted what and when.
  • All the edits are saved on-the-fly.
  • You can easily lock down parts of the spreadsheet so that people don't accidentally change it.

The "Insert Comment" feature, with the ability to "+" add someone is fantastic. If a part of the input isn't clear to you, you can ask for help from someone and they get a link to come and comment on that part of the document.

If Your Data Is Modular

In our spreadsheet, we even have a template sheet that, using custom made menus can be used to make "more sheets like this one". This is great in those circumstances when you might want people to submit something like recipes, each with the same items ( such as ingredients, method and picture ) and you want to be able to allow them to add one, two or twenty recipes.

Conclusions

So far, we haven't used this in anger yet, but as an approach I really like it. It uses a combination of Google Apps and doesn't get too fancy. There is a huge temptation to make this sort of system do things that maybe don't need doing.

Keeping it simple has been the main design ethic.

Most of the things we thought we'd need in terms of functionalities have turned out to already exist ( such as "Named and Protected Ranges" ) in the tools themselves, OR they can be achieved by just agreeing to work a certain way ( no software creation required ).

One of the things I like about this approach is that at the end of the day, they are all just first class Google files that one could, if need be, keep updated by hand, but it makes the whole process easier by gently easing all the things you'd need to do by hand, sending emails, adding permissions, creating files, adding calendar entries etc.

I'll let you know how we get on.