LoginSignup
4
1

More than 1 year has passed since last update.

@koushiro(Koushiro A)

WRO京都での技術支援

Last updated at Posted at 2019-07-29

WROとは

wro.png
LEGO Mindstorms (EV3やNXT)を用いて行われるロボコン。
世界中で開催されており、沢山のチームが参戦している。
私自身も4度挑戦し、2度Japan大会まで進出した。
2019年度のJapan大会は関西学院大学(兵庫県)、世界大会はハンガリーで開催される。
WRO Japan ホームページ
WRO-association

大会運営の問題点

WROの競技はポイント制で、達成できたミッションに対してポイントが与えられる。
そして全チーム得点を計算し、順位を付けていく。
しかし今年度の参加チーム数は86チームにも昇り、計算量はハンパない。(しかも走行は2回)
対してその作業が出来る人員は数人程度。とてもじゃないが時間がかかって仕方がない。
前年度も作業が遅れ参加者からクレームが入ったりもした。
なんとか楽に集計できないだろうか……という問題。

解決策

困った時のGoogleさんでなんとかしようって感じで構想を始めた。
GASは前に使ったことがあったので(参照)ある程度知識はある。
データ入力にはGoogle Formを使用。これは本来アンケートを取るために用意されているが、今回はデータ入力向けにデザインした。
Google Formの集計はデフォルトでGoogle Spread Sheetで出来る。
これでなんとか出来そうだと確信。

ぶちあたった問題

Google Spread SheetはExcel同様に、関数を用いたり出来るので、『「ミッション成功」のチームに10点加算』みたいなことは容易に可能だと考えていた。
しかし、データが入力された行は、全ての列で上書きされ、せっかくのポイント集計列が全く機能しないということが発生した。
じゃあどのように解決するか、もうGAS使うしか無いでしょ？

最終的なシステム

download.png

今回はタブレット端末を用意し、QRコード経由でGoogle Formにアクセス出来るようにした。
これはあえて誰でも簡単にアクセス出来るようにするためだ。
そして、このQRコードが記された↓の首下げ札を審判員だけに渡し、アクセス出来る人を制限した。

download-1.png
download-2.png

これでGoogle Form にアクセスし、↓のようなフォームにデータを入力する。
スクリーンショット 2019-07-29 13.03.41.png
ポイントは全て必須入力にすること。
これでデータの書き忘れを防ぐことが出来る。

入力されたデータは↓のように入力される。(赤色部分がFormからの書き込み)
スクリーンショット 2019-07-29 13.15.42.png

このシートでいうとG列からK列にミッション成功に有無が書かれている。これをGASで計算し、L列からP列の部分に実際の点数を書き込む。最後にL列からP列の得点を合算し、D列に書き込む。

あと、Q列の書き込み済み欄は、GASの計算スピードを上げるためで、計算済みのチームは無視するというロジック。(後のコード参照)

このGASでの計算処理は、GASのトリガー設定で『書き込んだ時』と設定する。

最後に点数を確認するときは、このシートにアクセスする。

以上が、今回のシステムだ。

GASの使い方

基本的なところだが、一応説明する。
まずスプレッドシートひも付きタイプのGASは、『ツール』→『スクリプトエディタ』と進み、作成する。
スクリーンショット 2019-07-29 13.29.13.png
すると↓みたいな感じのページに進む。(コードは一旦無視で)
スクリーンショット 2019-07-29 13.30.16.png
この画面の、表示と実行の真ん中の下辺り、『時計マーク』があるが、これがトリガーの設定画面へのボタン。
進んだ先で、新しいトリガーを作成し、『フォーム送信時』と設定出来る。
スクリーンショット 2019-07-29 13.29.56.png

気を付けたポイント

  • 計算済みチームは無視するようにする
  • マイナスの点数のチームはルール上無条件で0点に書き換える
  • リタイアのチームはルール上無条件でタイムを150秒に書き換える

GASのコード

あくまで記録として、今回のコードを残しておく。
WROの専門用語が多出するが、気にしないで欲しい。

高校エキスパート

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetの情報の取得
  var sheet = ss.getSheetByName('フォームの回答');//Sheetの指定
  
  const columnBVals = sheet.getRange('A:A').getValues(); // A列値を配列で取得
  const LastRow = columnBVals.filter(String).length;  //空白を除き、配列の数を取得
  
  for(i=2;i<LastRow+1;i++){
    if(sheet.getRange(i,25).getValue()!="Yes"){
      sheet.getRange(i,16).setValue(sheet.getRange(i,7).getValue()*30);
      sheet.getRange(i,17).setValue(sheet.getRange(i,8).getValue()*10);
      sheet.getRange(i,18).setValue(sheet.getRange(i,9).getValue()*5);
      sheet.getRange(i,19).setValue(sheet.getRange(i,10).getValue()*30);
      sheet.getRange(i,20).setValue(sheet.getRange(i,11).getValue()*20);
      sheet.getRange(i,21).setValue(sheet.getRange(i,12).getValue()*10);
      sheet.getRange(i,22).setValue(sheet.getRange(i,13).getValue()*5);
      
      if(sheet.getRange(i,14).getValue()=="Yes"){
        sheet.getRange(i,23).setValue("10");
      }
      else if(sheet.getRange(i,14).getValue()=="No"){
        sheet.getRange(i,23).setValue("0");
      }
      
      if(sheet.getRange(i,15).getValue()=="Yes"){
        sheet.getRange(i,24).setValue("-10");
      }
      else if(sheet.getRange(i,15).getValue()=="No"){
        sheet.getRange(i,24).setValue("0");
      }
      
      var point = sheet.getRange(i,16).getValue()
      + sheet.getRange(i,17).getValue() 
      + sheet.getRange(i,18).getValue()
      + sheet.getRange(i,19).getValue()
      + sheet.getRange(i,20).getValue()
      + sheet.getRange(i,21).getValue() 
      + sheet.getRange(i,22).getValue()
      + sheet.getRange(i,23).getValue()
      + sheet.getRange(i,24).getValue();
      
      
      sheet.getRange(i,4).setValue(point);
      sheet.getRange(i,25).setValue("Yes");
      
      if(sheet.getRange(i,6).getValue()=="Yes"){
        sheet.getRange(i,5).setValue("150");
      }
      
      if(sheet.getRange(i,4).getValue()<0){
        sheet.getRange(i,4).setValue("0");
      }
    }
  }
}

中学エキスパート

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetの情報の取得
  var sheet = ss.getSheetByName('フォームの回答');//Sheetの指定
  
  const columnBVals = sheet.getRange('A:A').getValues(); // A列値を配列で取得
  const LastRow = columnBVals.filter(String).length;  //空白を除き、配列の数を取得
  
  for(i=2;i<LastRow+1;i++){
    if(sheet.getRange(i,25).getValue()!="Yes"){
      sheet.getRange(i,16).setValue(sheet.getRange(i,7).getValue()*25);
      sheet.getRange(i,17).setValue(sheet.getRange(i,8).getValue()*15);
      sheet.getRange(i,18).setValue(sheet.getRange(i,9).getValue()*10);
      sheet.getRange(i,19).setValue(sheet.getRange(i,10).getValue()*5);
      sheet.getRange(i,20).setValue(sheet.getRange(i,11).getValue()*20);
      sheet.getRange(i,21).setValue(sheet.getRange(i,12).getValue()*10);
      sheet.getRange(i,22).setValue(sheet.getRange(i,13).getValue()*5);
      sheet.getRange(i,24).setValue(sheet.getRange(i,15).getValue()*(-15));
      
      if(sheet.getRange(i,14).getValue()=="Yes"){
        sheet.getRange(i,23).setValue("10");
      }
      else if(sheet.getRange(i,14).getValue()=="No"){
        sheet.getRange(i,23).setValue("0");
      }
      
      var point = sheet.getRange(i,16).getValue()
      + sheet.getRange(i,17).getValue() 
      + sheet.getRange(i,18).getValue()
      + sheet.getRange(i,19).getValue()
      + sheet.getRange(i,20).getValue()
      + sheet.getRange(i,21).getValue() 
      + sheet.getRange(i,22).getValue()
      + sheet.getRange(i,23).getValue()
      + sheet.getRange(i,24).getValue();
      
      
      sheet.getRange(i,4).setValue(point);
      sheet.getRange(i,25).setValue("Yes");
      
      if(sheet.getRange(i,6).getValue()=="Yes"){
        sheet.getRange(i,5).setValue("150");
      }
      
      if(sheet.getRange(i,4).getValue()<0){
        sheet.getRange(i,4).setValue("0");
      }
    }
    
  }
}

小学エキスパート

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetの情報の取得
  var sheet = ss.getSheetByName('フォームの回答');//Sheetの指定
  
  const columnBVals = sheet.getRange('A:A').getValues(); // A列値を配列で取得
  const LastRow = columnBVals.filter(String).length;  //空白を除き、配列の数を取得
  
  for(i=2;i<LastRow+1;i++){
    if(sheet.getRange(i,27).getValue()!="Yes"){
      sheet.getRange(i,17).setValue(sheet.getRange(i,7).getValue()*25);//列7【M1】列17
      sheet.getRange(i,18).setValue(sheet.getRange(i,8).getValue()*15);//列8【M2】列18
      sheet.getRange(i,19).setValue(sheet.getRange(i,9).getValue()*5);//列9【M3】列19
      sheet.getRange(i,20).setValue(sheet.getRange(i,10).getValue()*10);//列10【M4】列20
      sheet.getRange(i,21).setValue(sheet.getRange(i,11).getValue()*5);//列11【M5】列21
      sheet.getRange(i,26).setValue(sheet.getRange(i,16).getValue()*(-5));//列16【M10】列26
      
      if(sheet.getRange(i,12).getValue()=="Yes"){
        sheet.getRange(i,22).setValue("15");
      }
      else if(sheet.getRange(i,12).getValue()=="No"){
        sheet.getRange(i,22).setValue("0");
      }
      
      if(sheet.getRange(i,13).getValue()=="Yes"){
        sheet.getRange(i,23).setValue("15");
      }
      else if(sheet.getRange(i,13).getValue()=="No"){
        sheet.getRange(i,23).setValue("0");
      }
      
      if(sheet.getRange(i,14).getValue()=="Yes"){
        sheet.getRange(i,24).setValue("5");
      }
      else if(sheet.getRange(i,14).getValue()=="No"){
        sheet.getRange(i,24).setValue("0");
      }
      
      if(sheet.getRange(i,15).getValue()=="Yes"){
        sheet.getRange(i,25).setValue("10");
      }
      else if(sheet.getRange(i,15).getValue()=="No"){
        sheet.getRange(i,25).setValue("0");
      }
      
      var point = sheet.getRange(i,17).getValue()
      + sheet.getRange(i,18).getValue()
      + sheet.getRange(i,19).getValue()
      + sheet.getRange(i,20).getValue()
      + sheet.getRange(i,21).getValue() 
      + sheet.getRange(i,22).getValue()
      + sheet.getRange(i,23).getValue()
      + sheet.getRange(i,24).getValue()
      + sheet.getRange(i,25).getValue()
      + sheet.getRange(i,26).getValue();
      
      
      sheet.getRange(i,4).setValue(point);
      sheet.getRange(i,27).setValue("Yes");
      
      if(sheet.getRange(i,6).getValue()=="Yes"){
        sheet.getRange(i,5).setValue("150");
      }
      
      if(sheet.getRange(i,4).getValue()<0){
        sheet.getRange(i,4).setValue("0");
      }
    }
    
  }
}

ミドル(高・中・小共通)

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetの情報の取得
  var sheet = ss.getSheetByName('フォームの回答');//Sheetの指定
  
  const columnBVals = sheet.getRange('A:A').getValues(); // A列値を配列で取得
  const LastRow = columnBVals.filter(String).length;  //空白を除き、配列の数を取得
  
  for(i=2;i<LastRow+1;i++){
    if(sheet.getRange(i,19).getValue()!="Yes"){
      if(sheet.getRange(i,11).getValue()=="Yes"){
        sheet.getRange(i,16).setValue("20");
      }
      else if(sheet.getRange(i,11).getValue()=="No"){
        sheet.getRange(i,16).setValue("0");
      }
      
      if(sheet.getRange(i,12).getValue()=="Yes"){
        sheet.getRange(i,17).setValue("10");
      }
      else if(sheet.getRange(i,12).getValue()=="No"){
        sheet.getRange(i,17).setValue("0");
      }
      
      sheet.getRange(i,14).setValue(sheet.getRange(i,9).getValue()*20);
      sheet.getRange(i,15).setValue(sheet.getRange(i,10).getValue()*10);
      sheet.getRange(i,18).setValue(sheet.getRange(i,13).getValue()*-5);
      
      var point = sheet.getRange(i,14).getValue()
      + sheet.getRange(i,15).getValue()
      + sheet.getRange(i,16).getValue()
      + sheet.getRange(i,17).getValue()
      + sheet.getRange(i,18).getValue();
      
      
      sheet.getRange(i,4).setValue(point);
      
      
      var length=0;
      
      if(sheet.getRange(i,8).getValue()=="1"){
        length=6230;
      }
      else if(sheet.getRange(i,8).getValue()=="2"){
        length=6710;
      }
      else if(sheet.getRange(i,8).getValue()=="3"){
        length=6710;
      }
      else if(sheet.getRange(i,8).getValue()=="4"){
        length=7190;
      }
      else if(sheet.getRange(i,8).getValue()=="5"){
        length=7190;
      }
      else if(sheet.getRange(i,8).getValue()=="6"){
        length=7190;
      }
      
      sheet.getRange(i,5).setValue(length/sheet.getRange(i,7).getValue());
      
      if(sheet.getRange(i,6).getValue()=="Yes"){
        sheet.getRange(i,5).setValue("40");
      }
      
      if(sheet.getRange(i,4).getValue()<0){
        sheet.getRange(i,4).setValue("0");
      }
      
      sheet.getRange(i,19).setValue("Yes");
    }
    
    
  }
}

小学ベーシック

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();//Spreadsheetの情報の取得
  var sheet = ss.getSheetByName('フォームの回答');//Sheetの指定
  
  const columnBVals = sheet.getRange('A:A').getValues(); // A列値を配列で取得
  const LastRow = columnBVals.filter(String).length;  //空白を除き、配列の数を取得
  
  for(i=2;i<LastRow+1;i++){
    if(sheet.getRange(i,17).getValue()!="Yes"){
      if(sheet.getRange(i,7).getValue()=="Yes"){
        sheet.getRange(i,12).setValue("1");
      }
      else if(sheet.getRange(i,7).getValue()=="No"){
        sheet.getRange(i,12).setValue("0");
      }
      
      if(sheet.getRange(i,8).getValue()=="Yes"){
        sheet.getRange(i,13).setValue("1");
      }
      else if(sheet.getRange(i,8).getValue()=="No"){
        sheet.getRange(i,13).setValue("0");
      }
      
      if(sheet.getRange(i,9).getValue()=="Yes"){
        sheet.getRange(i,14).setValue("1");
      }
      else if(sheet.getRange(i,9).getValue()=="No"){
        sheet.getRange(i,14).setValue("0");
      }
      
      if(sheet.getRange(i,10).getValue()=="Yes"){
        sheet.getRange(i,15).setValue("1");
      }
      else if(sheet.getRange(i,10).getValue()=="No"){
        sheet.getRange(i,15).setValue("0");
      }
      
      if(sheet.getRange(i,11).getValue()=="ゾーンCにイン"){
        sheet.getRange(i,16).setValue("2");
      }
      else if(sheet.getRange(i,11).getValue()=="黒円から出した"){
        sheet.getRange(i,16).setValue("1");
      }
      else if(sheet.getRange(i,11).getValue()=="No"){
        sheet.getRange(i,16).setValue("0");
      }
      
      var point = sheet.getRange(i,12).getValue()*20 
      + sheet.getRange(i,13).getValue()*20 
      + sheet.getRange(i,14).getValue()*20 
      + sheet.getRange(i,15).getValue()*20
      + sheet.getRange(i,16).getValue()*10;
      
      
      sheet.getRange(i,4).setValue(point);
      sheet.getRange(i,17).setValue("Yes");
      
      if(sheet.getRange(i,6).getValue()=="Yes"){
        sheet.getRange(i,5).setValue("150");
      }
      
      if(sheet.getRange(i,4).getValue()<0){
        sheet.getRange(i,4).setValue("0");
      }
    }
    
  }
}

まとめ

ちゃんと使用できてよかった笑

4
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
1