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

The Button

The New Tab button that overlays cells A8:B9 is a simple image that is linked to the newTab function. Follow these steps to create this button:

• In the spreadsheet insert a drawing (Insert->Drawing)
• In the Drawing editor select Shape->Shapes
• A box containing 7 rows of different shapes will open
• Click on the first shape in the fourth column (the Bevil)
• Use the cursor to draw a bevil shaped like the one in the 18EU sheet
• Doubleclick on the image to add the "New Tab" text
• Close the drawing editor
• Size and position the button that now appears on the spreadsheet
• Right-click the button and click on the ... that appears on top left corner
• From the menu that now appears, select Assign script
• Type newTab
• The button should now cause a new tab to be created.