Google Sheets Data Flow

This is a pretty specific thing but the concepts ought to be broadly applicable and interesting for the 3 to 5 people who will end up reading this. It’s a fairly amusing blend of less standard Google Functions and a bit of Google Script to do something fairly decent that had been quite a bit of hassle to do previously.

We have Social Work students who are assigned to various supervisor/liaison people. There are a lot of students. We wanted students to be able to submit a form to Google Drive and we’d keep track of all this and show only the relevant data to the various supervisors.

Files from Form

Setting up a form that requires you to be logged in and accepts files is now very easy in Google Forms. It also remains easy to log that information to a spreadsheet associated with the form.

Merging the Data

The student email address became the unique ID that would allow us to tie the form submission to the list of students and their programs, liaisons etc. Now we needed a formula to link these two sheets via email. I started with =VLOOKUP but that would have required the student email to be the leftmost column in the data and that would be awkward for other things. After some banging around and general bad attitude on my part I found =MATCH. MATCH doesn’t care about the order and returns the row where the match occurs. That formula =MATCH(H2,’Form Responses 1′!D:D,0) let me build a few others to pull in what I wanted like this =INDIRECT(“‘Form Responses 1’!A”&I2). Note that to use other cell values as variables in other cell functions you need to use =INDIRECT. This ended up getting me the time of submission for the file and a link to the file.

Creating the Individual Views

Next up I needed to create views for 24 different people who were associated with various students. To get this I used the =UNIQUE function on the column holding their names. I didn’t want to do this by hand because I don’t like to do boring things. I also needed to figure out the function that would pull in the correct data from the combined sheet and prevent people from accidentally messing up that function.

The formula ended up being a combination of =IMPORTRANGE and =QUERY. The following function did the trick.

function makeSupervisorFormula(sup){
  var supervisor = "'"+sup+"'";
  var formula = '=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/YOUR_FILE_ID_HERE/","student list!A:K"),"SELECT * WHERE Col1=' + supervisor +'")';
  return formula;
}

Now I needed to create a spreadsheet for each person, name it, set cell A1 to be that function, protect it, and then put all the spreadsheets in the same folder.

function makeSupervisorSheet(sup){
  var ssNew = SpreadsheetApp.create(sup);//make new ss
  var id = ssNew.getId();
  var sheet = ssNew.getSheets()[0];//get first and only sheet

  var cell = sheet.getRange("A1");//get cell A1
  var formula = makeSupervisorFormula(sup);
  cell.setFormula(formula);//set our formula via the function above
  var protection = cell.protect().setDescription('Sorry this links elsewhere.');//protect this with the emails for users defined below
  protection.addEditor('foo@vcu.edu');
  protection.addEditor('bar@vcu.edu');

  var file = DriveApp.getFileById(id);//put it all in the same folder
  var folder = DriveApp.getFolderById('YOUR_FOLDER_ID_HERE');
  folder.addFile(file);
}

Now we just needed to run all this through an array containing the names of the supervisor people.

function allSups(){
  var sups = ['foo','bar','buzz'];//your supervisors here . . .   
 sups.forEach(function(sup){
  makeSupervisorSheet(sup);//do that work
});
}

And presto, a whole bunch of files that do what we want. The one hassle is that it seems you have to approve the =IMPORTRANGE function by hand from the created sheet.