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?


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);


  listBox1.setItemSelected(0, true);

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

  var button = app.createButton('Add score').setId('addButton').setVisible(false)

  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'));


  // show GUI
  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()) {
          ScriptProperties.setProperties({'rowid': i, 'colid': 3});
      case 'Away':
        if(range[i][0] == lbValue2.toString()) {
          ScriptProperties.setProperties({'rowid': i, 'colid': 2});

  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('tBox').setText(e.parameter.listBox2 + ' have a total of ' + 
    total + ' points (' + e.parameter.listBox1 + ')').setVisible(true);

  return app;

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

  return app;

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

  • 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! 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. Nov 22 '12 at 9:10
  • @ArcaneGamer92 I've created a UI for you to input data to the scores. Are you interested? Dec 25 '12 at 22:13

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.