月: 2021年7月

オオタニサン！！大谷翔平の目標シート（マンダラチャート）をスプレッドシートに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を学ぶ方向け。
スプレッドシートの基本的な使い方からGASのベースとなるJavaScriptの基礎文法、GASでの初歩的なプログラミングを学べます。

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を学ぶ方向け。
スプレッドシートの基本的な使い方からGASのベースとなるJavaScriptの基礎文法、GASでの初歩的なプログラミングを学べます。

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を学ぶ方向け。
スプレッドシートの基本的な使い方からGASのベースとなるJavaScriptの基礎文法、GASでの初歩的なプログラミングを学べます。

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

←episode5　　　　　　　　　　　　　　　episode7→

←GAS自作サンプルへ戻る

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

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

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

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

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

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

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


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

  //行数を取得して、変数Nにメンバーの人数を格納
  let N=ss.getLastRow();

  //ループを使って各キャラクターの念が「変化系」と等しいか判定して結果入力
  for(let i=1;i<=N;i++){
    let nen=ss.getRange(i,2).getValue();  //【メンバー】の念を取得
  
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(nen=='変化系'){                      
      ss.getRange(i,3).setValue('ヒソカと一緒');
    }else{
      ss.getRange(i,3).setValue('XXX'); //違うからこっちに分岐
    }

  }  
}


//ヒソカチェッククリア
function HisoClear(){

  //行数を取得して、変数Nに人数を格納
  let N=ss.getLastRow();
  ss.getRange(1,3,N).clear();
}

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

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

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


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

  //行数を取得して、変数Nにメンバーの人数を格納
  let N=ss.getLastRow();

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

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

  //念を配列に格納
  array=ss.getRange(1,2,N).getValues();

  //arrayに念が格納されたことをログに出力して確認
  console.log(array);

  //ループを使って配列の中身が「変化系」と等しいか調べて判定結果を二次元配列として追加
  for(let i=0;i<N;i++){
    if(array[i][0]=='変化系'){
      judge.push('ヒソカと一緒');
    }else{
      judge.push('XXX');
    }
  }

  //判定結果が二次元配列として格納されたことをログで確認
  console.log(judge);

  
  //配列judgeをスプレッドシートに出力
  for(let i=1;i<=N;i++){
    ss.getRange(i,3,1).setValue(judge[i-1]);
  }
}


//ヒソカチェッククリア
function HisoClear(){

  //行数を取得して、変数Nに人数を格納
  let N=ss.getLastRow();
  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を学ぶ方向け。
スプレッドシートの基本的な使い方からGASのベースとなるJavaScriptの基礎文法、GASでの初歩的なプログラミングを学べます。

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

←episode4　　　　　　　　　　　　　　　episode6→

←GAS自作サンプルへ戻る

episode4 GASの「ヒソカチェックで、IFとループを一気に学ぶ」（Hunter×Hunterで覚えるGoogle Apps Script）

GAS（Google Apps Script）をアニメ、ハンターハンターを通して入門の入門から学べる記事を掲載しております。

この記事を通して、GASによるIF分岐とループ、SpreadSheetApp→アクティブシート→アクティブセルと階層を辿ることによって処理時間がかかってしまうことを空の配列を用意することで解決する方法を掲載しています。
GASでは配列の扱いがとても重要になります。

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

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

←GAS自作サンプルへ戻る

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

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

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

//キャラクターの名前と念の系統を入力する関数
function mySet(){

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

  //二次元配列hhにデータが格納されたことをログ出力で確認
  console.log(hh);

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

}

//入力範囲を消去する関数
function myClear(){

  hh=ss.getDataRange();
  hh.clear();

}

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

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

念が入力されているセルA2は（1,2）と洗わせます。
elseでは、「XXX」と表記されます。

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


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

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

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

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


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

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

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

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


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

もうお気づきですよね。

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

この、「i」の部分のみを変えています。
ただ、コードをいちいち変えるの面倒なので、Forによるループを使います。

実は、これまでで、ループのトレースができちゃっていました。

追加としては、

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

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

なぜ「i」なのか、それは、ループで慣習的に使う変数として、「i、j、k」がよく使われるからです。このことについては、「なぜ、新人つぶしのトンパさんは、トンパさんという名前なのか」と突き詰めてしまうことのようなものなので、ループではiとかを使うんだな、程度におさえておいて大丈夫です。

また、メンバーは4人なので、その人数を調べるために、変数Nに「LastRow」（最後の行）を調べます。

以上を踏まえてコードを書くと、こうなります。

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


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

  //行数を取得して、変数Nにメンバーの人数を格納
  let N=ss.getLastRow();

  //ループを使って各キャラクターの念が「変化系」と等しいか判定して結果入力
  for(let i=1;i<=N;i++){
    let nen=ss.getRange(i,2).getValue();  //【メンバー】の念を取得
  
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(nen=='変化系'){                      
      ss.getRange(i,3).setValue('ヒソカと一緒');
    }else{
      ss.getRange(i,3).setValue('XXX'); //違うからこっちに分岐
    }

  }  
}

この「Hisoka」をボタンの「スクリプトを割り当て」でボタンに登録します。
また、下記のコードで、判定結果の範囲を削除します。

//ヒソカチェッククリア
function HisoClear(){

  //行数を取得して、変数Nに人数を格納
  let N=ss.getLastRow();
  ss.getRange(1,3,N).clear();
}

一旦ここまでのコードをまとめます。

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


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

  //行数を取得して、変数Nにメンバーの人数を格納
  let N=ss.getLastRow();

  //ループを使って各キャラクターの念が「変化系」と等しいか判定して結果入力
  for(let i=1;i<=N;i++){
    let nen=ss.getRange(i,2).getValue();  //【メンバー】の念を取得
  
    //【メンバー】の念はヒソカと同じ「変化系」か？
    if(nen=='変化系'){                      
      ss.getRange(i,3).setValue('ヒソカと一緒');
    }else{
      ss.getRange(i,3).setValue('XXX'); //違うからこっちに分岐
    }

  }  
}


//ヒソカチェッククリア
function HisoClear(){

  //行数を取得して、変数Nに人数を格納
  let N=ss.getLastRow();
  ss.getRange(1,3,N).clear();
}

これで、ボタンを使って、判定したり消したりが出来る様になりました。
このままでも良いのですが、GASでループを使う場合、1回1回SpreadsheetAppからアクティブシートを辿ってしまうので処理に時間がかかってしまいます。

そこで、一旦配列arrayにメンバーの念系統を格納します。
また、メンバーの念系統がヒソカの変化系と等しいかどうかの判定結果を格納する配列judgeを用意します。

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

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

  //念を配列に格納
  array=ss.getRange(1,2,N).getValues();

配列arrayを使って、念系統がヒソカと同じ変化系かどうか調べて、その判定結果を配列judgeに格納するループです。
これ（配列）を使うことによって、スプレッドシートからアクティブシートに階層を辿って行く処理時間を短縮できます。

配列arrayをログ出力しました。


この配列arrayは、4行1列の二次元配列です。
なので、array[0][0]=ゴンの「強化系」,array[1][0]=キルアの「変化系」,array[2][0]=クラピカの「具現化系」,array[3][0]=レオリオの「放出系」にアクセスして、それがヒソカの「変化系」と等しいかどうか調べてその結果を配列judgeに格納しています。

//ループを使って配列の中身が「変化系」と等しいか調べて判定結果を二次元配列として追加
  for(let i=0;i<N;i++){
    if(array[i][0]=='変化系'){
      judge.push('ヒソカと一緒');
    }else{
      judge.push('XXX');
    }
  }

配列judgeのログはこちらです。

この判定結果の配列judgeをループでスプレッドシートに格納します。

//配列judgeをスプレッドシートに出力
  for(let i=1;i<=N;i++){
    ss.getRange(i,3,1).setValue(judge[i-1]);
  }

同様にスプレッドシートに書き込めました。
ここまでのコードをまとめます。

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


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

  //行数を取得して、変数Nにメンバーの人数を格納
  let N=ss.getLastRow();

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

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

  //念を配列に格納
  array=ss.getRange(1,2,N).getValues();

  //arrayに念が格納されたことをログに出力して確認
  console.log(array);

  //ループを使って配列の中身が「変化系」と等しいか調べて判定結果を二次元配列として追加
  for(let i=0;i<N;i++){
    if(array[i][0]=='変化系'){
      judge.push('ヒソカと一緒');
    }else{
      judge.push('XXX');
    }
  }

  //判定結果が二次元配列として格納されたことをログで確認
  console.log(judge);

  
  //配列judgeをスプレッドシートに出力
  for(let i=1;i<=N;i++){
    ss.getRange(i,3,1).setValue(judge[i-1]);
  }
}


//ヒソカチェッククリア
function HisoClear(){

  //行数を取得して、変数Nに人数を格納
  let N=ss.getLastRow();
  ss.getRange(1,3,N).clear();
}

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

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

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

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

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

←episode3　　　　　　　　　　　　　　　episode5→

←GAS自作サンプルへ戻る