Google-sheets – Google Spreadsheets Calculation/Reinput Automation

automationgoogle sheets

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?

Best 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

Related Topic