Google Apps Scripts for 18xx

From BOARD18 Project WIKI
Jump to navigation Jump to search

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 Need For Author Identification

In the last few years a large number of Dream [and dream like] spread sheets have been written. There is now a developing problem in identifying who is responsible for a given sheet. IE: who owns it.
If a problem is found with a sheet then who should it be reported to?

To solve this problem, I would like anyone who submits a scripted sheet to be included in my spread sheet template folder to comply with this requirement.

The following header (or something like it) should be put at the start of the script file of all scripted sheets: 

 
/*
 * @OnlyCurrentDoc
 *
 * This sheet was created by Richard Price [or whomever] on mm/dd/yyyy
 * This sheet was last updated by xxxxx on mm/dd/yyyy [repeat each time the sheet is updated]
 *
 * Copyright (c) yyyy by xxxxxxx under the the MIT License.
 * A copy of this license can be found in the LICENSE.text file.
 */

I have created a LICENCE.txt file in the template folder.

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.

Making Usage Easier

Google has created a very strict authorization requirement for Google Apps Scripts running on Google sheets. See 18xx Dream Spreadsheet Authorization Issue for more information about this. This requirement can be made easier for players to cope with by putting an @OnlyCurrentDoc declaration at the start of the file. This stops the script from doing anything that affects any other documents, and means you get a considerably less scary message when authorizing the script.

You should add this declaration in a Javascript comment at the start of the script file. 

/**
 * @OnlyCurrentDoc
 */
Retrieved from "https://wiki.board18.org/index.php?title=Google_Apps_Scripts_for_18xx&oldid=2732"