g前々から作っていた講師を検索するツールですが、現在Googleフォームから得られたデータをGoogleスプレッドシートに保存し、そこからデータを整形するスクリプトを作成しているところです。
Google Apps Script(GAS)を用いて実装していますが、JavaScriptすら触ったことがないので初心者実装になっています。
作成するスクリプトは
- フォームから名簿を作成する
- データが更新されたらExcelに渡す表を作成する
- Excelに渡す表から退職者を削除する
今回は表から退職者を削除するスクリプトを組みました。
目次
1.やりたいこと
上のような表から退職者だけを削除したいです。ただし、退職者は次の図のように管理されているものとします。
講師名はテストデータのため適当です。在籍フラグ1は在籍、0は退職としています。
2.スクリプト全体
| //メイン | |
| function taisyokuMain() { | |
| //開始前にポップアップを出す | |
| let okbutton = startAlart(); | |
| if (okbutton == 0) return; //キャンセルを押されたら終了する | |
| //bookをactiveにする | |
| let book = SpreadsheetApp.getActiveSpreadsheet(); | |
| /*book内すべてのシートを取得 | |
| 0.anser(フォーム回答を貯める場所) | |
| 1.enrolledFlag(在籍/退職名簿) | |
| 2.toExcel(Excelに流すデータ)*/ | |
| let sheets = book.getSheets(); | |
| //toExcelの講師番号の列だけ取得(A2からデータ取得) | |
| let exceldata = getData(sheets[2], 1); | |
| //enrolledFlag全体を取得(検索対象) | |
| let retiredTutors = getData(sheets[1], 3); | |
| //flagdataから退職済みの講師番号だけを配列として取得(検索対象) | |
| let retiredTutor = []; | |
| for (let i = 0; i < retiredTutors.length; i++) { | |
| //退職済みなら在籍フラグは0に設定している | |
| if (retiredTutors[i][2] == 0) { | |
| //退職済みなら1次元配列の末尾に追加 | |
| retiredTutor.push(retiredTutors[i][0]); | |
| } | |
| } | |
| //検索と削除 | |
| eraseTutor(sheets[2], sarchIndex(exceldata, retiredTutor)); | |
| //処理後に終了のダイアログを表示する | |
| endAlart(); | |
| } | |
| //シートから必要な列だけ抜き出す関数 | |
| function getData(sheet, row) { | |
| let len = sheet.getLastRow(); | |
| return sheet.getRange(2, 1, len - 1, row).getValues(); | |
| } | |
| //toExcel中の退職済み講師のindexを検索する関数 | |
| function sarchIndex(exceldata, retiredTutor) { | |
| //削除対象を格納する一次元配列 | |
| let deleteTutor = []; | |
| //retiredTutorを参照して、退職ならばtoExcelから該当講師の行を削除する | |
| for (let i = 0; i < exceldata.length; i++) { | |
| let index = retiredTutor.indexOf(exceldata[i][0]); | |
| if (index != -1) { | |
| deleteTutor.push(i + 2); //indexと行数は2ずれるため補正 | |
| } | |
| } | |
| return deleteTutor; | |
| } | |
| //sarchIndexで得られたindexを削除する | |
| function eraseTutor(sheet, deleteTutor) { | |
| //削除対象のindexの配列を降順にソートする | |
| deleteTutor.sort((a, b) => { return b - a; }); | |
| //indexが大きいものから削除していく(deleteRowは1行削除すると上詰めされるため下から削除していく) | |
| for (let i = 0; i < deleteTutor.length; i++) { | |
| sheet.deleteRows(deleteTutor[i]); | |
| } | |
| } | |
| //開始前のポップアップを出す | |
| function startAlart() { | |
| let ui = SpreadsheetApp.getUi(); | |
| let result = ui.alert("リストから退職済みの講師を削除します", ui.ButtonSet.OK_CANCEL); | |
| if (result == ui.Button.OK) { return 1; } | |
| else { return 0; } | |
| } | |
| //処理後のポップアップを出す | |
| function endAlart(){ | |
| Browser.msgBox("リストから退職者を削除しました") | |
| } |
by Mainって関数名に入っているのに全然Main感ないのはごめんなさい。
3.説明やメモ
JavaScriptやGASを書いたことが初めてなので、書いているときに調べたことや自分の実装の説明などを書いていきます。
3-1.VScodeでGASを編集する
“VSCode GAS”ってググればいくらでも出てくるので説明は省略します。
Googleが提供しているGASをローカルで管理できるようになるCLIツールであるclaspを導入してloginなどをすれば使えるようになります。
コマンドを実行したときに、実行開始したみたいなレスポンスが返ってくるまでに間があるのが気になる。
3-2.GASのレファレンス
3-3.変数の宣言
var / let / constの3種類があるそうです。letは再宣言不可・ブロックスコープで、varは再宣言可能、関数スコープになります。
varで調べると”非推奨”という単語とともに検索結果がでてきますし、入門書もvarを使わなくなってきたとかなんとか。
3-4.独自関数
受け取った数を10倍にして返す関数を作ってみます。
function myFunction(number){
return number * 10;
}
C/C++の癖で引数の部分に変数の型を書かないように気をつけましょう。
また、引数ですがオブジェクトを指定することもできます。
C++の考え方ですとこれは値渡しなのか参照渡しなのかポインタ渡しなのかと考えないといけませんが、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でダイアログを作るいくつかの方法と基礎知識