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

The showPrompt function

The showPrompt() function is quite self explanatory. It uses the ui class to display a request for the name of the new tab. The rest of the code handles the various response types available to the user. Any response other than entering a name and pressing OK will terminate the creation of the new tab.

 
function showPrompt() {
  var ui = SpreadsheetApp.getUi(); 

  var result = ui.prompt(
      'Please enter name of new tab:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    return text;
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
    return "";
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.
    return "";
  }
}