5
\$\begingroup\$

I wrote this script to help me keep track of who has worked together and for what project or discussion in my classes.

I make a Google Sheet with the names of the students going across the first row and down the first column. Each student gets a number corresponding to their location in the spreadsheet. The value is the letter or number to represent the project or discussion and the numbers are the students who are on a team for that project.

I was particularly unsure of my use of getDataRange() because it seemed like that might not really be necessary since I'm just trying to get a cell. I just didn't see a more efficient way to do that. In my function for setting the backgrounds of the cells to white, I tried using getRange('upperleft:lowerright'), but I wasn't sure if that was really any better (and could possibly introduce errors if I tried to reuse the code for similar purposes in a larger class since I might forget to change it). The updateTeams() code is just where I enter the teams to see how that balances things out since I am trying to have students pair up with as many people as possible in the first few discussions or projects. It's there so I can try out different arrangements without having to remember much about what I tried before. If there is a yellow square at the intersection of two names, it means they have worked together before and the letters or numbers tell on what they worked together.

function fillInTeam(value, numbers){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var first = 1;
  for (var i = 0; i< numbers.length; i++) {
    var theNum1 = numbers[i];
    for (var z = first; z < numbers.length; z++) {
      var y = numbers[z];
      var cell = spreadsheet.getDataRange().getCell(theNum1, y).setBackground('yellow');
      cell.setValue(cell.getValue() + value);
    }
    first++;
  }  
}

function updateTeams(){
  clearBackgrounds();
  fillInTeam('s', [15, 14]);
  fillInTeam('s', [13, 12]);
  fillInTeam('s', [11, 10]);
  fillInTeam('s', [9, 8]);
  fillInTeam('s', [7, 6]);
  fillInTeam('s', [5, 4]);
  fillInTeam('s', [3, 2]);
  fillInTeam('h', [14, 13]);
  fillInTeam('h', [12, 11]);
  fillInTeam('h', [10, 9]);
  fillInTeam('h', [8, 7]);
  fillInTeam('h', [6, 5]);
  fillInTeam('h', [4, 3]);
  fillInTeam('h', [15, 2]);
  fillInTeam('j', [15, 13, 11, 9]);
  fillInTeam('j', [14, 12, 10, 8]);
  fillInTeam('j', [7, 5, 3]);
  fillInTeam('j', [6, 4, 2]);

}

function clearBackgrounds() {
  SpreadsheetApp.getActiveSpreadsheet().getDataRange().setBackground('white');
  SpreadsheetApp.getActiveSpreadsheet().getRange('B2:O15').setValue('');
}
\$\endgroup\$
4
\$\begingroup\$
  • I like your argument names for fillInTeam. I think they were good choices.
  • You declare the variable first and only use it here.
for (var z = first; z < numbers.length; z++) {

You can just set z = i + 1 instead.

for (var z = i + 1; z < numbers.length; z++) {
  • Speaking of, use j instead of z. Loop counters traditionally start with i and work their way up the alphabet as nesting goes deeper.
  • theNum1 and y are both absolutely atrocious names. Find something better and more meaningful for them, or better yet, just get rid of them entirely. Just use i and j to access the array elements directly.

    function fillInTeam(value, numbers){
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      for (var i = 0; i< numbers.length; i++) {
        for (var j = i + 1; j < numbers.length; j++) {
          var cell = spreadsheet.getDataRange().getCell(numbers[i], numbers[j]).setBackground('yellow');
          cell.setValue(cell.getValue() + value);
        }
      }  
    }
    
  • getDataRange().getCell() was the right choice here. getRange() is your other option, but it only takes A1 notation. That would have been less flexible in the long run.

  • Getting the DataRange inside of the loop is a bit inefficient. Instead of declaring spreadsheet as a variable, declare a dataRange variable and use it to access the individual cells.

    function fillInTeam(value, numbers){
      var dataRange = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
      for (var i = 0; i< numbers.length; i++) {
        for (var j = i + 1; j < numbers.length; j++) {
          var cell = dataRange.getCell(numbers[i], numbers[j]).setBackground('yellow');
          cell.setValue(cell.getValue() + value);
        }
      }   
    }
    
  • One last transformation. setBackground() returns a cell, so you can chain the calls together like this. (Note that I like chaining, but I might have taken it too far and made it less clear by getting rid of the cell variable.)

    function fillInTeam(value, numbers){
      var dataRange = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
      for (var i = 0; i< numbers.length; i++) {
        for (var j = i + 1; j < numbers.length; j++) {
          dataRange.getCell(numbers[i], numbers[j])
            .setBackground('yellow')
            .setValue(cell.getValue() + value);
        }
      }  
    }
    

updateTeams() has a definite smell to it, but I suspect that's because you're manually changing those prior to each run. I'm not sure what to do about it though.


clearBackgrounds() is a little clunky. It could definitely benefit from a spreadsheet variable like you have in fillInTeam().

I also don't like this.

.getRange('B2:O15').setValue('');

What happens if you add columns to your spreadsheet beyond O? You'll have to update your code to handle it. I'm thinking you can use getLastRow and offset to do this instead. Something like this untested code here.

var dataRange = spreadsheet.getDataRange();
dataRange.offset(1,0,dataRange.getLastRow() - 1, dataRange.getLastColumn()).setValue('');

I'm admittedly unhappy with how far it scrolls, but it's dynamic at least.

\$\endgroup\$
  • \$\begingroup\$ Looking at this through someone else's eyes has been really helpful. I seem to have gotten mired down in how I was thinking about the problem and chosen poorly for my variable names. Your ideas definitely clean things up. Why do you think we need to use getRange() or getDataRange() at all in order to get at a cell? I could not think of a reason and thought maybe someone else had. Is getDataRange() only looking at cells that have values entered in them? Thanks! \$\endgroup\$ – PrairieProf Aug 30 '14 at 2:06
  • \$\begingroup\$ Ya know, that's a good point. I didn't think to check to see if getCell() is available directly from the spreadsheet. You should definitely try. \$\endgroup\$ – RubberDuck Aug 30 '14 at 2:52
  • \$\begingroup\$ I tried, but I didn't see anything besides getActiveCell(), but I have to admit I don't know what a blob is, and that seems to be something I could get if I wanted. \$\endgroup\$ – PrairieProf Aug 30 '14 at 2:57
  • \$\begingroup\$ It's just how you get to the cells. See my updated answer for a way to make it a little cleaner yet. \$\endgroup\$ – RubberDuck Aug 30 '14 at 11:51
  • 1
    \$\begingroup\$ getDataRange returns the used ranged, so it looks for the last row and column that has data in it and returns that range. For example, if there's data in A30 and D1, it will return the range of A1:D30. Glad I could help. \$\endgroup\$ – RubberDuck Aug 30 '14 at 18:34
1
\$\begingroup\$

To summarize:

  • I pulled dataRange out of the loop and assigned it a name so that I would not need to call it multiple times
  • I chained function calls where possible for cell and for data range
  • I removed some variables that I didn't use very much or didn't need (and that were poorly named, which just made everything worse)
  • I used offset rather than specifying the range of cells involved in the function for clearing the old teams
  • I renamed the input values (this was in spite of praise from @RubberDuck) to better reflect what those input values for fillInTeams actually mean to me
  • I added a sorting method to make sure the numbers provided as input for the teams of students in fillInTeams would be sorted in descending order. This makes the graph more useful since there is only one intersection where two people will be indicated as having worked together.

I left the code for updateTeams alone because I don't have a better way of doing that part of things at this point. I did sort the values to take some of the burden off of the user of the script. Some future improvements to consider might be making sure all students are actually placed in a group or team for each activity or being able to generate lists of teams with student names attached for each activity.

function fillInTeam(activity, students){
  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
  students.sort(function(a, b){return b-a});
  for (var i = 0; i< students.length; i++) {
    for (var j = i + 1; j < students.length; j++) {
      var cell = dataRange.getCell(students[i], students[j]);
      cell
        .setBackground('yellow')
        .setValue(cell.getValue() + activity);
    }
  }  
}

function clearOldTeams() {
  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
  dataRange
    .setBackground('white')
    .offset(1,1,dataRange.getLastRow() - 1, dataRange.getLastColumn()).setValue('');
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.