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
In MS Excel try this:
=A1+TIME(2,0,0)
Same with Google:
=A1+Time(3,0,0)
Adds 3 'Hours', 0 'Minutes', 0 'Seconds' to the date/time value of A1
Additionally if only you use the main sheet you can go into the sheet settings and change it's timezone accordingly.
Best Answer
Assuming the first row is used for column headers, you can enter
into cell A2, and get the list of dates with each repeated 10 times. This list will fill the entire first column; if more rows are added later, they will be automatically filled as well.
A formula can't add new rows to a spreadsheet (for that one would need a script), but if someone is manually entering sales data, they may as well add rows when needed.