Tutorial: Interactive (Linked) Google Sheets

How I set up Google Sheets to track students in my Grid Method Class.
Visit Site

John Alesch

This tutorial walks you through how I use Google Sheets to track and monitor student progress in my Grid Method Class. The essential outline of what I do is:

  1. Create a Sheet to distrubute to each student that contains the information I want to know in each column
  2. Create a Master Sheet with the exact same columns
  3. Add a Code Script (I give this to you) to my Master Sheet to access all student copies
  4. Distrubute the Student Sheets in Google Classroom
  5. Link Student file location to our Script in Master Sheet
  6. Add a formula to import information from Student Sheet to Master Sheet
  7. Add Conditional Formatting to help visualize information easier


Step 1

Create a new Google Sheet and Title it. (This will be your student copy that each student will have their own copy of)


Add Column Headers for the information you want to collect.


Go to Tools > Data Validation and add any pre-determined choices you want students to be able to select their answers from. (This helps with Sorting and Filtering later, but it's not required) Examples Below of what I added:




Step 2

Once you have your student document set up, go to File > Make a Copy and copy the document. This copy will become your Master Sheet that tracks all students in one place.


Step 3

Now we have to add some code to our Master Sheet that will collect all of our students Sheets and pull them in to one place.

Go to Tools > Script Editor. Delete any code that comes pre-loaded on the screen. (You may have to give permission to Google if it asks)


Now copy the code in the box below, and paste it into the script editor. Your screen should look like the image that follows after the code below.

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var c=s.getActiveCell();
  var fldr=DriveApp.getFolderById("Replace with Folder Location");
  var files=fldr.getFiles();
  var names=[],f,str;
  while (files.hasNext()) {
    f=files.next();
    str='=hyperlink("' + f.getUrl() + '","' + f.getName() + '")';
    names.push([str]);
  }
  s.getRange(c.getRow(),c.getColumn(),names.length).setFormulas(names);
}


function linkURL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  var formulas = range.getFormulas();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      var url = formulas[i][j].match(/=hyperlink\("([^"]+)"/i);
      row.push(url ? url[1] : '');
    }
    output.push(row);
  }
  return output
}

Click the save (disk) icon and give your code file a name. (The name does not really matter)


Step 4

Now go to Google Classroom and Create a new assignment for the class you want to distrubute the Sheet to.


Attach the Student Sheet that we created in Step 1.


Be sure to select "Make a Copy for each Student". This is very important!


Step 5

Next we need to find where Google Classroom stored the Student Sheet that it assigned to each student. To do so, go to your Google Drive Folder and find the "Classroom" folder. This is a folder that Google Classroom creates to store all files distributed and collected through classroom.


Now find the folder that is named after what you titled your assignment in Google Classroom to distrubute the Student Sheets.


Open the folder and look at the address bar. We need to copy the long text/numerical id that is located at the end of the URL. Be sure to copy only the text/numerical id that comes after the final slash. It is the part of the URL that is highlighted in blue in the image below.


With the ID copied, navigate back to your Script in your Master Sheet and replace the words "Replace with Folder Location" (highlighted and in red below) with the folder ID that we just copied. Be sure to keep the ID inside of the quotation marks.


Your Script should look similar to the image below.


Open your Master Sheet and insert 2 column headings titled "Get Link" and "Get Student" Be sure to make these column past any of the columns you created for the students as they will get filled in with student information.

Once you have added the headings make your active cell (the one you are clicked in) the first open cell under the "Get Student" heading. Your screen should look similar to the image below.


Now for the magic of our code. Go to your Code Script and if you haven't done so, click the save (disk) icon. Then make sure "myFunction" is selected with the drop down arrow just to the right of the save icon. Then click the run (triangle/play) icon.


You will likely get a pop up to authorize permissions. If you don't you probably have something blocking pop ups and you will need to allow them for this site. Follow the prompts to allow all the permissions.


Once your code has run, go back to the Google Sheet and you should see the names of all the students in the class and their sheet that you assigned them, each in their own row!

Special Note - while I showed this step at this point in the tutorial, I strongly reccomend having all your students open their copy of the Sheet first. Google Classroom does not create all student copies until the student goes to view the assignment. That leaves the chance you won't see every student. If this happens, just run the code like we did before after all students have filled in their form for the first time.


Part 2 of our code is to extract just the URL of the student copy out of what our first code imported. The good news is we can do this directly from our Sheet. Click in the first open cell under "Get Link" like in the image below.


Now type in the formula

=linkURL(K2)

If your "Get Student" column is not in column K like my example be sure to use the correct column letter


You should see the URL appear in the cell. Use the copy formula feature to copy the formula to all of the other cells for each student file.


Step 6

Now we just have to use the link we extracted to import the information from the students sheet into ours. To do so type the following formula into the first empty cell in Column A

=IMPORTRANGE(CONCATENATE(CHAR(34),J2,CHAR(34)),"Sheet1!A2:H2")

Make sure what I have as "J2" represents your "Get Link" Column, and the "Sheet1!A2:H2" should reference the starting column (A) and the ending column (H) of the information you want to sync from the students sheet.


You will see an error code #REF! that says you need to connect these sheets. Click the blue Allow Access button.


Once the student fills in their information on their sheet, it will automatically update and display on your master sheet as well!

The image below is the students copy. They have filled in the information and made choices from the lists that you gave them. The great thing is that they can only see and edit their information and progress.


Below is the Teacher view of what will display on the Master Sheet when students fill in the information. It's the exact same, and it updates immediatly!


All you need to do to get all of the other students information into your sheet is to just copy the formula down from your first entry in column A to all of the other student columns.


Step 7

The final thing I like to do is completely optional. If you want to help visualize the data a little better, consider adding conditional formatting to the Master Sheet for some of the information.


Example

Below is a screen shot of what each of my students uses on a daily basis. They update their progress on the sheet as they go. I also use it as a way for them to let me know they need my help during class so they don't have to sit with their hand up.

Then under that is the my teacher copy. I adjust column widths and freeze columns based on what I want to display on the projector to the class. The nice thing is students can only adjust their own information, not someone else's and I don't have to share student progress with the whole class if I don't want to. I also do a lot of sorting, and filtering to help determine any grouping or internventions I need.