技術Note

最近、Google スプレッドシートに入力されたデータをwordpressに取り込める形にするという業務が有りました。

取り込み自体は優秀なプラグインがあったので問題なかったのですが、 5桁を超える行数の重複の削除や整形が必要だったので最初は死ぬかと思いました。

スプレッドシートの既存の機能だけでは難しかったり、手間が多いのでどうにかできないかと調べたところ、Goolge App Scriptというもので自動化で複雑なデータ整理もできるようです。

「でも難しいんでしょ…？」と思い調べたところ専用の関数や変数がある程度でJavaScriptの知識だけで思ったよりカンタンにできたので今回は紹介していこうと思います。

Google App Scriptの開始方法

Goolge App Scriptはスプレットシートのメニューバーの拡張機能の中にあります

自動化したい作業を関数として記述し、その関数を実行することでデータの整形や取得、既存の機能では難しい計算などをすることができます。

今回は実際にデータ整理に使った関数をベースに一部改善を加えたコードを使用して紹介しようと思います。

実際にデータ整理：重複データを削除

今回は例として上記のデータを整理していこうと思います。

今回のデータ整理の条件として

• 海ぶどうの列を削除（海ぶどうは果物ではなく海藻なので）
• 果物と生産地が同じ場合数字が若いものを残し重複を削除

という条件でデータを整理していきます。

実際は「A,B,Cの列が同一のものを番号にが〇〇を含むもの△△を含むものを優先して１列だけ残して削除」、「住所を都道府県、市区町村、それ以降にセルを分割」というもう少し複雑なことをやっていましたが今回は前者の条件を簡略化したものを例に行っていこうと思います。

おそらくJSがそれなりに書ける方なら今回の解説で応用してかけると思います。

コード

function duplication() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();// シートを取得
  var ssLength = ss.getDataRange().getValues().length; // シートの列数を取得（ループで使う）
  var deleteLine = 0;

  // 1 = 管理番号
  // 2 = 果物
  // 3 = 生産地

  // 比較元ループ
  for (let i = 1,len = ssLength; i <= len; i++){
      // 果物が海ぶどうの場合削除
      if ( ss.getRange(i,2).getValue() === '海ぶどう' ){
        ss.getRange(i,1,1,3).clearContent();
        continue;
      }
      
      // 管理番号が空欄の行はスキップ
      if (ss.getRange(i,1).isBlank()){continue;}

    //比較先ループ
    for (let j = 1,len = ssLength; j <= len; j++){     
      // 比較元より上の行はスキップ
      if (i >= j) {continue;}

      // 2列目、3列目が同じ場合 削除実行
      if (
        ss.getRange(i,2).getValue() === ss.getRange(j,2).getValue() &&
        ss.getRange(i,3).getValue() === ss.getRange(j,3).getValue()
      ){
        var compareBaseID = ss.getRange(i,1).getValue();
        var compareTargetID = ss.getRange(j,1).getValue();
        
        // 管理番号を比較し
        // 同じなら比較先を
        // 違うのであれば大きい方をクリア

        if ( compareBaseID > compareTargetID ){
          // 比較元の行をクリア
          ss.getRange(i,1,1,3).clearContent();
        }
        else if( compareBaseID < compareTargetID || compareBaseID === compareTargetID ) {
          // 比較先の行をクリア
          ss.getRange(j,1,1,3).clearContent();
        }
      }
    }
  }
}

重要なのは下記の3つのみで、残りはJavaScriptと全く同じです。

スプレッドシートの取得

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

値の取得

ss.getRange(i,2).getValue()

セルのクリア

ss.getRange(i,1,1,3).clearContent();

工程としては

1. シートの取得
2. データの比較と整理
   1. 海ぶどうの場合列をクリア
   2. 名前の欄が空欄であればスキップ
   3. 2,3列目を比較し両方同じであればIDを比較しIDが小さい方を削除、同じであれば比較先を削除

となります。

1.シートの取得

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

この記述で取得したシートをss という変数に代入しています。

なぜ.getActiveSpreadsheet()　のようにメソッドにアクセスしているかというと、SpreadsheetApp はスプレッドシートのアプリそのもの、getActiveSpreadsheet() でファイルを選択、getActiveSheet() でタブを選択しているからです。

取得した変数はss.getDataRange().getValues() のように更にメソッドを呼び出しセルの範囲の指定、データの取得などを行います。

2. データの比較と整理

あとは普段のjsと同じようにループやif文を記述します。

for (let i = 1,len = ssLength; i <= len; i++){
      // 果物が海ぶどうの場合削除
      if ( ss.getRange(i,2).getValue() === '海ぶどう' ){
        ss.getRange(i,1,1,3).clearContent();
        continue;
      }
      
      // 管理番号が空欄の行はスキップ
      if (ss.getRange(i,1).isBlank()){continue;}

			...

比較元のループで先に

1. 海ぶどうの場合列をクリア
2. 名前の欄が空欄であるかどうか

の確認を行い以降の処理をスキップします。 後述しますが無駄な処理は行わないように書くのが結構大事だったりします。

ss.getRange(i,2).getValue() === '海ぶどう' のようにss.getRange(i,2).getValue()でセルを指定し値を取得して比較を行います。

getRange() は引数が2つの場合セルを単体で指定します。 3つ以上の引数を設定することでセルを範囲で指定することできるので後ほど利用します。

条件に当てはまった場合はss.getRange(i,1,1,3).clearContent(); で範囲指定＆クリアして、continue; でその行のあとの処理をスキップします。

同じようにss.getRange(i,1)で値を取得しisBlank() でセルが空かを確認し空だった場合はスキップしています。

for (let j = 1,len = ssLength; j <= len; j++){・・・　の中も同様に値を取得、比較、セルのクリアを行っています。

実際に実行したときにわかるのですが値の取得や更新に時間がかかるため、取得や比較の回数をできるだけ少なく済むようにしています。

時間がかかるとタイムアウトしてしまうため、今回のような数十行ならまだしも数百行あるような場合は途中で処理が終了してしまい、また最初からになり永遠に最後までたどり着けなくなってしまいます。

それでも処理が最後まで行えない場合はスプレットシートのデータクリーンアップで可能なものを先に行っておいたり、下記の記事のように数十行ごとに列を分割し処理を行います。

https://hajiritsu.com/gas-timeout-error-routine/

今回の処理も「2列目と3列目の内容が重複している場合一行を残して削除」だけであればデータクリーンアップのみで対応できるのですが、それでは今回説明する意味がないため「海ぶどうの場合列をクリア」や「管理番号が若い方を残す」などの条件を加えています。

関数を実行

完成したら下記画像の赤下線「マクロをインポート」から先程作成した関数を追加します。

追加が完了したら青下線部分に追加されるので、クリックするだけで好きなときに実行できます。

実際に実行した後のスプレッドシートが以下のようになります。

今回のような30行程度なら手動でも可能でしょうが、最初に話した実務で行ったデータ整理は47シート合計数万行という量だったので、この方法を調べて実装するまでを含めても１０倍以上の効率化にはなったと思います。

同じように膨大なデータを整理することになった方にはぜひおすすめしたい機能でした。

また面白い機能を見つけたら紹介しようと思います。