Skip to main content

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())
  
}



Comments

Popular posts from this blog

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 necessaryThe 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 si…

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 cod…

Getting CSV data into Google Spreadsheets Automatically

Today I was attempting to get CSV data from Estates' Alarm System into Google Docs as a spreadsheet. There were two ways to try and achieve this...


Create an AppScript in Google that pulled a .CSV file from a web serverWrite a (python) script on the local machine that pushed the data into Google Spreadsheet by using the API. The Google AppScript Way As you know, my JavaScript ain't great, but it initially looked like it was going to work... Some code like this below and using the Array to CSV functions from here, looked promising.



function encode_utf8( s ){
//This is the code that "I think" turns the UTF16 LE into standard stuff....
return unescape( encodeURIComponent( s ));
}

function get_csv(){
var url ='http://www-users.york.ac.uk/~admn812/alarms.csv.Active BA Alarms.csv';// Change this to the URL of your file
var response = UrlFetchApp.fetch(url);
// If there's an error in the response code, maybe tell someone
//MailApp.sendEmail("s.brown@york.ac.uk&qu…