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

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

» 2017年10月18日 05時00分 公開
[高橋宣成プランノーツ]

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セルの「250円」というデータは、文字として「単位」が含まれています。また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 データが数値かどうかをチェック
       1|2|3|4 次のページへ

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。