×

[PR]この広告は3ヶ月以上更新がないため表示されています。
ホームページを更新後24時間以内に表示されなくなります。


Googleを追いかけろ!

.

チュートリアル 中級者 2-3: トーナメント表の作成

  • JavaScriptオブジェクトを利用してスプレッドシート内のデータを読み込み、 バスケットボールのトーナメント表を作成します。 作業はおよそ15分で完成します。
  • 配列やオブジェクトなどの基礎知識が必要となります。
  • ボタンを付け加えてそれをシートに割り付けるスクリプトについても説明します。
  • ここでは下の8つの関数を使います。

* Spreadsheet.getRangeByName() * Sheet.getMaxRows() * Range.getRowIndex() * Range.offset() * Browser.msgBox() * Sheet.clear()
* Range.setValue() * Range.setBackgroundColor()

§1 スプレッドシートの作成とセッティング

§2 コードの貼り付け

§3 コードの異なるセクションの理解

§4 コードを実行させるためのボタンの作成

§5 コードの実行と結果の確認

チュートリアル 中級者/目次 参照ページ(Goolge)


WWW を検索 Googleを追いかけろ! を検索


§1スプレッドシートのセッティングとサンプルコード

  1. 先ずdocs.google.comへ行って新規のスプレッドシートを開きます。
  2. My Brackets Testの名前で保存します。
  3. シートが2枚になるようにシートを追加します。シートには一番目が Players で二番目を Bracket という名前にしてください。
  4. 一番目の Players

    • A1 のセルに太字の文字列で Players/Teams:と入力します。
    • A2 のセルを選択しメニューから FirstPlayer という名前範囲を定義します。ここまでできたらもう一度メニューに戻って FirstPlayer を選択したときにA2セルを指しているか確かめましょう。 この箇所が完全でないとスクリプトは作動しません。
    • A2 からA9 までのセルに "Goofy", "Daffy", "Mickey", "Minnie", "Donald", "Daisy", "Pluto", "Fudgy"の人名を入れてください。
ここまででスプレッドシートは下のようになっています。特にシート下の名前を確認してください。
  1. 最後に"Bracket"のBからNまでのカラム(たて列)幅を一列ごとに細くします。(下図を参照) この幅は後にスプレッドシートの機能で修正が可能です。

§2コードの貼り付け

  1. 【Script Editor】を開きます。
  2. 下のコードを貼り付けたら BracketTest という名前で保存してください。スクリプトが呼び出せればどんな名前でも構いません。
// This script works with the Brackets Test spreadsheet to create a tournament bracket
// given a list of players or teams.

var RANGE_PLAYER1 = 'FirstPlayer';
var SHEET_PLAYERS = 'Players';
var SHEET_BRACKET = 'Bracket';

// This method creates the brackets based on the data provided on the players
function createBracket() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rangePlayers = ss.getRangeByName(RANGE_PLAYER1);
  var sheetControl = ss.getSheetByName(SHEET_PLAYERS);
  var sheetResults = ss.getSheetByName(SHEET_BRACKET);

  // Get the players from column A.  We assume the entire column is filled here.
  rangePlayers = rangePlayers.offset(0, 0, sheetControl.getMaxRows() -
      rangePlayers.getRowIndex() + 1, 1);
  var players = rangePlayers.getValues();

  // Now figure out how many players there are(ie don't count the empty cells)
  var numPlayers = 0;
  for (var i = 0; i < players.length; i++) {
    if (!players[i][0] || players[i][0].length == 0) {
      break;
    }
    numPlayers++;
  }
  players = players.slice(0, numPlayers);

  // Provide some error checking in case there are too many or too few players/teams.
  if (numPlayers > 64) {
    Browser.msgBox("Sorry, currently this script can only create brackets for 64 or fewer players.");
    return; // Early exit
  }

  if (numPlayers < 3) {
    Browser.msgBox("Sorry, you must have at least 3 players.");
    return; // Early exit
  }

  // First clear the results sheet and all formatting
  sheetResults.clear();

  var upperPower = Math.ceil(Math.log(numPlayers) / Math.log(2));

  // Find out what is the number that is a power of 2 and lower than numPlayers.
  var countNodesUpperBound = Math.pow(2, upperPower);

  // Find out what is the number that is a power of 2 and higher than numPlayers.
  var countNodesLowerBound = countNodesUpperBound / 2;

  // This is the number of nodes that will not show in the 1st level.
  var countNodesHidden = numPlayers - countNodesLowerBound;

  // Enter the players for the 1st round
  var currentPlayer = 0;
  for (var i = 0; i < countNodesLowerBound; i++) {
    if (i < countNodesHidden) {
      // Must be on the first level
      var rng = sheetResults.getRange(i * 4 + 1, 1);
      setBracketItem_(rng, players);
      setBracketItem_(rng.offset(2, 0, 1, 1), players);
      setConnector_(rng.offset(0, 1, 3, 1));
      setBracketItem_(rng.offset(1, 2, 1, 1));
    } else {
      // This player gets a bye
      setBracketItem_(sheetResults.getRange(i * 4 + 2, 3), players);
    }
  }

  // Now fill in the rest of the bracket
  upperPower--;
  for (var i = 0; i < upperPower; i++) {
    var pow1 = Math.pow(2, i + 1);
    var pow2 = Math.pow(2, i + 2);
    var pow3 = Math.pow(2, i + 3);
    for (var j = 0; j < Math.pow(2, upperPower - i - 1); j++) {
      setBracketItem_(sheetResults.getRange((j * pow3) + pow2, i * 2 + 5));
      setConnector_(sheetResults.getRange((j * pow3) + pow1, i * 2 + 4, pow2 + 1, 1));
    }
  }
}

// Sets the value of an item in the bracket and the color.
function setBracketItem_(rng, players) {
  if (players) {
    var rand = Math.ceil(Math.random() * players.length);
    rng.setValue(players.splice(rand - 1, 1)[0][0]);
  }
  rng.setBackgroundColor('yellow');
}

// Sets the color for connector cells.
function setConnector_(rng) {
  rng.setBackgroundColor('green');
}

§3 コードの異なるセクションの理解

  1. エディターの一行目は幾つかのコンスタンツを定義しています。
  2. createBracket 関数が各シートやFirstPlayer 名前付範囲を指し示している幾つかの変数を例示している数行が続きます。 名前付範囲を参照する【Spreadsheet.getRangeByName()】関数の使い方についてを確認してください。
  ...
  var rangePlayers = ss.getRangeByName(RANGE_PLAYER1);
  ...
  1. 下のコードはrangePlayersA2 から始まって A100 までのすべてのカラム(たて列)最初のセルをポイントしています。 そして、それらのセル内の players 名の配列の値をセットします。

    シートの行数が最大いくつになるのかを知るには Sheet.getMaxRows()関数を使います。 また、A2 セルの行インデックスを得るにはRange.getRowIndex()関数を用います。 A2 セルから同カラムの最大行までの範囲を選択する方法は Range.offset()関数が受け持ちます。

 ...
  // Get the players from column A.  We assume the entire column is filled here.
  rangePlayers = rangePlayers.offset(0, 0, sheetControl.getMaxRows() -
      rangePlayers.getRowIndex() + 1, 1);
  var players = rangePlayers.getValues();
  ...
  1. 次は rangePlayers 内をループして空のセルに達するまで何人のプレーヤーがいるかを数え上げます。 Javascriptの splice method を利用して実際の選手すべての配列を作ります。(IEの場合は最下段の空白セルも含む)
 ...
  // Now figure out how many players there are(ie don't count the empty cells)
  var numPlayers = 0;
  for (var i = 0; i < players.length; i++) {
    if (!players[i][0] || players[i][0].length == 0) {
      break;
    }
    numPlayers++;
  }
  players = players.slice(0, numPlayers);
  ...

 for (var i = 0; i < departments.length; ++i) {
    var sheet = ss.getSheetByName(departments[i]) || 
      ss.insertSheet(departments[i], ss.getSheets().length);
    sheet.clear();
    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
    headersRange.setValues([columnNames]);
    headersRange.setBackgroundColor(headerBackgroundColor);
    setRowsData(sheet, dataByDepartment[departments[i]]);
  }
  1. 次のセクションでエラーチェックの実現と選手があまりにも多かったり少なすぎやしないかの警告を出すためにBrowser.msgBox()関数 を利用します。 これはトーナメント表をクリヤーにするために使うSheet.clear()関数を使う上でも有益です。
...
  if (numPlayers < 3) {
    Browser.msgBox("Sorry, you must have at least 3 players.");
    return; // Early exit
  }

  // First clear the results sheet and all formatting
  sheetResults.clear();
  ...
  1. 最後のセクションです。どの選手がどこの位置にいて、それらをどのように結びつけるかを描く面白いコードです。 選手がどこにくるかは選手の合計人数次第なのです。もし選手数が2番手でれなければ変則の表を作ります。あとでスクリプトを眺め回して 2番手の選手を探して、選手数を増やしてみてください。

    ここを上手くやり抜くには Range.setValue()関数と 下に挙げるRange.setBackgroundColor()関数を利用することが最大のポイントです。 Range.setValue() がセルに値を設定し、Range.setBackgroundColor() が色付けをします。

§4 コードを実行させるためのボタンの作成

スクリプトを実行するためのボタンの作成と取り付けです。
  1. 2番目のシート(IEは"Bracket"シート)を開きメニューボタンの挿入で"Insert-->Drawing..." を選択します。
  2. Insert Drawing のダイアログがポップアップするので Insert shape アイコンを使って新規の図形を描きます。ここでは傾斜付き四角形を選んでいます。
  3. 四角形の上をダブルクリップして挿入します。Create Bracket の文字を入れましょう。
  4. 大きさを整えてダイアログボックスを閉じればおしまいです。確かにシートにボタンが追加されたことを確かめたらGカラム辺りまでドラッグして移動させましょう。
  1. ボタンをクリックすると Drawing メニューが出てくることに気がつきます。Assign macro... という表示がありますね。これをクリックしてこのスクリプトのメイン関数である createBracketと記入します。 このボタンは呼び出されればいつでも createMacro を実行します。図形の上でShiftControl キーを押しながらクリックすれば図形の編集が可能です。

§5 コードの実行と結果の確認

まだプログラムを実行していないなら、残された作業はボタンをおすことけです。ミスなければこのようになっていませんか?
一番目のシートで選手を追加したり削除したりしてから、もう一度ボタンを押してみてください。トーナメント表もたしかに修正されていますね。
これでスクリプトを実行するためのボタンをシートに追加することに成功しました。



    * チュートリアル 中級者/目次
    1. スプレッドシートの読み取り - JavaScript オブジェクトを利用してシート内のデータ構造を読み取る
    2. スプレッドシートへの書き込み - シートに構築されたデータを読み取り他のシートへ移植
    3. トーナメント表の作成 - バスケットのトーナメント表をスプレッドシートで作成
    4. Google Site の作成 - Gmail や Calendar からデータを収集してサッカーチーム用のサイトを作成
    5. Docs Listとの連携を図る - Docs List上のファイル検索やCSVファイルのインポート(エクスポート)
    6. Google Sites にApps Script を埋め込む - Google SitesにGoogle Apps Scriptを実装する
    7. デバッガーの利用 - YouTubeのVideoによる Tutorial
    8. Google Sites に Google DocList を埋め込む - ファイルキャビネットとリストページを利用してフォルダーを作成する
    9. 読書リストからスプレッドシートを作成する - 未読URLリストからタスクアイテムを作成するための同期関数を作成するGoogle APIの統合
    10. Gmail受信トレイの統計レポート - Gmail内の毎月の送受信とそのトップ5の統計情報
    -- チュートリアル 中級者 2-3.トーナメント表の作成 : end -- 2011/10/30