【コピペで使える】GASでスプレッドシートの行や列を削除してみる

2021 4/21

2020年9月23日 2021年4月21日

URLをコピーする

URLをコピーしました！

スプレッドシートでデータ管理をしていると大量のデータが溜まっていきます。もちろんそのまま保存して行くこともいいですがもう使うことのない不要なデータが多すぎてしまうのも検索時間や起動時間が長くなってしまう可能性もあります。

そこでデータ行や列を削除していくのですが、少ないデータでしたら検索して消していくという事も出来ます。

しかしながらせっかくGASを学んでいるので、今回紹介するdeleteRow deleteColumnプログラムでさくっと消していきましょう。

複数行を一気に削除する方法。ループを使って削除する方法、トリガーを使って定期的に要らない情報を削除する方法などもお伝えします。

今回はサンプルスクリプトとサンプルスプレッドシートを用意しました。

スプレッドシートの上部メニューにはでも処理というカスタムメニューを追加しています。デモ処理をクリックすると、関数が5つ用意されていることが確認できると思います。

①deleteRow/deleteColumnデモ処理
②deleteRows/deleteColumnsデモ処理
③上から下の行へループ処理で行を削除
④下から上の行へループ処理で行を削除
⑤処理対象シートを初期化

設定シートをクリックすると、同じ名称が書いてあるのが確認できると思います。

①はC2セルとC3セルに記載されている列と行を処理対象シートから1列/行ずつ削除する処理になります。

②はC6セルC7セルで削除開始位置を設定し、E6セルE7セルでそこからいくつの列や行を削除するかということを設定し、複数行を一括で削除する処理になります。

③はC10で設定した項目名の列の値がC11セルの値に等しい行を上から下の行へ順番に削除していく処理になります。（本記事で後述しますが、この処理は想定通りには機能しません）

④はC10で設定した項目名の列の値がC11セルの値に等しい行を下から上の行へ順番に削除していく処理になります。

⑤は処理対象シートを初期の状態に戻すための処理になります。

実際に動作を確認できますので、確認してみてください。一番最初に実行する時にスクリプトに自分の代わりに処理をさせることに同意をする必要がありますので、『無料のGmailアカウントでGASに初回の実行許可を与える方法』の手順を確認しながら許可を与えてください。

1つの行や列を削除するdeleteRow/deleteColumn

今回は以下のスプレッドシートのデータを用いてスクリプトを作成していきます。内容としては１行目は項目２行目以降にデータを入れています。・・・は省略しているので本来データ入っているものとします。

今回は先程のサンプルスクリプトの①の処理に近いものになります。削除対象の列番号と行番号を指定して処理を行う方法になります。

deleteRow(rowPosition)
deleteColumn(columnPosition)

『引数に入れる値』

rowPosition（数値） — 削除対象の行番号
columnPosition（数値） — 削除対象の列番号（A列から順番に1となります）

deleteRow(rowPosition)/deleteColumn(columnPosition)サンプルコード

対象の列や行を削除する

//列や行を指定して削除するコード
function sampleCodeForDeleteRowAndDeleteColumn() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  sheet.deleteRow(4);
  sheet.deleteColumn(5);
}

上記のサンプルスクリプトでは、現在開いているシートの４行目と５列目を削除する処理が書かれています。上記スクリプトをコピーして使う場合は、getActiveSheetというメソッドがスプレッドシートにバンドルされているスクリプトエディタでしか実行できませんので、必ずスプレッドシートの上部メニューから『ツール』→『スクリプトエディタ』をクリックして貼り付けてください。

deleteRow/deleteColumnの引数に行や列の番号を入れるだけなのでとてもシンプルに出来ます。次に複数行・列を削除する場合はどうすればいいのか説明していきます。

複数の行・列を削除するdeleteRows　deleteColumns

私の方で用意したスプレッドシートでは、上記の処理を行うと以下のスクリーンショットのようになります。

今度は1行目の4行目「部品名D」5行目「E」と4列目のTELと5列目の管理番号の4・5行目をそれぞれ削除していきます。サンプルスクリプトでは②の処理に近いのがこちらの処理になります。

deleteRows(rowPosition, howMany)
deleteColumns(columnPosition, howMany)

『引数に入れる値』

rowPosition（数値） — 削除対象の行番号
columnPosition（数値） — 削除対象の列番号（A列から順番に1となります）
howMany（数値） — 削除する行/列数

deleteRows(rowPosition, howMany)/deleteColumns(columnPosition, howMany)サンプルコード

対象の複数列や複数行を一括で削除する

//複数列や複数行を一括で削除する関数
function sampleCodeForDeleteRowsAndDeleteColumns() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  sheet.deleteRows(4, 2);
  sheet.deleteColumns(4, 2);
}

sheet.deleteRows(4, 2)という部分では、4行目と5行目を削除するという処理をしています。
sheet.deleteColumns(4, 2)という部分ではD列とE列を削除するという処理をしています。
単数行列削除との違いは複数のsがつくところと行数・列数が必要になります。一気に並んだデータを削除することが可能ですが、これですと飛び飛びのデータを削除することが出来ません。

では該当する飛び飛びになったデータを削除するにはどうしたらいいのか次で説明します。

該当するデータを順番に削除していく方法

ループを使った削除方法（正しい削除手順）

ループ処理を使用して条件に合致する行や列があれば順番に削除していく方法がこれから紹介する方法です。サンプルスクリプトでは④の処理に近いと思います。

この方法であれば飛び飛びのデータであっても削除していくことが出来ます。しかしながら行・列を削除する際にはループの方法に注意が必要です。

今度はスプレッドシートのF列状態の廃番というセルがあれば行を消すプログラムを作成していきます。以下のコードを実行してみてください。

ループ処理で条件に一致する行を削除する

//ループ処理で条件に一致する行を削除する関数（うまく処理できる）
function sampleCodeForDeleteRowCompleteLoop() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var length = data.length;
  
  for (var i = length - 1; i >= 1 ; i--){
    var keyword = data[i][5]; 
    if(keyword === "廃番"){
      sheet.deleteRow(i + 1);
    } 
  }
}

変数dataで全てのデータを配列で取得しlengthで最終行を取得しています。

配列で取得しているのでdeleteRow(i + 1)の部分は必ずプラス１して通常の行・列番号にすることを忘れないようにしてください。

このようにスプレッドシートには廃番の行が削除されています。

ループを使った削除方法（誤った削除手順）

では以下のコードでプログラムを起動してみるとどうなるでしょう。サンプルスクリプトでは③の処理に近いと思います。

ループ処理で条件に一致する行を削除する

//ループ処理で条件に一致する行を削除する関数（うまく処理できない）
function sampleCodeForDeleteRowsIncompleteLoop() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var length = data.length;
  
  for (var i = 1; i <= length -1 ; i++){
    var keyword = data[i][5]; 
    if(keyword === "廃番"){
      sheet.deleteRow(i + 1);
    } 
  }
}

結果はこうなります。廃番を対象にしているはずなのに廃番が残ってしまっていますね。さらにFなどの使用と記載されている行まで削除されてしまいました。

削除する場合の注意点（逆ループの方法）

先ほどの最初のコードは逆ループを使用し、2番目のコードは通常のループを使用しました。行・列を削除するとそれ以降の行・列は繰り上がっていきます。

例えば3行目のデータを削除すると4行目のデータは3行目になります。ループ処理では1つの処理をするごとに変数がカウントアップしていきますので、3行目の処理をした次は4行目の処理をしようとします。
データが削除されていなければ、4行目のデータはそのままあるはずですが、3行目が削除されてしまったため、4行目には5行目のデータが入ってしまうということが起こり、上の行から順番に通常のループを行うと対象のデータがあるたびに順番がずれて予期せぬ結果が出てしまうことになります。

ですので、スプレッドシートの行や列をループ処理で削除する場合は下の行から上へ、あるいは右の列から左へデータを削除するループを作成する必要があります。

このように大きい行や列から小さい方へループすることによって削除行・列があっても順番をずらすことなく確認をすることが出来ます。