Adding a Button/Script to a VRG Generator Google Sheet

As I am planning my preparations for the new school year, I came across a recent tweet from Meg Craig:

She included very simple instructions for using a Google Sheet as "The Easiest, Most Customizable Seating Chart and VRG Generator Ever™". The acronym VRG refers to "visible random grouping", a method for setting seating charts and student groups. Her very detailed directions can be found HERE.

After setting mine up to fit my classroom layout, I got the idea of adding a button to activate the randomizing. I had limited experience with buttons and scripts from my days of working with Excel. So I started Googling.

First, I found the directions to add a BUTTON.  Once the button was added to my Google Sheet, I needed to develop a script to make the button do it's job.

Unfortunately, I am not a computer programmer. Fortunately, with some Googling, I was able to find the SCRIPT that would randomize the name cells with a click of the button. 

I made a little edit to work with my data:

function shuffleSheet() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  var range = sheet.getRange("A1:A30");

  range.setValues(shuffleArray(range.getValues()));    

}    

function shuffleArray(array) {

  var i, j, temp;

  for (i = array.length - 1; i > 0; i--) {

    j = Math.floor(Math.random() * (i + 1));

    temp = array[i];

    array[i] = array[j];

    array[j] = temp;

  }

  return array;

}

The highlighted cells need to be adjusted, depending on the location and number of names in your sheet. 

So here are the instructions:

1. Follow Meg's directions to make your VRG Google Sheet.

2. Make the BUTTON.

3. In Google Sheets, click on Tools -> Script Editor...

4. Create a new project and paste the script text posted above. Remember to adjust the highlighted cell range to match your name list on the Google Sheet.

5. Rename the Untitled Project. I made the name of the project the same as the name in the first line of the script text ("shufflesheet").

6. Save the script:

7. Go back to the Google Sheet, and click on the top right corner of the button

... and select "Assign script...".

8. Enter the name of the script that you saved in step 6.

9. Test your button!!!

Here is a LINK to my VRG Generator. A few notes...

• I made a separate sheet for each of my classes. I was not able to figure out a way to make one script that would work for all of the classes. Because each class has a different number of students, the "varrange" line from the script became an issue. For example, a range of A1:A30 for a class of 16 students would put blank cells in the seats. So each class has it's own script with a specific cell range for that class. But, if students are added/removed, I will need to update the script to make sure the cell range matches my student-name list.

• The student names are in column A. However, to clean up the sheet, I made the student names white.

Thanks Meg for your post and thanks to my #MTBoS friends for the feedback!