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