Wednesday, 20 November 2013

Data-driven Visualisation Tools - A Resource List





A visualisation can be everything from a complicated multi-dimensional data set that incorporates time and geographic information, to a couple of blobs that represent and idea.The Periodic Table of Visualisation may help you broaden your visualisation undertsanding and maybe reveal the difference between a Sankey Diagram and an Hyperbolic Tree or reveal the layout of a Radar Tree and a Mindmap.

The Information is Beautiful site lets you browse lots of different visualisations and visualisation styles. I'm particularly drawn to those visualisations that aren't complicated but convey a simple idea well, like the one below.



The Basics



For most simple visualisation needs, you really only need something simple. The tools in Google Forms ( to create surveys ) and Google Spreadsheets ( to store and manipulate your data ) and Google Charts ( to display your data in interesting ways ) are enough for most people.

As well as bar, column and line charts, Google Spreadsheets can display the more esoteric Tree maps, Trendlines, Maps and Bubble charts once you have your data organised into the correct format.

Other Visualisation Tools

You may end using a variety of tools. Go and explore these other tools to get a feel for what's possible.

Tableau is easy to use and let you explore your data in interesting ways. This video shows how to view a public data set of graffiti in New York within an interactive Tableau visualisation and is worth watching to get a feel for what is possible. Go watch this.

Infogr.am is a simple tool for creating data visualisations.

There are lots of visualisation types and visualisation tools at IBM's Many Eyes ( requires the latest Java which for me only works with Firefox ) and is worth exploring to discover what is possible.

If your dataset is large, or needs to integrate with a large dataset, then Google Fusion Tables can import Google Spreadsheets and show data in a number of visualisations. For example;


Google Fusion tables are a fantastic tool if you simply have a large dataset or you want to integrate the data you have a collected with a large dataset. Here is a quick tutorial to introduce you to how they can be used.

The Guardian has a guide to the free tools you can use for data journalism.

For those comfortable with Javascript and HTML you can create your own live and interactive visualisations that might have completely new approaches to presenting data. For example, take a look at these two interactive visualisations built using Javascript, the UK Government Daily spend and UK Government departmental spending.

Here are some examples of Javascript libraries built for visualisation.


Public Data Sets

In many circumstances, often if collecting geographical data, there are cases when you might want to consider integrating the data you collect, with larger or more complete datasets. There are many datasets worth exploring to see if they can contribute to your visualisation.


Note: Google Refine is a geeky but hugely essential tool that helps to clean your data if it is very messy and unregular.

Visualisation and Visual Tools - A Resource List

I recently had to put together a collection of tools for "thinking visually" and thought it would be good to share it here. It is basically a collection of tools for you to try.

The starting point was finding ways to display quantitative information but we also had to cover the creation of more emotive or softer images that communicate an idea, maybe very effectively, with less reliance on the numbers.

My mind is always drawn initially back to this image, which plots Napoleon's Army's size, position, direction and even the weather into one chart.



But we also looked at images such as this one, that looks at data gathered about attitudes to the Hijab in Denmark. I find it interesting that even though I can't see what the percentages refer to in the image below, I am blown away by the idea of a headscarf pie chart. In some ways, it almost doesn't matter what the data is, and whether the areas match the percentages or not, simply the idea of presenting it like this has me hooked and willing to look deeper... to engage. 

And then there's the swathes of visualisations that look fantastic but don't really tell you anything. You can't argue with their beauty but they're almost unfathomable, like a picture of "everything ever" ( from here ). 


And of course, there are any number of other brilliant ways to present information from infographics to animations.

Tools You Can Use

There are many tools that make producing charts or other structured data ( like mind maps or timelines ) much easier. However, often all you need for a great visualisation is a generic tool and great idea. For example, there isn't yet an app to do hijab pie-charts, nor should there be. There are tools to help creating tree maps ( one of my favourite visualisation types ) but you could equally create a tree map with a simple drawing tool and calculator to work out the areas needed. 

A Graphics Editor: Pixlr


One of the first tools I recommend is Pixlr Editor. Pixlr is like a "Photoshop Lite" that runs in your browser, great for resizing and cropping and adding filters etc. http://pixlr.com/editor/. Chrome users can add it in the App Store here and Google Apps users can connect to their GDrive.

Whatever other tools you use, you will probably need to postprocess the image you create with something like Pixlr.

My only caveat about this app is that it doesn't let you do fonts bigger than 90pt but apart from that it's fantastic and always available if you have a browser to hand.

Diagrams


If you want to work collaboratively developing your ideas then Google Drawings are great. I've found them to be really useful when designing something like screen wireframes because you can invite collaborators to look at your diagrams and have them comment on the individual items in your diagram ( see above ).

And with generic tools you can also create flow charts, storyboards, regular diagrams, hierarchies and taxonomies etc. Each of the tools below have huge libraries to help with different diagram types.

I'd recommend you give one of these diagramming tools trial remembering that there's almost nothing you can't do with boxes, circles and lines.

Mindmaps and Concept Maps



Mindmaps are great ways to help your ideas to develop and grow into new places. I personally find that mindmaps are often a useful process to go through, but can be difficult to "read" for the viewer, simply because they weren't involved in the "growing process". But having said that, I use them all the time.


Other online apps include:
                

Infographics



At times my heart can sink when I see the word infographics simply because so many don't convey information at all, instead they collect together heaps of  numerical soundbites and add a picture to each one. Don't get me started. Having said that, great infographics can be hugely informative and have a very strong visual presence.

Two tools that aid the production of striking infographics are:
  • http://www.easel.ly/ ( essentially just a drawing tool )
  • http://piktochart.com/ An easy tool for creating infographics. Lots of themes. ( log in with Google ) - I'm starting to really like this one, you can add basic data and use some simple but cool looking chart types ( and no, I don't mean 3D pie charts ).
  • https://infogr.am Lets you edit basic data tables to create a variety of charts  ( see the size chart below ).

Timelines


If your data has dates in it, then it is always interesting to see it in a timeline. One of my favourite tools, dipity lets you add images, and YouTube videos to a timeline but you can also view them as a slideshow.

Tagclouds


Tagclouds take a large body of text and present the words that are used the most the biggest. Above is the content of a speech by Barack Obama as a tagcloud.
Tagclouds can often seem of dubious merit, but work really well as means of comparison. Below is a tagcloud of a speech by George Bush.


And here, an academic journal makes its content viewable as a navigable tool with additional tools to read the actual text and show word frequency in the sidebar (shown below)... incredible.


Treemaps



I think treemaps are poorly named, they are nothing like trees, but they still are one of my favourite visualisation kinds. A treemap is designed to help you understand the difference in proportion between amounts without bothering you with what the actual numbers may be.

This wonderful treemap shows the proportion of news articles on a given subject, http://newsmap.jp/

I always want to see UK Government spending shown as treemaps, simply because budgets of billions become strangely meaningless when they're that big. You can imagine why most governments don't want you to see their data in this way, because typically a person looks at it and says, "We're spending HOW MUCH on X?!".

You can make treemaps from within Google Apps or with lots of other chart tools.

Cartoon and Comics

Although your first thought with a cartoon is probably that they're childish but tools to create comics are a great way to break your idea down into a discrete sequence. Love or hate IKEA flatpack furniture, the instruction comics are very good and barely use any textual language whatsoever.


Animation

Once your idea is in the format of a comic, it's not a huge leap of faith to consider if your idea would work if it was animated.

Poster Makers

These two tools are useful when you want to print your design.... BIG!


Collaborative Visual Tools


Brainstorming



Personally, I find brainstorms tend to be fast and furious and so only a pen and paper will do, but there are times, either after a face-to-face brainstorm has finished, or when you can't get together in real life that online brainstorm tools become useful.


Feedback Via Annotation

Sometimes you just need some feedback on your image.

Moodboards


Moodboards are often used to help groups define the visual tone when working together. In the past I have collected together heaps of magazines and people have ripped out images that fit their ideas. 

Pinterest - Not Just Cupcakes and Knitting Projects

Moodboards are good for curating a collection of ideas too. Sites like Pinterest let you collect together themes of web pages ( most of these web pages tend to be very visual in nature ) for example here is a Pinterest board collecting pages about collaboration with social media.


 Pinterest is best once you've added the browser add-ons to make adding items to your boards really simple. You can also make "group boards" and share the task of collecting resources and web pages.

Here's a challenge. Imagine you were planning on creating a visual image, or visualisation with the theme of "forest". You maybe had an idea or some data that you wanted to represent with woodland feel. If you notice your first thoughts about what it might look like, or even your second thoughts after a few doodles, you might be happy with the direction of your visual design. Now go and look at a Pinterest search for "forests"... and see if your first sketchy ideas about forests have been pushed in a new direction.

Pinterest is a great site for helping with your inspiration, but if you want images that you can use, then it pays to understand Creative Commons and which images can be used, in which circumstances, without breaking copyright law. Here is a search on Flickr for Creative Commons "forest" images, look at this beautiful image I found.




Next post: Data-driven Visualisation Tools - A Resource List





Tuesday, 19 November 2013

From Survey To Google Spreadsheet To Google Document

Earlier today we were looking over the results of a survey we'd put out with Google Forms. The answers were well thought out, very long and textual and impossible to read in a spreadsheet.

As a group we want to read the responses and share our thoughts about them using the comment feature in Google Documents so I whipped up this script to move the all the data from a spreadsheet to a Google Document.


function document_from_spreadsheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var header_range = sheet.getRange(1,1, 1, sheet.getLastColumn())
  var headers = header_range.getValues()[0]
  
  var data_range = sheet.getRange(2,1, sheet.getLastRow(), sheet.getLastColumn())
  var values = data_range.getValues()
  
  
  var doc = DocumentApp.create(ss.getName() + " Exported")
  var body = doc.getBody()
  
  for (var h in headers){
    h = Number(h)
    var header_name = headers[ h ]
    var p = body.appendParagraph(header_name)
    
    p.setHeading( DocumentApp.ParagraphHeading.HEADING1 )
    
    for (i = 0; i < values.length; i++){
      var row = values[i]
      var value = row[h]
      var p = body.appendParagraph(value).setHeading( DocumentApp.ParagraphHeading.NORMAL )
      body.appendHorizontalRule()
      
    }
  body.appendPageBreak()
  }
  doc.saveAndClose()
}

function onOpen(T) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Export to Google Document", functionName: "document_from_spreadsheet"},
                     ];
  ss.addMenu("Admin", menuEntries);
}
  

There was a little cleaning up to do, to remove any choice-based or numerical data items ( we could of course paste those in as images ) but this code was all we need to start easier on the eye and brain analysis of the responses.



Friday, 15 November 2013

Blogging Archaeology Students

As part of my work one of the more fun things I get to do is to introduce groups of people to new tools and approaches. The students in the Visual Media module in Archaeology are using blogs to experiment with ways to raise the profile or extend the awareness of an exhibition or artefact.

There's everything here, from the Mitford sisters to a mummy in a museum in Bolton. There's even a blog by Edward VIII himself ( kinda ).

Like all bloggers, they need a little encouragement in the form of comments, so if you have a spare minute or two, could you add a thought or suggestion or response to one or more of these fine archaeology blogs...

http://visualmedia-archaeology2013.blogspot.co.uk/

Creating a Process Workflow with Google Spreadsheets

I thought I'd share this ongoing project I've been working on about creating workflow support for a team. I've referred to it before in a blog post called Using Spreadsheets Instead of Forms, in which I argue how using the commenting, "live saving", versioning and collaborative features of spreadsheet far exceeds what can be done with a simple Google Form (see below). This is especially so when the data you are collecting is long and complicated. ( Tip of the hat to Tim Saunders who had this idea ).



Having quickly trialled and liked bespoke workflow apps like Kissflow, and also read the documentation for bigger workflow systems like RunMyProcess, we realised that one of our first challenges was to define the mood or tone of this process. It was clear that the process we were trying to support was more consultative and discussion-based than a hard passing of numbers and approvals in a clear process flow. Our process needed to be more about "letting the right people know where they are up to" and asking for input than a mechanistic model.

As we worked we realised that the flow itself was remarkably simple and was mainly about making documents available to people for commenting and letting teams of people know what the status is.

We realised that we could break most stages of the process into simple interactions and added them to the spreadsheet as menu items, for example, "Submit initial request" or "Submit to Team B" or "Reject request" etc.

These menu items do three things:

  • Show a dialog telling the user what they were about to do
  • Sent emails to the relevant people and groups
  • Updated the project's status in a central spreadsheet
And to help us to speed up the process of development, we created a Processes spreadsheet that looks like this...


Each process gets rendered against the NamedRanges in the spreadsheet, so that {MainContact} becomes "Joanne Blogs" or whatever. Sometimes, even emails are rendered this way like {HeadOfDepartmentEmail} in the CC of the email that gets sent out.

Some special values had to be sneaked in such as {folder_link} and {link} which means that emails can contain links to the current document or GDrive folder.

Working this way has meant that fine-tuning who gets sent what is a LOT quicker, it not being hard-coded. It makes the authoring of those automatic emails which usually get sent to groups rather than people so that people can easily turn off email notification if need be. 

The menu items typically have code that look something like this...

function utc_approved( url ){
  if (is_a_member_of( "planning-utc-controllers@york.ac.uk" ) == true ){
    run_process( "Process:10", url )
    set_status("UTC approved")
  }else{
    Browser.msgBox("Not allowed", "You need to be a member of the UTC controller group to run this", Browser.Buttons.OK_CANCEL)
  }
  Browser.msgBox("UTC approved" )
}

... and the run_process() function simply shows a dialog, renders and sends out emails and sets the status of the request. Some menu items have code that moves a folder to an "Archived" folder for tidiness sake, but nothing too complicated.

I think the thing I learned from this project, once again, is that, like Booking Systems, although they always present themselves at the door as a the same thing, they are always in a clever disguise and really are something very, very different indeed. And by the time they've got through the door and taken their coat off it's already too late.



















Thursday, 14 November 2013

The Apps Script and Google Spreadsheet Room Booking System

You may remember my previous posts about attempts to create a room booking system with Apps Script. This system uses Apps Script to populate a spreadsheet with weekly sheets, that contain lots of "Book me" links ( see below ).



The "Book me" links open a very small web application that is essentially a confirmation screen with a "Book" button.

When a student books a room, the web application says "Booked" and adds the booking to a central calendar and invites the student as guest, so that it appears in their calendar.

One very important aspect of this booking system was the booking quotas that student are given. Each student can only book 3 hours a day in each room. The student is allocated a colour, not just because it looks nice, but because, as you can imagine when a large amount of students are wanting to finish their projects with a finite resource, it can get quite busy. The admin team previously has been spending HOURS policing the bookings to make sure that nobody was bending or breaking the booking rules. The student colouring system is useful for "keeping an eye" on usage of the rooms in general.

There is code to hide "past weeks" based on the day and code to allocate a colour to each user. The solution we have currently is one that is quite difficult to share simply because it is a bit complex to set up, but you may be interested in the approach which is working and despite some hacky aspects and being a little rough around the edges is incredibly simple.

The parts of this project that I like are that, although it looks a bit like a booking system, it is just a spreadsheet, and the admin can "block items" by just deleting the "Book me" links. That the web application is opened in a new tab and "disconnected" from the spreadsheet is a bit icky, but it works and students like it.

The booking system has saved hours and hours of an administrator's time who had to police the bookings every day. "You should patent this" is what they said :-)

One thing I think I've learned about making this (and other similar) booking system is that the words "booking system" should be a warning to all who hear of them. Every booking system is very subtly different, and needs different tweaks and considerations, and hacks and "by the ways" until you realise this booking system isn't subtly different at all, it's completely different. Be warned.




Emailing Someone a Fancy PDF When They Submit a Google Form

Google Forms are a great way to gather and process Application Forms, but often the data entered is very text heavy and apart from a Google Spreadsheet being a hopeless way to read text, the submitted might need a "better formatted" version of their submission.

Here's a code snippet that builds on the ideas in a previous post,
Using Google Docs To Make Google Spreadsheets Easier to Read, where we use Apps Script to make it easy to add {tags} to a document that correspond to the names of headers in a spreadsheet and then use that document as a template.

This code, gathers the student's name and email and shows how to create a rendered Google Document and then email the PDF of their data back to them when they submit a Google Form.


var folder_id = 'YOUR_FOLDER_ID_HERE'
var institution_folder = DocsList.getFolderById( folder_id )
    var personal_folder = institution_folder.createFolder(student_name) 
    
    var template_id = 'YOUR_TEMPLATE_DOC_ID' // The Application Form
    var template_doc = DocsList.getFileById(template_id)
    var new_doc_id = template_doc.makeCopy("Application: " + new_doc_title).getId()
    var doc = DocsList.getFileById(new_doc_id)  //Move to destination folder
    doc.addToFolder(personal_folder)
    
    //Open the document for content editing
    var new_doc = DocumentApp.openById( new_doc_id )
    //Render the values into the doc
    var s = ''
    for ( var key in values) {  
      var value = values[key][0]
      var tag = "{" + key + "}"
      s+=  tag + " " + values[key][0] + "\r"
      new_doc.replaceText(tag, value ) 
    }
    //Replace any unreplaced tags for tidiness
    new_doc.replaceText("\{.*?\}", "" ) 
    new_doc.saveAndClose() // Force changes
   
    var new_doc = DocumentApp.openById(new_doc_id)
    var pdf_blob = doc.getAs(MimeType.PDF)
    personal_folder.createFile(pdf_blob)
    
    //Send email to student.
    var text = new_doc_title + "\r\r\r"
    text = text +  'Your application has been received, thank you. \r'
    text = text + "\r\r\r"
    
    MailApp.sendEmail(student_email, 'Application Received: ' + new_doc_title , text, {name: 'Automatic Script',
                      noReply:true,
                      attachments: [pdf_blob],
                      }) 
    doc.setTrashed(true)//Optional...you might choose to keep it.

The above code would be added somewhere in your onFormSubmit() function.

Creating A Shared Logging System

This is an approach we've used and re-used a number of times. Imagine you want a group of people to share some information using a Google Form. But although you don't really want to share the spreadsheet of the collected data, you do want people to use a subset of it.

In this example, we will create a "Research Logger". Here's the first form, go fill it in.

The Confirmation Page of the Form has a link to a web application made earlier, like this.


The web application uses a Table Chart visualisation to show a subset of the data but has really nice filters so that you can drill down on the information. It looks like this.





The code to display a table like this is...

 function doGet(e) {    
  var spreadsheet_id = 'YOUR_SPREADSHEET_ID'
  var ss = SpreadsheetApp.openById(spreadsheet_id)
  var sheet = ss.getSheetByName("Form Responses")
  var last_row = sheet.getLastRow()
  var last_column = sheet.getLastColumn()
  var range = sheet.getRange(2, 1, last_row-1, last_column)
  var data = range.getValues( )
  
  var dataTable = Charts.newDataTable()      
        .addColumn(Charts.ColumnType.STRING, "Added by")     
        .addColumn(Charts.ColumnType.STRING, "Department")      
        .addColumn(Charts.ColumnType.STRING, "Researcher")  
        .addColumn(Charts.ColumnType.STRING, "Funder")  
        .addColumn(Charts.ColumnType.STRING, "Name of Call") 
        .addColumn(Charts.ColumnType.STRING, "URL") 
  
  for ( r in data){
    var row = data[r]

    var username = row[1]
    var department = row[2]
    var researcher_name = row[3]
    var researcher_email = row[4]
    var tags = row[5]
    var notes = row[6]
    var funder = row[7]
    var name_of_call = row[8]
    var deadline = row[9]
    var research_title = row[10]
    var folder_url = row[21]
    
    
    if ( researcher_email != '' & typeof researcher_email != 'undefined' ){
      researcher_name = '' + researcher_name + '' 
    }
    
    folder_link = 'files'
    dataTable.addRow( [username, department, researcher_name, funder, name_of_call, folder_link ])    
  }
     
  dataTable.build( );
  
  var chart = Charts.newTableChart()
      .setDimensions(1200, 500)
      .setDataTable(dataTable)  
      .setOption('allowHtml', true)
      
      .build();
  
  var name_of_callFilter = Charts.newStringFilter().setFilterColumnLabel("Name of Call")
        .setLabelStacking(Charts.Orientation.HORIZONTAL)
        .setLabel("Name of Call")
        .setRealtimeTrigger(true)
        .setCaseSensitive(false)
        .setMatchType(Charts.MatchType.ANY)
        .build()
  
  var departmentFilter = Charts.newCategoryFilter()
       .setFilterColumnLabel("Department")
       .setAllowMultiple(true)
       .setSortValues(true)
       .setLabelStacking(Charts.Orientation.VERTICAL)
       .setCaption('Department')
       .setSortValues(true)
       .build();
   
     var funderFilter = Charts.newCategoryFilter()
       .setFilterColumnLabel("Funder")
       .setAllowMultiple(true)
       .setSortValues(true)
       .setLabelStacking(Charts.Orientation.VERTICAL)
       .setCaption('Funder')
       .setSortValues(true)
       .build();
   
   
   var dashboard = Charts.newDashboardPanel()
       .setDataTable(dataTable)
       .bind(name_of_callFilter, chart)
       .bind(departmentFilter, chart)
       .bind(funderFilter, chart)
       .build();
   
   
   var app = UiApp.createApplication().setTitle("Research")
   var panel = app.createVerticalPanel().setSpacing(10)
   
   panel.add(name_of_callFilter).add(departmentFilter).add(funderFilter).add(chart );
   
   dashboard.add(panel)
   app.add(dashboard)

   var label = app.createLabel().setText("TOTAL: " + data.length + " research bid projects").setStyleAttribute("color", "#442233").setStyleAttribute("font-size", "18px")
   app.add( label )
   
   
   var link_to_form = app.createAnchor("Add a new Research bid", "https://LINK_TO_YOUR_FORM/viewform")
   link_to_form.setStyleAttribute("color", "blue").setStyleAttribute("font-size", "18px")
   app.add( link_to_form )
   app.setWidth(1200)
   return app
}

One added useful feature is that when someone submits the form, a GDrive folder is created for that item and they are added as an Editor to that folder. A link is then added to the table for ease of access.

The code automatically create a GDrive folder when the form is submitted is...

function onFormSubmit(e) {            
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Form Responses")
  
  var row = e.range.getRow() 
  
  var values = e.namedValues
  var department = values['Department']
  var researcher_name = values['Researcher name']
  var researcher_email = values['Researcher email']
  var name_of_call = values['Name of Call']
  var funder = values['Funder']
  
  var folder_title = department + " - " + researcher_name + " - " + funder + " - " + name_of_call
  
  var destination_folder = DriveApp.getFolderById('CHANGE_TO_YOUR_FOLDER_ID')
  var folder = destination_folder.createFolder(folder_title)
  folder.addEditor(researcher_email)
  var folder_url = folder.getUrl()
  

  
  sheet.getRange( row, 22).setValue( folder_url ) 
}


This collection of a Google Form, a Google Spreadsheet and a Web Application means that staff can easily add information and be able to easily browse the information other people have added. I think although calling a Table Chart a visualisation might be a bit grand, they are incredibly useful ways of presenting information in a navigable and filterable format.






Monday, 11 November 2013

Using Hangouts on Air To Run A Distributed Mini Conference

Sara Perry ( Archaeology ) wanted to run a mini-conference as part of her Gender and Digital Culture work using Hangouts.

The plan was to have two presentations, one from Sara in York and one from Lucy Shipley and Jim Osborne from the University of Southampton. The presentations would be screen-shared. The audio was amplified so everyone in the room could hear it ( of course ).

There'd be some general discussion ( between everyone ) time and then we'd split into teams that discussed the issues raised and add ideas and questions and solutions into a shared Google Document.

The participants, at both York and Southampton were given an iPad that was already logged into the document. Potentially, the "general public" could have "tuned in" via the YouTube stream and if anyone had have been watching it via Google+ then they would have had the opportunity to ask questions of the speakers. They used Twitter for the backchannel in this case though.


Sara ( as seen by Southampton )

A participant joining the discussion from Southampton

Lucy and Jim's presentation

The "live" shared Google Document.


The whole event went remarkably well, there was only a slight glitch when the audio broke up when someone drifted too far away from the microphones. We used regular university wifi and a macbook at our end, with the only difficulty being the fancy footwork required to get a hangout to correctly stream from a second monitor ( Tip: turn off mirroring mac folks ).

You can watch the event here and were I to say "but you had to be there" - you'd see that you really didn't. You could have watched and participated in the whole two hours no matter where you were.




Tidying Up Spreadsheet Data Gathered In A Google Form

Google Forms are a great way to make it easier to get the right data from your colleagues, but after a while your spreadsheet data can get very messy and you need to organise it a bit.

For example, Jo created a form so that people could submit requests to go on external courses that asks for all the data needed for them to be able to make a decision about it. It asks how much it costs, how much the hotels and travel will be and who will benefit from the course etc. It works really well.

But now that lots of people have submitted it, and had their course requests approved, she wanted to tidy up the spreadsheet without losing the data so that it was easy to process a small list of current requests. Funnily enough, two other people in the last two weeks have come to me with identical needs, so here's an example that works.

What it does...

All this script does is, if you set a column called "Status" to "OK", then it moves that row of data to a hidden sheet. The sheets are organised by which department they come from. So, for example, if in the form you have selected "Senior Management" as your department, then the script looks to see if there is a sheet called "Dept: Senior Management" and if there is - it uses it, and if there isn't it creates one. Then it moves the data to that sheet and hides the sheet.

It's very simple but an extremely handy way to make working with current course requests so much simpler.

To use it...

Either go to my example spreadsheet here and File > Make a copy and give it a trial on your version. You will of course need to fill in the form a few times to be able to set the Status column to "OK" and see it working.

Or you can copy-n-paste the code below into your spreadsheet's Script Editor, changing the values to match your department name (or whatever you want to use as your differentiator ).

Tip: You may need to run the Script from the Script Editor to get it to Authorise to begin with.



function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var sheet_name = sheet.getName()
  var range = e.range
  
  if ( sheet_name == 'Form Responses'){ // We're on the right sheet(s)
    var row = range.getRow() // which row is being edited?
    var number_of_columns = range.getLastColumn() // how wide is the sheet?
    var source_range =  sheet.getRange(row, 1, 1, number_of_columns) // get the row
    var data = source_range.getValues()[0] // get the row's values
    Logger.log( "data: " + data )
    var status = data[8]    // This is the cell that controls it all. The 9th item
    Logger.log ( "Status: " + status )
    
    if ( status.toLowerCase() == "ok"){
      //Move the row to a "Dept: name"
      var dept_name =  data[3] // This is the column that controls which sheet it will go to/make  
      var destination_sheet = get_or_make_a_sheet( dept_name )
      
      source_range.moveTo( destination_sheet.getRange( destination_sheet.getLastRow() + 1 , 1 ) )
      destination_sheet.hideSheet() // Comment this line out if it pisses you off
      // Now delete the original row from Form Responses. Eek!
      sheet.deleteRow(row)
    }
    
  }
  ss.setActiveSheet(sheet) // Move the user back to the orgininal sheet
  
}

function get_or_make_a_sheet(name){
  // If a sheet is found called "Dept: name" then that is returned, otherwise a new one is created and the correct headers added.
  try{
    var ss = SpreadsheetApp.getActiveSpreadsheet( )
    var sheets = ss.getSheets()
    for ( var s in sheets){
      var curr_sheet = sheets[s]
      var sheet_name = curr_sheet.getName()
      
      if ( sheet_name ==  "Dept: " + name ){
        // A sheet with that name exists, here it is
        //Logger.log( "Sheet found: " + sheet_name )
        return curr_sheet
      }else{
        // Do nothing
        //Logger.log( "Sheet: " + name + " not found")
      }
    }
    //No sheet found with that name, so carry on and create a new one.
    
    var sheet = ss.getActiveSheet() // Where is it being created from? The Form Responses sheet usually.
    //Copy the header row
    var index = Number(sheets.length)  //created in the above repeat loop, maybe sheets.length would be better?
    var name = "Dept: " + name
    
    // Get main headers
    var source_range = sheet.getRange(1,1, 1, sheet.getLastColumn() )
    
    // Create a sheet but copy the headers over.
    var new_sheet = ss.insertSheet(name , index)
    var destination_header_range = new_sheet.getRange( 1,1,1, sheet.getLastColumn() ) // Headers go here
    
    // Copy the headers from main sheet
    source_range.copyTo(destination_header_range)
    
    // Make it look nice, like the headers in Form Responses
    var grid_id = source_range.getGridId()  
    source_range.copyFormatToRange(new_sheet,1,source_range.getLastColumn(),1, 1 ) 
    return new_sheet
  }catch(e){
    Logger.log( e + " " + e.lineNumber + " " + e.stack )
  }
}


function test_get_or_create_a_sheet(){
  var sheet = get_or_make_a_sheet( "Art History")
  Logger.log( sheet.getName())
  
}