退職者リストに沿って名簿から退職者を削除するスクリプトをGASを使って書く

g前々から作っていた講師を検索するツールですが、現在Googleフォームから得られたデータをGoogleスプレッドシートに保存し、そこからデータを整形するスクリプトを作成しているところです。

Google Apps Script(GAS)を用いて実装していますが、JavaScriptすら触ったことがないので初心者実装になっています。

作成するスクリプトは

• フォームから名簿を作成する
• データが更新されたらExcelに渡す表を作成する
• Excelに渡す表から退職者を削除する

今回は表から退職者を削除するスクリプトを組みました。

1.やりたいこと

上のような表から退職者だけを削除したいです。ただし、退職者は次の図のように管理されているものとします。

講師名はテストデータのため適当です。在籍フラグ1は在籍、0は退職としています。

2.スクリプト全体

Mainって関数名に入っているのに全然Main感ないのはごめんなさい。

3.説明やメモ

JavaScriptやGASを書いたことが初めてなので、書いているときに調べたことや自分の実装の説明などを書いていきます。

3-1.VScodeでGASを編集する

“VSCode GAS”ってググればいくらでも出てくるので説明は省略します。

Googleが提供しているGASをローカルで管理できるようになるCLIツールであるclaspを導入してloginなどをすれば使えるようになります。

コマンドを実行したときに、実行開始したみたいなレスポンスが返ってくるまでに間があるのが気になる。

3-2.GASのレファレンス

Google Apps Script Reference

3-3.変数の宣言

var / let / constの3種類があるそうです。letは再宣言不可・ブロックスコープで、varは再宣言可能、関数スコープになります。

varで調べると”非推奨”という単語とともに検索結果がでてきますし、入門書もvarを使わなくなってきたとかなんとか。

3-4.独自関数

受け取った数を10倍にして返す関数を作ってみます。

function myFunction(number){
    return number * 10;
}

C/C++の癖で引数の部分に変数の型を書かないように気をつけましょう。

また、引数ですがオブジェクトを指定することもできます。

C++の考え方ですとこれは値渡しなのか参照渡しなのかポインタ渡しなのかと考えないといけませんが、JavaScriptなどはそういう概念とは違う感じらしいので、そこまで考えなくても良さそう？

JavaScriptに参照渡し/値渡しなど存在しない

3-5.行末のセミコロン

JavaScriptには自動セミコロン挿入という機能があるらしいので書かない派閥も存在するらしいです。

私はC++をメインに書くので行末にセミコロンがないと死にます。だからつけます。

3-6.配列

配列はリストのようなオブジェクトであり、そのプロトタイプは配列に対して横断的な操作や変更を行うメソッドを持っています。 JavaScript の配列は、要素数も要素の型も固定されていません。配列の長さは常に可変であり、データを連続しない位置に格納できるため、 JavaScript の配列は密であることが保証されていません。これはプログラマーの使い方次第です。一般に、これらは便利な特性です。しかし、もし特定の用途で望ましくないのであれば、型付き配列を使用したほうが良いかもしれません。(MDN Web Docs – Array)

空の一次元配列を作るときは以下のようにします。

let array1 = [];            //方法1
let array2 = new Array();   //方法2

その他はArrayを参照。

3-7.範囲内のセルの値を取得する

毎回スプレッドシート上のシートから値を取得しているとAPI呼び出し回数が大変なことになります。そのため、予め配列などに値を格納してから処理をするほうが良いらしいです。

範囲内のセルの値を取得するときは以下のようにします。

function myFunction(){
    let book = SpreadSheetApp.getActiveSpreadSheet();       //現在のスプレッドシートを取得
    let sheet = book.getActiveSheet();      //スプレッドシート上のアクティブなシートを取得
    let values = sheet.getRange(1, 1, 10, 2).getValues();       //A1セルから10行2列の範囲のRangeオブジェクトを取得し、範囲内の値を取得する
}

複数のシートがあり、その内指定のシート中の範囲内のセルの値を取得する場合は、シートの取得の仕方を変えれば同じことができます。

function myFunction(){
    let book = SpreadSheetApp.getActiveSpreadSheet();   //現在のスプレッドシートを取得
    let sheets = book.getSheets();      //スプレッドシート上のシートを取得
    let values = sheets[0].getRange(1, 1, 10, 2).getValues();   //A1セルから10行2列の範囲のRangeオブジェクトを取得し、範囲内の値を取得する
}

getSheets()ではスプレッドシート内のシートをすべて取得できます。indexは0,1,2,…となりますが、これはスプレッドシートでのシートの並びとは必ずしも一致しないことに気をつけてください。

また、Rangeオブジェクトを得るgetRange()ですが、引数は次のようになっています。

getRange(範囲の左上のセルの行番号, 範囲の左上のセルの列番号, 範囲の行数, 範囲の列数);

範囲の行数、範囲の列数は省略可能です。省略した場合1が入ります。また、配列のindexと違って、セル番号は行、列どちらも1から始まることに気をつけてください。

getValues()の返り値は指定範内の値の二次元配列です。1行を指定した場合でも二次元配列となるので注意してください。

3-8.一次元配列内の要素を検索する

indexOf()を用いると可能です。

function myFunction(){
    let array1 = [1, 2, 3, 4, 5];   //一次元配列を用意する
    let index = array1.indexOf(3);  //3を検索する
    // indexには2が代入されている
}

3-9.最終行・最終列の取得

getLastRow()、getLastColumn()で取得可能です。

function myFunction(){
    let book = SpreadSheetApp.getActiveSpreadSheet();       //現在のスプレッドシートを取得
    let sheet = book.getActiveSheet();      //スプレッドシート上のアクティブなシートを取得
    const lastRow = sheet.getLastRow();     //最終行の取得
    const lastCol = sheet.getLastColumn();
}

3-10.ログ出力

途中の変数の値やオブジェクトの中身を知りたいときに使える。

Loggerを使うと次の通り。

Logger.log("Hello,World!");

またはconsoleを利用すると次の通り。

console.log("Hello,World!");

consoleの出力はStackDriver Loggingで確認できます。

3-11.シート上の指定の行を削除

deleteRow()またはdeleteRows()を使うとできます。

function myFunction(){
    let book = SpreadSheetApp.getActiveSpreadSheet();       //現在のスプレッドシートを取得
    let sheet = book.getActiveSheet();      //スプレッドシート上のアクティブなシートを取得
    sheet.deleteRow(index);     //indexは削除する行、deleteRow()は1行のみ削除
    sheet.deleteRows(index, rows)   //indexは削除する行、rowsは削除する行数、deleteRow()は複数行削除
}

非連続の行を複数削除する場合でもdeleteRows()のほうがパフォーマンスはいい気がします。

3-12.ダイアログを出す

Uiクラスを使う場合は次の通り。

function myFunction(){
  let ui = SpreadsheetApp.getUi();
  let result = ui.alert("メッセージ", ui.ButtonSet.OK_CANCEL);
}

Browserクラスを使う場合は次の通り。

function myFunction(){
    Browser.msgBox("メッセージ");
}

ボタンも色々あるのでレファレンスを参照してください。

4.おわりに

まだ実装することが色々あります。

このシステムはできるだけ人の手を介入させないようにしているので、作ってる側はすごく大変です。

頑張っていこう。ちなみにこのシステムに対する対価は一切ありません。

5.参考文献

• Google Apps Script Reference
• 第14回.複数のシートを扱う
• Google Apps Scriptコーディングガイドライン【随時更新】
• JavaScriptに参照渡し/値渡しなど存在しない
• MDN Web Docs – Array
• 【GAS GoogleAppsScript | スプレッドシート】セルの範囲を配列で取得する！
• 【GAS GoogleAppsScript | スプレッドシート】特定の列をキーとしてデータを取り出す！
• 【コピペで使える】GASでスプレッドシートの行や列を削除してみる
• GASでログ出力する2つの方法(Logger.logとconsole.log)の紹介と使い分け
• JavaScriptの行末セミコロンは省略すべきか
• Google Apps Scriptでダイアログを作るいくつかの方法と基礎知識