「計算式エラーで残業確定……」、GASで入力データのチェックツールを作成：働く価値を上げる“VBA／GAS術”（4）（1/4 ページ）

1つのGoogleスプレッドシートを複数人で共同編集するとき、想定していた形式とは違うデータを入力されると、不備が発生してしまう場合があります。そのような問題を、GASで解決するにはどうすればいいのでしょうか。

[高橋宣成，プランノーツ]

Excel VBAとGAS

　業務で発生するちょっとした面倒くさい業務。特定の操作を繰り返すだけなので、今のままでもいいけど、楽になったらいいなと思うこともあるのではないでしょうか。例えば業務を自動化すると時間が空くため、他の業務に時間を充てたり、残業時間を減らしたりできるかもしれません。

　本連載「働く価値を上げる“VBA／GAS術”」では、業務を効率化する手段として「Microsoft Excel」（マイクロソフトエクセル）で使える「VBA」（Visual Basic for Applications）と「G Suite」の「GAS」（Google Apps Script）の使い方を説明し、よくある業務課題の解決策を紹介します。

GASで入力データのチェックツールを作る

　Googleスプレッドシート（以下、スプレッドシート）はクラウド上にあり、リアルタイムでの共同編集が可能です。そのため、チームでデータやリストを共同で作成するときに便利です。しかし、多くのメンバーが入力する場合、「想定していない形式のデータが入力されるケース」が増えてしまう可能性があります。結果として、スプレッドシートを使用する上で、下記のような困ったことが起こり得ます。

• 数値を入れるべき欄に「円」や「個」などの単位が入力されているため、合計金額の計算式でエラーになる
• 日付欄に「2017/8/3〜2017/8/5」や「いつでも」など、日付形式で入っていないためフィルターが使えない

　今回は、これを解決する手段として、GASを使ってスプレッドシートの入力データを検査するスクリプトを作成していきます。なお、このスクリプトは、前回のVBA編で作成したツールと同様の機能を持つものですので、合わせて読むと理解が深まるでしょう。

　またGASの基本については、連載第2回の「プログラム自動実行で仕事を楽に！　APIでSlackなどと連携できる『Google Apps Script』とは」を参照してください。

備品購入リストの例

　例として、以下のような備品購入リストをスプレッドシートに用意しました。

図1　備品購入リスト

　チームのメンバーが各自入力してリストを作成していき、いずれかのタイミングで合計金額を集計することにします。C列の数量とD列の単価を、数式で乗算して金額を求めようとすると、以下のように「#VALUE!」とエラー表記になってしまいます。

図2　計算式がエラーになる

　C3セルの「3個」、D6セルの「２５０円」というデータは、文字として「単位」が含まれています。またD6は全角文字で数字が入力されています。そのためセルのデータとしては「文字列」と見なされているので、「数値」として計算することができません。

GASでデータチェックツールを作る

　そこで、この備品購入リストのC2からD6の範囲を検査し、数値ではないデータがあった場合、そのセルの背景色を黄色に変更するGASのコードを作りました。

function checkNumber() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var i=2;i<=6;i++){
    if(!isFinite(sheet.getRange(i,3).getValue())){
      sheet.getRange(i,3).setBackground('yellow');
    }
    if(!isFinite(sheet.getRange(i,4).getValue())){
      sheet.getRange(i,4).setBackground('yellow');
    }
  }
}

　スプレッドシート「備品購入リスト」のコンテナバインド型（つまりスプレッドシートのメニューから「ツール」→「スクリプトエディタ」でスクリプトを作成する）で上記スクリプトを入力します。その上で、関数「checkNumber」を実行すると、以下のようにC2からD6の範囲で数値でないデータを持つセルの背景色が黄色に変更されます。

図3　データが数値かどうかをチェック