Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a little basketball stats sheet, and a cell that has a total of a specific player's statistic they have accumulated throughout the season. But there is no way I know of to update this cell without tediously getting out a calculator and adding together the old total and the statistic in their most recent game to reinput the new total.

How can I make my spreadsheet automate the calculation/reinput?

share|improve this question
add comment

1 Answer

I created in November 2012 this little app. For the sake of it not being made in vain, here's the code plus a sample Google Spreadsheet. It will handle team scores only:

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "Add team stats", functionName: "teamStats"}];

  // add to menu
  ss.addMenu("Stats", menu);  
}

function teamStats() {
  var app = UiApp.createApplication().setTitle('Add team statistics').setWidth(350).setHeight(200);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getSheetByName('Schedule').getDataRange().getValues();

  var panel = app.createVerticalPanel(), grid = app.createGrid(4, 2);
  var listBox1 = app.createListBox().setId('listBox1').setName('listBox1').setWidth(150);
  var listBox2 = app.createListBox().setId('listBox2').setName('listBox2').setWidth(150);  
  var handler = app.createServerHandler('getCurrent').addCallbackElement(panel);

  listBox2.addChangeHandler(handler);
  listBox1.addChangeHandler(handler);

  listBox1.addItem('Home').addItem('Away');
  listBox1.setItemSelected(0, true);

  listBox2.addItem('Select team ...');
  for(var i=1, lenRange=range.length; i<lenRange; i++) {    
    listBox2.addItem(range[i][0]);
  }  
  listBox2.setItemSelected(0, true);

  var button = app.createButton('Add score').setId('addButton').setVisible(false)
    .addClickHandler(app.createServerHandler('addScore').addCallbackElement(panel));

  grid.setWidget(0, 0, app.createLabel('Select location'))
    .setWidget(1, 0, app.createLabel('Select team'))
    .setWidget(0, 1, listBox1)     
    .setWidget(1, 1, listBox2)
    .setWidget(2, 0, app.createLabel('Current score').setStyleAttribute('margin-left','25%'))
    .setWidget(3, 0, app.createTextBox().setId('currentScore').setName('currentScore').setEnabled(false)
      .setStyleAttribute('text-align', 'center').setWidth(150))
    .setWidget(2, 1, app.createLabel('Add new score').setStyleAttribute('margin-left','25%'))
    .setWidget(3, 1, app.createTextBox().setId('newScore').setName('newScore')
      .setStyleAttribute('text-align', 'center').setWidth(150).addChangeHandler(app.createClientHandler().forTargets(button).setVisible(true))); 

  var tBox = app.createTextBox().setId('tBox').setName('tBox').setVisible(false).setWidth(500);
  var closeButton = app.createButton('close').setId('closeButton').setVisible(false).addClickHandler(app.createServerHandler('closeUi'));

  app.add(panel.add(grid).add(button).add(tBox).add(closeButton));

  // show GUI
  ss.show(app);
  return app;  
}  

function getCurrent(e) {
  var app = UiApp.getActiveApplication();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Schedule');
  var range = sheet.getDataRange().getValues();

  var lbValue1 = e.parameter.listBox1;
  var lbValue2 = e.parameter.listBox2; 

  for(var i=1, len=range.length; i<len; i++) {
    switch(lbValue1) {
      case 'Home':
        if(range[i][0] == lbValue2.toString()) {
          app.getElementById('currentScore').setText(range[i][2]);
          ScriptProperties.setProperties({'rowid': i, 'colid': 3});
        }
        break;
      case 'Away':
        if(range[i][0] == lbValue2.toString()) {
          app.getElementById('currentScore').setText(range[i][1]);
          ScriptProperties.setProperties({'rowid': i, 'colid': 2});
        }
        break;
    }
  }

  return app;
}

function addScore(e) {
  var app = UiApp.getActiveApplication();

  if(e.parameter.listBox2 != 'Select team ...') {
    if(e.parameter.newScore != null || e.parameter.newScore != '') {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('Schedule');
      var oldScore = e.parameter.currentScore;
      var newScore = e.parameter.newScore;
      var total = Number(oldScore)+Number(newScore);
      var rowid = ScriptProperties.getProperty('rowid');
      var colid = ScriptProperties.getProperty('colid');

      sheet.getRange(parseInt(rowid)+1, parseInt(colid)).setValue(total);
    }
  }

  ss.getSheetByName("Logger").appendRow([new Date(), e.parameter.listBox2, 
    e.parameter.listBox1, oldScore]);

  app.getElementById('addButton').setVisible(false);
  app.getElementById('closeButton').setVisible(true);
  app.getElementById('tBox').setText(e.parameter.listBox2 + ' have a total of ' + 
    total + ' points (' + e.parameter.listBox1 + ')').setVisible(true);

  return app;
}

function closeUi() {
  var app = UiApp.getActiveApplication();

  app.close();
  return app;
}

See the following example to watch it in real life: Team Stats

share|improve this answer
    
I mean on the Roster/Stats sheet. But that could work there too, though it's not the most intuitive solution. I must say I never really thought about doing it that way, certainly better than my current method. Is there a formula/script/shortcut that can make it so I just highlight the cell, type a value in, and it adds/updates the total all in 1 motion? Or maybe a new row where I type the most recent game statistic(s) in and it adds/updates it to the total(s)? Appreciate the help! –  ArcaneGamer92 Nov 22 '12 at 8:59
    
Using a script is a whole new ball game.....You could also keeo track of all data per date, make a summation and reference to that summation. The script way, doesn't give insight on individual stats. –  Jacob Jan Tuinstra Nov 22 '12 at 9:10
    
@ArcaneGamer92 I've created a UI for you to input data to the scores. Are you interested? –  Jacob Jan Tuinstra Dec 25 '12 at 22:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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