7月, 2021 | ロスジェネ！ねこくんのリスキリングの学びをシェア

オオタニサン！！大谷翔平の目標シート（マンダラチャート）をスプレッドシートにGASでマクロを使って作ってみた

この記事を読むことによって、GAS（Google Apps Script)を使って、スプレッドシートに太枠の罫線を引く・セルの色を塗りつぶす・シートの書式を全てクリアする、といった処理を、二重ループなどを使うことによって詳細に学習できます。

また、剰余演算子、スプレッドシートにボタンを追加してマクロを割り当てて、更にボタンを複製する方法も触れております。

ループが二重になってくるとコードが複雑になってくるので、エディターのコードの部分を選択して右クリックして、「選択範囲のフォーマット」をクリックするとコードが整形できます。

今回の具体例として、満場一致でMVPを取得した二刀流で有名なメジャーリーガー、エンジェルスの大谷翔平選手が、高校時代に愛用していたマンダラチャート（目標シート）のフォーマットをスプレドシートに自動で作成出来るマクロをGASでプログラミングしました。

「オオタニサン」こと大谷翔平選手が高校時代にドラフト１位で8球団から指名されることを目標にして作ったマンダラチャートがこちらになります。

大谷翔平選手が使っていた目標達成シート（マンダラチャート）とは？作り方、目標達成シートを作る場合の注意点について

サイトマップはこちらから

GASサンプル集に戻る
 メインメニューに戻る
 これからプログラミングを始める方へ
 基本情報技術者へ
 自己紹介

マンダラチャートは規則性があるので、GASに限らずExcel VBAなどのマクロの練習には打って付けだと思います。

私の場合ですが、こんな感じに仕上がりました。

関数はグローバル関数と、ローカル関数を4つ使っていきます。
グローバル関数（どこからでも参照できる関数）は、SpreadSheetAppから辿ってアクティブドシートにアクセスするコードを書きました。

//グローバル関数でSpreadsheetAppからアクティブシートにアクセスする
const ss=SpreadsheetApp.getActiveSheet();

以降はローカル関数になります。
ローカル関数はスプレッドシート内でボタンを作って関数を登録しています。

ローカル関数１つ目：太枠線を囲う。

この関数の完成形のシートを掲載します。

上のシートを見ると分かるように、1行目の1列目、4列目、7列目と、3で割って１余る行と列を起点として太枠が描かれています。
太枠はThick Borderなので、それにちなんだ関数名「thickBorder」を付けています。

//太枠線を引く関数
function thickBorder() {

  /*枠線は、
  1行目の1列目、4列目、7列目、
  4行目の1列目、4列目、7列目、
  7行目の1列目、4列目、7列目
  と、3で割って1余るセルを基点として、3行3列分設定されている
  */

  for(let i=1;i<=9;i++){
    for (let j=1;j<=9;j++){
      if(i%3==1 && j%3==1){
        let cell=ss.getRange(i,j,3,3);
        cell.setBorder(true,true,true,true,null,null,'black',SpreadsheetApp.BorderStyle.SOLID_THICK);
      }
    }
  }
}

この関数をボタンに登録します。
ボタンはスプレッドシートの挿入メニュー→図形描画でこの画面が出てくるので、下の赤枠をクリックして、好きな形を選びます。

図形を選択した状態で、「太枠線を引く」などのボタン名を入力します。

また、図形を選択しているその状態で、下記の図の緑の「保存して終了」ボタンの下にあるの赤枠の「・・・」ボタンを押すと詳細な設定が可能になります。

その中でも良く使う処理が「ボタン名を中央揃えにする」処理だと思いますので、下記の図の赤枠内で任意の設定を行います。ここで、水平・垂直の文字の位置が調整出来ます。

あとはボタンの色や太さ、フォントの大きさなどをお好みに設定して、右上の緑の「保存して終了」を押します。

スプレッドシートに戻るので、ボタンを選択して（【すでに関数が登録されている場合は】間違ってボタンを押して実行しまわないように必ず【右クリック】で選択）、右上の「・・・」から「スクリプトの割り当て」を選んで、この場合は「thickBorder」と入力します。

これで、このボタンをポチると、スプレッドシートに太枠線が表示されました。
（スクリプト何々関数が見つかりませんでした、というエラーを防ぐために、前もって関数名をコピペしておくと便利かもしれません。）

ローカル関数2つ目：各太枠の中央のセルを薄黄色に塗りつぶす

完成形のシートです。

上図の様に濃い黄色に塗り潰す対象は、
2行目の2列目、5列目、8列目、
5行目の2列目、8列目、
8行目の2列目、5列目、8列目です。
（5行目の5列目はど真ん中で別の色に指定するので、今回は対象外）

今回塗りつぶしの色として指定する濃い黄色は、「#ffee00」と設定します。
これはRGB形式なので、最初のffで赤を最強、次のeeで緑を割と強め、最後の00で青を最弱、という設定にしています。
因みに白が#ffffffで、黒が#000000です。

濃い黄色はDark Yellowなので、それにちなんだ関数名「darkYellow」にしています。

スプレッドシートのボタンを複製するには、まず最初に作ったボタンを右クリックして、ボタン右上の「・・・」をクリックして編集を押し、図形描画画面からボタンをクリックして【control】+【C】でコピーしてから一旦右上の緑の「保存して終了」を押します。（Macの方は【command】ボタン）

面倒ですが、もう一度スプレッドシート画面に戻って、挿入→図形描画画面で、【control】＋【V】で貼り付けてボタンの文字を「濃い黄色に塗りつぶし」と変えます。

このローカル関数darkYellowのコードです。

//ど真ん中を除く各太枠の中央を濃い黄色に塗り潰す関数
function darkYellow() {

  /*
  濃い黄色（#ffee00）に塗り潰す対象は、
  2行目の2列目、5列目、8列目、
  5行目の2列目、      8列目、
  8行目の2列目、5列目、8列目です。
  */

  for (let i = 1; i <= 9; i++) {
    for (let j = 1; j <= 9; j++) {
      if (i != 5 || j != 5) {
        if (i % 3 == 2 && j % 3 == 2) {
          ss.getRange(i, j).setBackground('#ffee00');
        }
      }
    }
  }

}

ローカル関数3つ目：ど真ん中のエリアの塗りつぶし

今度はど真ん中の太枠セル内を薄い黄色に塗りつぶしをして、更にその中央をオレンジ色にします。
薄い黄色を「#ffff88」、オレンジを「#ffd400」としました。
このローカル関数を「centerArea」にします。

完成形のシートとコードです。

//ど真ん中の太枠を薄黄色に塗りつぶしてから、その中央を濃いオレンジに塗りつぶす
function centerArea(){
  ss.getRange(4,4,3,3).setBackground('#ffff88');
  ss.getRange(5,5).setBackground('#ffd400');
}

ローカル関数4つ目：書式をクリア

最後はこのシートの書式のみをクリアします。
完成形はこちらになります。

このローカル関数をmyClearに致しました。

//シートの書式をクリアにする
function myClear(){
  ss.clearFormats();
}

ここまでのプログラムを全てまとめます。

//グローバル関数でSpreadsheetAppからアクティブシートにアクセスする
const ss=SpreadsheetApp.getActiveSheet();

//ローカル関数その1:太枠線を引く関数
function thickBorder() {

  /*枠線は、
  1行目の1列目、4列目、7列目、
  4行目の1列目、4列目、7列目、
  7行目の1列目、4列目、7列目
  と、3で割って1余るセルを基点として、3行3列分設定されている
  */

  for(let i=1;i<=9;i++){
    for (let j=1;j<=9;j++){
      if(i%3==1 && j%3==1){
        let cell=ss.getRange(i,j,3,3);
        cell.setBorder(true,true,true,true,null,null,'black',SpreadsheetApp.BorderStyle.SOLID_THICK);
      }
    }
  }
}

//ローカル関数その2:ど真ん中を除く各太枠の中央を濃い黄色に塗り潰す関数
function darkYellow() {

  /*
  濃い黄色（#ffee00）に塗り潰す対象は、s
  2行目の2列目、5列目、8列目、
  5行目の2列目、      8列目、
  8行目の2列目、5列目、8列目です。
  */

  for (let i = 1; i <= 9; i++) {
    for (let j = 1; j <= 9; j++) {
      if (i != 5 || j != 5) {
        if (i % 3 == 2 && j % 3 == 2) {
          ss.getRange(i, j).setBackground('#ffee00');
        }
      }
    }
  }

}

//ローカル関数その3:ど真ん中の太枠を薄黄色に塗りつぶしてから、その中央を濃いオレンジに塗りつぶす
function centerArea(){
  ss.getRange(4,4,3,3).setBackground('#ffff88');
  ss.getRange(5,5).setBackground('#ffd400');
}

//ローカル関数その4:シートの書式をクリアにする
function myClear(){
  ss.clearFormats();
}

お疲れ様でした。
すごく長くなって冗長な所もあったと思いますがご精読、大変にありがとうございます。私も優秀な方からは年齢関係なくこれからも沢山学んで吸収して行きます！！

■参考文献の紹介■
初めてGASを学ぶ方向け。
スプレッドシートの基本的な使い方からGASのベースとなるJavaScriptの基礎文法、GASでの初歩的なプログラミングを学べます。

GASに少し慣れて来たら、基礎固めとリファレンスとしてこの本を通してじっくり学べます。

サイトマップはこちらから

GASサンプル集に戻る
 メインメニューに戻る
 これからプログラミングを始める方へ
 基本情報技術者へ
 自己紹介

episode8（最終回） GASの「キャラの身長をシンプルなグラフ化する」（Hunter×Hunterで覚えるGoogle Apps Script）

この記事を読むことによって、GAS（Google Apps Script）の最も基礎的な縦棒グラフの作り方が学べます。
このコーナーではGASをアニメ、ハンターハンターを通して入門の入門から学べる記事を掲載しております。

サイトマップはこちらから

GASサンプル集に戻る
 メインメニューに戻る
 これからプログラミングを始める方へ
 基本情報技術者へ
 自己紹介

まず、前回(episode7)の復習をします。

セルに「平均」、「170cm以上の人数」と入力し、平均身長と該当する人数を求めています。

この身長のデータは、HUNTER×HUNTER（ハンターハンター）の主要キャラクターの設定を用いました。

前回のコードです。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//身長の平均と170cm以上の人数を求める関数
function height(){
  ss.getRange(6,1).setValue('平均');
  ss.getRange(7,1).setValue('170cm以上の人数');

  //平均身長を計算
  ss.getRange(6,2).setFormula('=AVERAGE(D1:D4)');

  //身長が170cm以上のキャラクターの人数を求める
  ss.getRange(7,2).setFormula('=COUNTIF(D1:D4,">=170")');
}

今回は、「キャラクター名」と「身長」の最も簡単な縦棒グラフを作ります。
名前の範囲をセルA1から4行分取得して、定数nameに格納します。

const name=ss.getRange(1,1,4);

身長の範囲をセルD1から4行分取得して、chara_heightとします。

const chara_height=ss.getRange(1,4,4);

以下、「chart」というグラフを作る処理になります。
ここまでで、上記のnameと、chara_heightを追加します。

let chart=ss.newChart()
    .asColumnChart()//単純な縦棒グラフ
    .addRange(name)//名前を追加
    .addRange(chara_height) //身長を追加

セルA10から縦横に10ドット下がった位置に配置し、高さ300、幅400のグラフを指定します。

.setPosition(10,1,10,10)//セルA10からタテ・ヨコ10ドット下がった所に配置
    .setOption('height',300)//グラフの高さを設定
    .setOption('width',400)//グラフの幅を設定
    .build()//グラフを作る

最後にグラフchartをアクティブなスプレッドシートに追加します。

ss.insertChart(chart);

ここまでのコードです。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//キャラクター名と身長の単純な縦棒グラフを作る
function myGraph(){
  
  //キャラクターの名前を定数nameに格納する
  const name=ss.getRange(1,1,4);

  //キャラクターの身長を定数chara_heightに格納する
  const chara_height=ss.getRange(1,4,4);

  
  //グラフを作る処理
  let chart=ss.newChart()
    .asColumnChart()//単純な縦棒グラフ
    .addRange(name)//名前を追加
    .addRange(chara_height) //身長を追加
    .setPosition(10,1,10,10)//セルA10からタテ・ヨコ10ドット下がった所に配置
    .setOption('height',300)//グラフの高さを設定
    .setOption('width',400)//グラフの幅を設定
    .build()//グラフを作る
    
    //アクリティブなスプレッドシートにグラフchartを追加する
    ss.insertChart(chart);

}

それでは、マクロを実行します。

スプレッドシートに切り替えて、拡張機能→マクロ→関数を追加で指定のマクロ（今回でいえば、myGraph）をインポートします。

再び拡張機能→マクロから「myGraph」を選んで実行します。

グラフが表示されました。

お疲れ様でした。
入門講座はここで最後になります。
最終回までご覧いただき、ありがとうございました(=^x^=)mm

GASに少し慣れて来たら、基礎固めとリファレンスとしてこの本を通してじっくり学べます。

←episode7

←GAS自作サンプルへ戻る

episode7 GASの「キャラの身長で演算（平均・条件に該当する数のカウント）」（Hunter×Hunterで覚えるGoogle Apps Script）

この記事を読むことによって、GAS（Google Apps Script）で計算結果を格納する方法を学べます。今回は平均を求めるAVERAGEと条件に該当する人数を数えるCOUNTIF関数を使います。また、文字入力の復習も出来ます。

Excelが分かれば、すんなり納得されると思います。

このコーナーではGASをアニメ、ハンターハンターを通して入門の入門から学べる記事を掲載しております。

サイトマップはこちらから

GASサンプル集に戻る
 メインメニューに戻る
 これからプログラミングを始める方へ
 基本情報技術者へ
 自己紹介

前回(episode6)の復習をします。

ハンター試験ルーキーのキャラクターのセルの背景や文字の色を設定しました。
そのコードと実行結果です。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//セルの背景色とフォントを変える
function rookieColor(){

  //A列B列の名前と念系統が入っているセル範囲をcelDATAと定義
  const cellDATA=ss.getRange(1,1,4,2);

  //背景色を黒にする
  cellDATA.setBackground('black');

  //文字を白にする
  cellDATA.setFontColor('white');

  //C列のヒソカチェックのセル範囲をcellHISOKAと定義
  const cellHISOKA=ss.getRange(1,3,4);

  //背景を薄紫にする
  cellHISOKA.setBackground('#eeddee');

}

今回は、この一つ右のセルにキャラクターの身長を入力して、平均を求めてみたいと思います。（下の表のD列にゴンからレオリオまでの身長を入れました。）

それで、まずはこの表の下のセルA6に「平均」といいう文字を入れてみます。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//身長の平均と170cm以上の人数を求める関数
function height(){
  ss.getRange(6,1).setValue('平均');
}

この様に文字が入りました。

また、この1つ下のセルA7に「170cm以上の人数」の文字を入れます。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//身長の平均と170cm以上の人数を求める関数
function height(){
  ss.getRange(6,1).setValue('平均');
  ss.getRange(7,1).setValue('170cm以上の人数');
}

少しA列を広げました。

今度は、セルB6に平均身長を求めます。

ss.getRange(6,2).setFormula(‘=AVERAGE(D1:D4)’);

この様に、setFormula(‘=関数(セル範囲)’);で求まります。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//身長の平均と170cm以上の人数を求める関数
function height(){
  ss.getRange(6,1).setValue('平均');
  ss.getRange(7,1).setValue('170cm以上の人数');

  //平均身長を計算
  ss.getRange(6,2).setFormula('=AVERAGE(D1:D4)');
}

最後に、セルB7に身長が170cm以上のキャラクターの人数を求めます。

ss.getRange(7,2).setFormula(‘=COUNTIF(D1:D4,”>=170″)’);

この様に、setFormula(‘COUNTIF関数(=セル範囲,”カウントする条件”)’);で求まります。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//身長の平均と170cm以上の人数を求める関数
function height(){
  ss.getRange(6,1).setValue('平均');
  ss.getRange(7,1).setValue('170cm以上の人数');

  //平均身長を計算
  ss.getRange(6,2).setFormula('=AVERAGE(D1:D4)');

  //身長が170cm以上のキャラクターの人数を求める
  ss.getRange(7,2).setFormula('=COUNTIF(D1:D4,">=170")');
}

この様に、求まりました。ご精読ありがとうございました。

GASに少し慣れて来たら、基礎固めとリファレンスとしてこの本を通してじっくり学べます。

←episode6　　　　　　　　　　　　　　　episode8（最終回）→

←GAS自作サンプルへ戻る

episode6 GASの「指定したセル範囲に色を付ける」（Hunter×Hunterで覚えるGoogle Apps Script）

この記事を読むことによって、GAS（Google Apps Script）でスプレッドシートの指定の範囲のセル背景やフォントに色を付けることを学べます。
GASをアニメ、ハンターハンターを通して入門の入門から学べる記事を掲載しております。

サイトマップはこちらから

GASサンプル集に戻る
 メインメニューに戻る
 これからプログラミングを始める方へ
 基本情報技術者へ
 自己紹介

前回(episode5)の復習をします。

スプレッドシートA列にある、ハンター試験ルーキーのキャラクター名のフォントを大きくしたり、文字を太くしたりしました。

そのコードと結果です。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//キャラクターの名前を12ptの太字にする
function Bold12pt(){

  const N=ss.getLastRow();
  let cellROOKIE=ss.getRange(1,1,N);
  cellROOKIE.setFontSize(12).setFontWeight('bold');

}

今回は、この表に色を付けます。
イメージとしては、厨二病っぽい感じにしたいと思います。

各キャラクターの名前（列A）と念系統（列B）を黒背景に白文字に、
ヒソカチェックの列（列C）は、紫にします。

列ABの4行２列分の範囲をcellDATAと定義します。

//A列B列の名前と念系統が入っているセル範囲をcelDATAと定義
let cellDATA=ss.getRange(1,1,4,2);

背景色を黒にしています。

cellDATA.setBackground(‘black’);

文字の色を白にしています。

cellDATA.setFontColor(‘white’);

列Cの4行1列分の範囲をcellHISOKAという名前にします。

//C列のヒソカチェックのセル範囲をcellHISOKAと定義
let cellHISOKA=ss.getRange(1,3,4);

白がffffff、黒が000000で、最初の2桁が赤、次が緑、最後が青なので、薄紫を[eeddee]で表しました。
このコードでは、赤と青を強めにしていて、薄い紫になります。

cellHISOKA.setBackground(‘#eeddee’);

ここまでのコードです。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//セルの背景色とフォントを変える
function rookieColor(){

  //A列B列の名前と念系統が入っているセル範囲をcelDATAと定義
  const cellDATA=ss.getRange(1,1,4,2);

  //背景色を黒にする
  cellDATA.setBackground('black');

  //文字を白にする
  cellDATA.setFontColor('white');

  //C列のヒソカチェックのセル範囲をcellHISOKAと定義
  const cellHISOKA=ss.getRange(1,3,4);

  //背景を薄紫にする
  cellHISOKA.setBackground('#eeddee');

}

ご精読ありがとうございました。

GASに少し慣れて来たら、基礎固めとリファレンスとしてこの本を通してじっくり学べます。

←episode5　　　　　　　　　　　　　　　episode7→

←GAS自作サンプルへ戻る

episode5 GASの「指定したセル範囲のフォントを変える」（Hunter×Hunterで覚えるGoogle Apps Script）

この記事を読むことで、GAS（Google Apps Script）で、スプレッドシートの指定の範囲の文字を太字にする処理を学べます。
GASをアニメ、ハンターハンターを通して入門の入門から学べる記事を掲載しております。

サイトマップはこちらから

GASサンプル集に戻る
 メインメニューに戻る
 これからプログラミングを始める方へ
 基本情報技術者へ
 自己紹介

前回(episode4)の復習をします。

IF文とForループを使って、各キャラクターの念がヒソカと同じ「変化系」か、それ以外かで分岐しました。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//行数(人数)を取得する
const N=ss.getLastRow();
// console.log(N); 確認済みなのでコメントアウトしました。


//【メンバー】がヒソカと同じ念系統か判定する関数
function Hisoka() {

  for(i=1;i<=N;i++){
    let nen = ss.getRange(i,2).getValue(); //メンバーの念を取得
    // console.log(nen); 確認済みなのでコメントアウトしました。
  
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(nen=='変化系'){
      ss.getRange(i,3).setValue('ヒソカと一緒'); //キルアの場合
    }else{
      ss.getRange(i,3).setValue('XXX'); //キルア以外
    }
  }
}

//ヒソカチェックのクリア
function HisokaClear(){
  
  ss.getRange(1,3,N).clear();
}

このコードでもループを使ったIFによる分岐とその結果の出力は可能なのですが、GASでは、SpreadsheetAppからアクティブシートを辿り、更にセルを辿るとその分処理時間がかかります。

そこで、配列を使って分岐をするコードも作りました。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//行数(人数)を取得する
const N=ss.getLastRow();


//【メンバー】がヒソカと同じ念系統か判定する関数
function Hisoka() {

  let array= [];
  let judge = [];

  //B列の各メンバーの念を配列arrayに格納
  array=ss.getRange(1,2,N).getValues();
  console.log(array);

  for(i=0;i<N;i++){
   
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(array[[i]]=='変化系'){
      judge.push(['ヒソカと一緒']); //キルアの場合
    }else{
      judge.push(['XXX']); //キルア以外
    }
  }
  console.log(judge);

  //スプレッドシートC列4行分に判定結果の入った配列judgeごと入力する。
  ss.getRange(1,3,N).setValues(judge);
}

//ヒソカチェックのクリア
function HisokaClear(){
  
  ss.getRange(1,3,N).clear();
}

今回は、この表のA列にあるキャラクターの名前のフォントを変えます。
キャラクター名の所を12ptと少し大きいフォントにして、更に太字にして目立たせます。

ハンター試験ルーキーのデータが掲載されている範囲は、セルA1からC4まであります。この範囲が文字で大きさ12ptになります。

キャラクターの人数は、getLastRowで取得します。

const N=ss.getLastRow();

範囲に「cellROOKIE（ルーキー）」と名前を付けて、大きさを変える範囲を確保します。

let cellROOKIE=ss.getRange(1,1,N);

(1,1,N)は、セルA1（1,1）からN行分の範囲という意味です。
これで、キャラクターが増えても対応可能です。

この範囲にフォントの大きさを12ptで太字にする設定を下記のコードに施します。

cellROOKIE.setFontSize(12).setFontWeight(‘bold’);

ここまでのコードです。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//キャラクターの名前を12ptの太字にする
function Bold12pt(){

  const N=ss.getLastRow();
  let cellROOKIE=ss.getRange(1,1,N);
  cellROOKIE.setFontSize(12).setFontWeight('bold');

}

この様に、キャラクターの名前がフォント12の太字になりました。

7行目の

const N=ss.getLastRow();

で、最終行を取得しているので、キャラクターの名前が増えても対応可能です。

新人潰しのトンパさんからビスケまで、キャラクターを追加しました。
再度実行してみます。

全員の名前が太字になりました。

ご精読ありがとうございました。

サイトマップはこちらから

GASサンプル集に戻る
 メインメニューに戻る
 これからプログラミングを始める方へ
 基本情報技術者へ
 自己紹介

GASに少し慣れて来たら、基礎固めとリファレンスとしてこの本を通してじっくり学べます。

←episode4　　　　　　　　　　　　　　　episode6→

←GAS自作サンプルへ戻る

episode4 GAS【IFとループ】ヒソカの念チェック「HUNTER×HUNTERで覚えるGoogle Apps Script」

この記事のシリーズを読むことで、アニメHUNTER×HUNTER（ハンターハンター）を通してGAS（Google Apps Script）の最初の一歩が学べます。

この記事では、

・GASによるIF分岐とループでヒソカと同じ念系統か調べる

・配列で処理時間を短縮

・最後の行をgetLastRow()で求める

SpreadSheetApp→アクティブシート→アクティブセルと階層を辿ることによって処理時間がかかってしまうことを空の配列を用意することで解決する方法を掲載しています。

GASでは配列の扱いがとても重要になります。

ますは、前回(episode3)の復習をします。

・セルA1からB4にキャラクター名を入力する関数と消去する関数を作り、それぞれをボタンに登録しました。

//アクティブなスプレッドシートを選択するグローバル領域
const ss=SpreadsheetApp.getActiveSheet();


//スプレッドシートにキャラクターの名前と念系統の入った配列を出力する
function nenArray(){

  //二次元配列hhにキャラクターの名前と念系統を格納
  const hh = [
      ['ゴン','強化系'],
      ['キルア','変化系'],
      ['クラピカ','具現化系'],
      ['レオリオ','放出系']
    ];
  
  //配列hhをログに出力
  console.log(hh);

  //行の長さを調べる
  console.log(hh.length);

  //列の長さを調べる
  console.log(hh[0].length);

  //二次元配列hhを格納するために、スプレッドシートの4行2列を確保して出力する
  ss.getRange(1,1,hh.length,hh[0].length).setValues(hh);

}


//出力した配列を消去する
function myClear() {

  //出力した配列をgetDataRangeを使って取得して消去する
  ss.getDataRange().clear();

}

実行するとこの様になっていると思います。

ヒソカの念は「変化系」でキルアと一緒です。

今回はIF文を使って、ゴンからレオリオまでのキャラの念が、ヒソカと同じ変化系かどうか調べます。

ゴンは「強化系」なので「変化系」とは異なる為、条件が当てはまらないelseへと分岐します。

ゴンの念が入力されているセル「B1」は「1行目の2列目」ですので、（行,列）で書くと（1,2）と書くことも出来ます。

処理はelseになる為、隣のセルである「C1 （1,3）」には「XXX」と入力されます。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();


//【ゴン】がヒソカと同じ念系統か判定する関数
function Hisoka() {
  let nen = ss.getRange(1,2).getValue(); //ゴンの念を取得
  console.log(nen);
  
  //【ゴン】の念はヒソカと同じ「変化系」か？
  if(nen=='変化系'){
    ss.getRange(1,3).setValue('ヒソカと一緒');
  }else{
    ss.getRange(1,3).setValue('XXX'); //違うからこっちに分岐
  }
}

ログ出力です。
ゴンの念系統「強化系」が出力されました。

スプレッドシートです。
C1に「XXX」と入りました。

次に、「キルア」を調べます。

キルアは「変化系」なので、「ヒソカ」と同じという条件が当てはまり「ヒソカと一緒」と表示されます。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();


//【キルア】がヒソカと同じ念系統か判定する関数
function Hisoka() {
  let nen = ss.getRange(2,2).getValue(); //キルアの念を取得
  console.log(nen);
  
  //【キルア】の念はヒソカと同じ「変化系」か？
  if(nen=='変化系'){
    ss.getRange(2,3).setValue('ヒソカと一緒'); //同じだからこっちに分岐
  }else{
    ss.getRange(2,3).setValue('XXX'); 
  }
}

ログ出力です。

スプレッドシートです。
キルアの念はヒソカと同じなので、セルC2に「ヒソカと一緒」と入力されました。

次に、「クラピカ」を調べます。

クラピカは「具現化系」なので、「変化系」とは異なる為、条件が当てはまらないelseへと分岐します。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();


//【クラピカ】がヒソカと同じ念系統か判定する関数
function Hisoka() {
  let nen = ss.getRange(3,2).getValue(); //クラピカの念を取得
  console.log(nen);
  
  //【クラピカ】の念はヒソカと同じ「変化系」か？
  if(nen=='変化系'){
    ss.getRange(3,3).setValue('ヒソカと一緒'); 
  }else{
    ss.getRange(3,3).setValue('XXX'); //違うからこっちに分岐
  }
}

ログ出力です。

スプレッドシートです。
ヒソカとは念が異なるので、C3に「XXX」と入力されました。

最後に、「レオリオ」を調べます。
レオリオは「放出系」なので、ヒソカの「変化系」とは異なる為、条件が当てはまらないelseへと分岐します。

//アクティブなスプレッドシートを選択するグローバル（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();


//【レオリオ】がヒソカと同じ念系統か判定
function Hisoka(){
  let nen=ss.getRange(4,2).getValue();  //レオリオの念を取得
  
  //【レオリオ】の念はヒソカと同じ「変化系」か？
  if(nen=='変化系'){                      
    ss.getRange(4,3).setValue('ヒソカと一緒');
    }else{
    ss.getRange(4,3).setValue('XXX'); //違うからこっちに分岐
  }
}

ログ出力です。
レオリオの「放出系」が出力されました。

スプレッドシートではC4に「XXX」と出力してelseの処理を実行しました。

ここまでで、「なんだか同じような処理をしていて、それで一部だけを変更している」と言う感覚になられたと思います。

上記のコードで「i」の部分のみを変えています。
そこで、ゴンからレオリオまでを繰り返して調べられる、Forによるループを使います。

getRange(i,2)getValue(); //キャラの念を取得　
getRange(i,3)setValue(‘ヒソカと同じか違うか’)　　　　　　

実はここまでの記事で、ループのトレースを行えておりました。

追加としては、

ゴン・・・i=1
キルア・・・i=2
クラピカ・・・i=3
レオリオ・・・i=4

と、変数が出てくるぐらいです。

なぜ「i」なのか、それは、ループで慣習的に使う変数として、「i、j、k」がよく使われるからです。

このことについては、「なぜ、新人つぶしのトンパさんは、トンパさんという名前なのか」と突き詰めてしまうことの様なものなので、ループではiとかを使うんだな、程度に捉えておりましたが、やはり気になって調べた所、分かりやすい記事を見つけました。

「i, j, k」ってなに？知っておきたいfor文の変数名の由来

ゴン〜レオリオまでメンバーは4人ですので、その人数を調べるために、変数Nに「getLastRow()」（最後の行）を調べて格納します。

最後の行…getLastRow();

以上を踏まえてループとIFを使ったコードを書くと、下記の様になります。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();


//【メンバー】がヒソカと同じ念系統か判定する関数
function Hisoka() {

  const N = ss.getLastRow(); //人数は変数Nに格納。この場合は4人
  console.log(N);

  for(i=1;i<=N;i++){
    let nen = ss.getRange(i,2).getValue(); //メンバーの念を取得
    console.log(nen);
  
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(nen=='変化系'){
      ss.getRange(i,3).setValue('ヒソカと一緒'); //キルアの場合
    }else{
      ss.getRange(i,3).setValue('XXX'); //キルア以外
    }
  }
}

ログ出力です。
スプレッドシートには同様に出力されております。

この「Hisoka」をボタンの「スクリプトを割り当て」でボタンに登録します。
ボタンを右クリックして3点リーダーから登録します。

また、下記のコードで、ヒソカと念が同じかどうか調べた範囲の判定結果を削除します。

//ヒソカチェックのクリア
function HisokaClear(){
  
  //行数を取得して変数Nに人数を格納
  const N=ss.getLastRow();

  ss.getRange(1,3,N).clear();
}

一旦ここまでのコードをまとめます。
人数の行数をは判定もクリアも同じ4人でNですので、グローバル領域に移動しました。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//行数(人数)を取得する
const N=ss.getLastRow();
// console.log(N); 確認済みなのでコメントアウトしました。


//【メンバー】がヒソカと同じ念系統か判定する関数
function Hisoka() {

  for(i=1;i<=N;i++){
    let nen = ss.getRange(i,2).getValue(); //メンバーの念を取得
    // console.log(nen); 確認済みなのでコメントアウトしました。
  
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(nen=='変化系'){
      ss.getRange(i,3).setValue('ヒソカと一緒'); //キルアの場合
    }else{
      ss.getRange(i,3).setValue('XXX'); //キルア以外
    }
  }
}

//ヒソカチェックのクリア
function HisokaClear(){
  
  ss.getRange(1,3,N).clear();
}

紫のボタン「ヒソカチェック」に関数「Hisoka」を、灰色のボタン「ヒソ消し」に関数「HisokaClear」それぞれスクリプトを割り当てます。

これで、ボタンを使って、判定したり消したりが出来る様になりました。
現在、ヒソ消しを押して、判定の列は削除してあります。

このままでも良いのですが、GASでループを使う場合、1回1回SpreadsheetAppからアクティブシートを辿ってしまうので処理に時間がかかってしまいます。

そこで、一旦B列4行分の各メンバーの念系統を配列arrayに格納します。
配列arrayには[[‘強化系’],[‘変化系’],[‘具現化系’],[‘放出系’]]が入ります。

また、そのメンバーの念系統がヒソカの変化系と等しいかどうかの判定結果を格納する配列judgeを用意します。
配列arrayには[[‘XXX’],[‘ヒソカと一緒’],[‘XXX’],[‘XXX’]]が入ります。

それぞれの空の配列を用意しております。

//【メンバー】の念を格納する配列を宣言
  let array=[];

//変化系と等しいか判定結果を入れる配列
  let judge=[];

配列arrayを使って、念系統がヒソカと同じ変化系かどうか調べて、その判定結果を配列judgeに格納する処理にループを用います。
また、judgeに格納するときにpushを使います。

この配列を使うことによって、スプレッドシートからアクティブシートに階層を辿って行く処理時間を短縮できます。

ここまでの処理について、ログ出力のところまでのコードを掲載致しました。
配列は0から始まるので、ループの条件部分を修正しました。

i=0、i<Nと変更してあります。

また、配列judgeに入れる際、スプレッドシートに出力するのに二次元配列の形にするので、と[ ]を付けて配列に追加しております。

judge.push([‘ヒソカと一緒’]) → ifでキルアの念系統の場合

judge.push([‘XXX’]) → elseでキルア以外のメンバーの念系統の場合

その部分のコードの抜粋です。

//【メンバー】の念はヒソカと同じ「変化系」か？
    if(array[[i]]=='変化系'){
      judge.push(['ヒソカと一緒']); //キルアの場合
    }else{
      judge.push(['XXX']); //キルア以外
    }

ここまでのログ出力までのコードです。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//行数(人数)を取得する
const N=ss.getLastRow();


//【メンバー】がヒソカと同じ念系統か判定する関数
function Hisoka() {

  let array= [];
  let judge = [];

  //B列の各メンバーの念を配列arrayに格納
  array=ss.getRange(1,2,N).getValues();
  console.log(array);

  for(i=0;i<N;i++){
   
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(array[[i]]=='変化系'){
      judge.push(['ヒソカと一緒']); //キルアの場合
    }else{
      judge.push(['XXX']); //キルア以外
    }
  }
  console.log(judge);

}

配列arrayとヒソカの念との比較のログを出力しました。

【配列array】

【配列judge】

この後、配列judgeをスプレッドシートのC列に4行分書き出します。
関数Hisokaにこの2行を加えたのみです。

//スプレッドシートC列4行分に判定結果の入った配列judgeごと入力する。
  ss.getRange(1,3,N).setValues(judge);

ここまでのコードをまとめました。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//行数(人数)を取得する
const N=ss.getLastRow();


//【メンバー】がヒソカと同じ念系統か判定する関数
function Hisoka() {

  let array= [];
  let judge = [];

  //B列の各メンバーの念を配列arrayに格納
  array=ss.getRange(1,2,N).getValues();
  console.log(array);

  for(i=0;i<N;i++){
   
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(array[[i]]=='変化系'){
      judge.push(['ヒソカと一緒']); //キルアの場合
    }else{
      judge.push(['XXX']); //キルア以外
    }
  }
  console.log(judge);

  //スプレッドシートC列4行分に判定結果の入った配列judgeごと入力する。
  ss.getRange(1,3,N).setValues(judge);
}

//ヒソカチェックのクリア
function HisokaClear(){
  
  ss.getRange(1,3,N).clear();
}

お疲れ様でした。ブレイクタイムPhotoは、