Google Apps Scripts for 18xx

Introduction

To paraphrase Jonathan Coveney:
The dream spreadsheet for 18xx PBEM would:

• Cause changes in the past spreadsheet tabs to flow forward to the future tabs.
• Create a new tab with one click.

I have created such a spreadsheet for 18EU using Google Apps Script combined with the formulas documented in my WIKI article at Spreadsheet Formulas for 18xx.

The resultant spreadsheet is here. This article documents the two scripts (functions) used in this spreadsheet.

The newTab function

The newTab() function contains the bulk of the code that actually creates and initializes the new tab. This entire function is included at the end of this section.

The first thing that this function does is to call the showPrompt() function (see next section) to get the name of the new tab. It then creates the new tab, renames it to the name supplied by showPrompt and moves it to the left most spot on the tab bar. It then places the name of the previous tab in cell A13.

Finally it clears the contents of all of the detail cells and the for each loop adjusts the Pool cells to include any values in he Sold This OR cells.

 
function newTab() {
  var newTabName = showPrompt();
  if (newTabName == "") {
    return
  }
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var curTab = sheet.getActiveSheet();
  var curTabName = curTab.getName();
  var newTab = sheet.duplicateActiveSheet();
  var soldAdjust = [
    ["AL9:AL9","AL10:AL10"], ["AN9:AN9","AN10:AN10"],
    ["AP9:AP9","AP10:AP10"], ["AR9:AR9","AR10:AR10"],
    ["AT9:AT9","AT10:AT10"], ["AV9:AV9","AV10:AV10"],
    ["AX9:AX9","AX10:AX10"], ["AZ9:AZ9","AZ10:AZ10"]
  ];
  sheet.moveActiveSheet(1);
  sheet.renameActiveSheet(newTabName);
  newTab.getRange("A13:A13").setValue(curTabName);
  newTab.getRange("E20:AJ20").setValue(0);
  newTab.getRange("E22:AJ22").setValue(0);
  newTab.getRange("E24:AJ25").setValue(0);
  newTab.getRange("E28:AJ28").setValue(0);
  newTab.getRange("AK20:AZ28").setValue(0);
  for each (xa in soldAdjust) {
    soldShares = newTab.getRange(xa[0]).getValue();
    poolShares = newTab.getRange(xa[1]).getValue();
    sumShares = soldShares + poolShares;
    newTab.getRange(xa[1]).setValue(sumShares);
  }
  newTab.getRange("AK9:AZ9").setValue(0);
}