【AIと一緒に】Googleスプレッドシート 行削除で学ぶ GASの繰り返し処理 2

GASネタ 前回の続きです。

前回は Googleスプレッドシートで 「A列にチェックがついている行を削除する」というGASとしてはシンプルな処理をお題として、無料で使える代表的な 3つのAIチャット

• ChatGPT

• BingChat （BingAI）

• Google Bard

こちらの活用をメインに、初心者が GASのコード作成を進める際のポイントなどを書きました。

↓ シリーズ前回の note

前回のGASのコードを確認しよう

まずはこれまでの流れをおさらいしつつ、前回は詳しく触れられなかったコードの中身を少し解説しておきましょう。

前回のおさらい

こちらのお題に対してGoogle検索や マクロの記録、そして 3つのAIチャットに同じ相談をしたところ、 Google Bardの回答が 最も 正解に近く、これを1カ所だけ 修正した以下のコードで処理は実現することができました。

//Bardの回答 修正
function delckline() {
  //チェックボックスの位置(列)
  const CKPOS = 1;

  //アクティブなスプレッドシートとシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();

  //最終行を取得
  const lastRow = sh.getLastRow();

  //最終行から 1 ずつ減らしながら、チェックが入っている行を削除
  for (let i = lastRow; i >= 1; i--) {
    //チェックが入っている場合
    if (sh.getRange(i, CKPOS).getValue()==true) {
      //行を削除
      sh.deleteRow(i);
    }
  }
}

しかし、コードがド定番の for文によるループ処理ってのは、初心者を脱したい人からするとクールさに欠けるなぁってのと、

この通り、あまりにも処理が遅いって問題があり、

• コードをもっとクールにしたい

• 処理をもっと早くしたい

これらを解決したい！ ってとこまでが、前回の流れです。

deleteRow と deleteRows

少しコードを見ていきましょう。実際に行を削除する処理は

この部分ですね。sh(シート）に対して deleteRow( 行番号 ) とすることで、そのシートの 〇行目を削除することができます。

ただし、行番号 のところは 1つの数値で単体の行を指定する必要があります。この処理で削除できるのは1行だけです。

つまり

こんな指定は出来ません。

ちなみに deleteRows というメソッドが別にあるんですが、こちらは

deleteRow( 行番号 , 行数)

という書き方で使うもので、〇行目から×行分を削除する といった形で、連続する複数行をまとめて削除する際に使うものです。

今回のように飛び飛びの行を削除する際には適しません。

この辺りの詳細は 他のサイトを参考に

というわけで、飛び飛びの複数行を削除する為には deleteRow を繰り返し実行する必要があります。

for文はループ（繰り返し）の基本

この繰り返し処理部分の記述が

ここですね。この部分は

このようになっています。

最終行から開始して i-- していく逆ループを使っているのは、行削除という処理で使われる手法です。

普通に 1行目から開始して下に処理を繰り返した場合、1行目を削除した時点で、もともと２行目だった行は１行目に変わってしまいます。

1行目の処理が終わったので +1で次に2行目の処理に移ってしまうと、元々2行目だった行は処理対処からも漏れてしまうわけです。これを防止する為に最終行から 上に処理を回す逆ループを使っています。

これについては前回、AIにも回答してもらいましたね。

for文は基本中の基本なんですが、逆ループもよくあるパターンですし、GASを書いてると for文は飽きるほど登場するんで、だんだん違う書き方したいなーってなってきます。

また、スプレッドシートでのGASは〇行〇列の範囲を取得する 二次元配列処理が多い為、forの 入れ子が登場するケースが多く、どうしてもコードが冗長になりがちです。

そうすると 違う書き方に手を出したくなるんですが、基本である for文を理解せずに応用に手を出すのは NGです。

しっかり for文が書けるようになってから次のステップ（配列メソッド）に進みましょう。

あと、コードの見た目はともかく、同じ処理だと 意外と基本の for文の方が処理速度が早いってことも多いです。データ量が膨大だと差がでることもあります。

AIに GASのコードをクールにして処理速度を上げてもらおう

基本を押さえた上で、AIチャットにコードを投げかけて修正してもらうえるかを試してみましょう。

なお、最近のAIチャットは過去のやりとりを履歴として保持しており、続きとしてチャットを再開することも出来ますが、今回は3つの AIを正しく比較するために 新規スレッド で前回の最終コードを渡してコード修正依頼をする形式とします。

AIにリクエスト1「GASのコードをクールにしたい」

あえて プロンプト等のお作法は意識せず、ふわっとした質問でいってみましょう。

ChatGPTの場合

うーん 変数名を変えたりコメントを入れたりで、読みやすくなったといえなくもないですが、その分冗長になった感も。。根本は変わってませんね。

こちらの希望するクールとはちょっと違いました。

BingChatの場合

コードを返してくれません。。

「なんでも相談しろよ」という上司に仕事の相談をしたらアドバイスじゃなく説教されたような気分ですｗ

やはり「クールに」という抽象的なリクエストが、AIへの依頼としてよくないのかもしれません。

Google Bardの場合

クールかは微妙ですが、新しいコードを提示してくれました。
やはり GAS関連はBardが強いのか。

Bardが返した行削除コード（クールに修正版）を見てみましょう。

// 関数名を変更する
function deleteCheckedRows(checkBoxPos) {
  // チェックボックスの位置を関数名で参照できるようにする
  const CKPOS = 1; //A列なんでここだけBardの回答を1に修正

  // アクティブなスプレッドシートとシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();

  // 最終行を取得
  const lastRow = sh.getLastRow();

  // チェックされた行を配列として取得する
  const checkedRows = Array.from(sh.getDataRange().getValues(), row => row[CKPOS] === true);

  // 配列からチェックされた行を削除する
  for (const row of checkedRows) {
    sh.deleteRow(row);
  }
}

Arrya.from という配列メソッドとアロー関数を使ったそれっぽい（初心者っぽくない）コードではありますね。

でも、Array.from は 文字列やオブジェクトから配列生成するケースで使う印象ですし、配列から Array.from しても ぶっちゃけ map と同じなんで、今回のケースで登場する必要性はありませんね。

そもそも、これはコード間違ってるんで 動きません。

直接実行したいので const CKPOS = 1 としても

このようにエラーになります。ぶっちゃけ

// チェックされた行を配列として取得する
  const checkedRows = Array.from(sh.getDataRange().getValues(), row => row[CKPOS] === true);

この部分はツッコみどころが多すぎです。

これをベースに 動く形に修正してもいいんですが、結局あまりクールにはならないんで、今回は割愛して後で mapを使う方法を紹介します。

AIにリクエスト2「GASのコードを処理速度が早くなるように修正して」

「クールにしたい」はふわっとしすぎだったのか、うまくいきませんでした。それでは

これでいってみましょう。

ChatGPTの場合

なるほど。

1. 行の削除をまとめて行う：各行を削除する代わりに、削除対象の行を一度にまとめて特定し、それらを一括で削除します。これにより、ループ内で行を削除する回数が減少し、処理速度が向上します。

2. データを一括で読み取る：各行でセルの値を1つずつ読み取るのではなく、データを一括で読み取り、処理することで効率が向上します。

言ってることは正しそうですが 回答のコード

function deleteCheckedRows() {
  // チェックボックスの位置（列）
  const CKPOS = 1;

  // アクティブなスプレッドシートとシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();

  // データ範囲を一括で読み取る
  const data = sh.getRange(1, CKPOS, sh.getLastRow(), 1).getValues();

  // 削除対象の行を格納する配列
  const rowsToDelete = [];

  // チェックが入っている行を特定
  for (let i = 0; i < data.length; i++) {
    if (data[i][0] === true) {
      // シート上の行番号に変換して配列に格納
      rowsToDelete.push(i + 1);
    }
  }

  // 行を一括で削除
  if (rowsToDelete.length > 0) {
    sh.deleteRows(rowsToDelete);
  }
}

これを実行するとエラーになります。

そりゃそうです。最初に書いた通り、deleteRowsは まとまった複数行を一括削除するメソッドであって、このような飛び飛び行の一括削除は出来ませんし、配列を引数にとれません。

考え方は合ってますし、2番目の 「データを一括で読み取る」 で getValues()を使うところまでは正しいです。惜しい。

BingChatの場合

うーん、なんだこりゃ。。

このコードはAIによって作成されたものじゃなくて、単なる質問のコードのオウム返しなんですが・・・。

「修正する方法があります」といいつつ

これで終わり・・・だと・・。。

やはり Microsoftは GAS（Google）と相性悪いのか、それとも完全に嫌がらせなのかｗ

Google Bardの場合

今回もそれっぽいコードを返す Bard。

さも稼働テストをしました風に回答してますが・・・

さてさて、実行したらどんな速度が出るのかな

ビズリーチ！！（ビズリーチは一切関係ありません）

Bard も結構GASのコードの回答ミスありますね。

素人みたいな getRange() だけやって getValue() なしで 値が取れてると勘違いしてるミスですね。。

ちなみに

 //修正前
const checkedRows = sh.getDataRange().filter(row => row[CKPOS] === true);

//修正後
const checkedRows = sh.getDataRange().getValues().filter(row => row[CKPOS-1] === true);

このように修正してあげると 動きはしますが、結局これで得られる checkeRows を使って行削除処理を走らせても

タイトル行含め 上から10行削除されるだけです。

うーん、今回は三賢者そろって 微妙な回答でしたね。

もちろん、質問の仕方（プロンプト）を工夫すれば 求めている回答を得られる可能性もありますが、そこで時間費やしてAIのご機嫌を取るのもなんだかなーって感じですし。。

結局 回答が間違っているリスクは常にあるわけですから、コーディングにAIチャットを活用する場合も 回答のコードがある程度理解できる基礎知識は必要ってことです。

GASのコードをクールにして処理速度をあげる（人力）

さて、AIの回答では思うような結果が得られなかったので、人力で取り組むことにしましょう。

//元のコード
function delckline() {
  //チェックボックスの位置(列)
  const CKPOS = 1;

  //アクティブなスプレッドシートとシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();

  //最終行を取得
  const lastRow = sh.getLastRow();

  //最終行から 1 ずつ減らしながら、チェックが入っている行を削除
  for (let i = lastRow; i >= 1; i--) {
    //チェックが入っている場合
    if (sh.getRange(i, CKPOS).getValue()==true) {
      //行を削除
      sh.deleteRow(i);
    }
  }
}

このコードをクールで処理速度を早く修正するには、どこをどうなおせば良いのか？

コード修正のポイント

AIの回答は コード自体は動かなかったりイマイチでしたが、実は 説明の中にはヒントがありました。

この辺りをコードに落としこめれば良さそうです。

元のコードで特に問題なのが

sh.getRange(i, CKPOS).getValue()

forの中で、ひたすら getValue() を繰り返しているココです！

まずはこれを getValues()で 1回でまとめて値を取得 する書き方に変更してから処理を進めましょう。これだけでも速度が結構変わります。

取得した配列は、配列メソッド で処理すればクールさアップですね。

コードを短くしていくポイント シートとセル範囲

GASのコードを短くシンプルに記述すると、クールな感じで 素人っぽさが抜けます。

たとえば元コードの最初の部分

  //アクティブなスプレッドシートとシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();

このように書いてますが、今回の処理は 開いているシート （アクティブシート）だけの処理なので、ss アクティブなスプレッドシート を使いません。

だから、

  //アクティブなシートを取得
  const sh = SpreadsheetApp.getActiveSheet();

このように書くことができます。

手動実行で開いている一つのシートだけの処理なら、このようにスプレッドシートの取得をすっ飛ばすことが可能です。

※時間トリガーで処理する場合は、シート名でシートを取得する必要があります。

そのうえで チェックボックスの列の値をこのように取得するか、

  //チェックボックス列の値を取得
  const values = sh.getRange(1,1,sh.getLastRow()).getValues();

または データの列数が多くなければ

  //まとめて値を取得
  const values = sh.getDataRange().getValues();

このようにまるっと取得して、後で処理の際に チェックボックスの列だけを使ってもよいです。

取得できる値はこんな感じの違い。

今回はよりコードが短くて済む getDataRange() の方を使うことにしましょう。

getDataRange()はシートから A1を起点としてデータがある範囲をまるっと取得するメソッドです。

そこから getValues() で取得した値は、右のような 二次元配列となります。（配列の中に配列がある状態）

これが 変数 values に格納されました。

値の取得はこの1回きりの getValues()だけなので、処理時間も大幅に短縮できています。

GAS（JavaScript）の map と filter

最終的にdeleteRow(行番号) で削除することになるので、この二次元配列（values）から 各行の先頭部分が true となっている 削除対象の行番号だけ欲しいわけですが、どのように取得すればよいか？

ここで使えるのがシート関数でも大活躍の map、そして filter です。

手順としては

これをチェーンメソッドで繋げて、 結果（返り値）を rowNum に格納という流れをやってみましょう。

配列は 0スタートなので、行番号に変換する場合は +1が必要となります。

また列の方も配列化で 0スタートとなるので、1マイナスして 

row[CKPOS-1] (今回の場合は結果的に row[0] ）

が チェックボックスの値となります。

↓ こんなコードになります。

const rowNum = values.map((row,i) => row[CKPOS-1]==true? i+1:"").filter(v => v);

処理の流れはこんな感じ。

map の中で使っているのは　=> で記述する アロー関数 と if文のショートハンド 三項演算子です。

アロー関数 や 三項演算子も 使えるとクールな印象ありますね。

mapでは要素の数を増減できないので、map処理後に filterで空を除去する必要があります。それが以下の部分。

array.filter( v => v )

これはよく使われる空白除去の書き方ですね。

v => v 

え？ってなりますが、右の v は v を 真偽評価していて、文字列や 0以外の数値が true と扱われる ことを利用しています。

これによって 削除した 行の行番号を取得することが出来ました。

初心者がハマりがちなのが、配列メソッドの引数 (今回だと row,i とか vとか）を 変数と混乱して、宣言もなにもしてないのにいきなり登場した？？となるパターン。

でも、これ今は シート関数の LAMBDA ヘルパー関数の MAPでも出てきますよね。

最初は理解が追い付かないかもしれませんが、繰り返し書いていくうちになんとなく腹落ちしてきて、自然に使えるようになります。

getValues()で取得した 二次元配列から、削除対象の行番号 の一次元配列への変換が1行で記述できました。クール！

GAS（JavaScript）の flatMap

この map して filter でも十分なんですが、実はこれだと2回処理を回してるんで、やや非効率だったりします。

全てのケースで出来るわけではないのですが、今回は最終的に 行番号の一次元配列を得られれば良いので、flatMapというメソッドを使うことで一発処理が可能です。

flatMapは、map処理をしたうえで 配列をフラット化 するメソッドです。

さきほどの map して filter のコードは、flatMapで以下のように書けます

const rowNum = values.flatMap((row,i) => row[CKPOS-1]==true? i+1:[]);

map の アロー関数の部分がちょっと変わっただけですね。

これは flatMap の方は A列が true では ない時に 空配列 [] を返すことで、フラット化処理がされる時に、その部分の要素を削除（filterの効果）できるというテクニックを使っています。

こっちの方が短くて、よりスマートですね！

[ 2, 5, 12, 17, 18, 19, 23, 26, 27, 30 ] という 削除対象の行番号の配列をクールに取得出来ました～。

GAS（JavaScript）の reverse と forEach

削除対象の行番号配列 rowNum を使って、いよいよ 行削除の処理を回します。

処理を繰り返す時に 配列メソッドを使う場合は、forEach の出番です。

ただし注意すべきは、forの時に最終行から 逆順で処理したのと同じように 下の行から削除処理をしないと ズレが生じてしまうという点。

 MDNの説明にもありますが、forEachは

というもので、（indexの）降順に処理は出来ません。

よって、forEachの前に rowNum を反転させておく必要があります。

配列メソッドには、まさに反転術式を行える reverse があります。（これシート関数にも欲しいｗ）

さきほどの flatMapの後ろにチェーンメソッドでこれを加えることで

[ 30, 27, 26, 23, 19, 18, 17, 12, 5, 2 ]

このような 削除対象の行を下から並べた配列を得ることができました。

あとはこの rowNum を使って

rowNum.forEach(row => sh.deleteRow(row))

ここも同じくアロー関数で 行削除の処理を書けばよいですね。

【回答1】スマートで処理速度の早い 行削除 GAS

一連の流れをまとめて、完成したコードがこちら。
スマートな感じはありますね。

//変更後のコード
function delckline2() {
  //チェックボックスの位置(列)
  const CKPOS = 1;

  const sh = SpreadsheetApp.getActiveSheet();
  const values = sh.getDataRange().getValues();
  
  //チェックが付いている削除対象の行を逆順で取得
  const rowNum = values.flatMap((row,i) => row[CKPOS-1]==true? i+1:[]).reverse();

  //行を削除
  rowNum.forEach(row => sh.deleteRow(row));
}

速度はどうでしょうか？ 動かしてみましょう。

爆速とはいきませんが、

元のコード 5.8秒　→　改良後 4.3秒

と 1.5秒短縮されています。

データ量が多ければさらに差は大きくなり、500行6列のデータで 三分の一が チェックが入っていた場合は

このように

元のコード 77.6秒　→　改良後 42.0秒　（差 35秒）

と結構早くなったのがわかります。

結局ほぼ自力となりましたが、クールで処理速度の早い GASが完成しました～。

ちなみに、こちらのコードは マクロから、もしくは ボタン等にスクリプトを割り当てて手動での実行を想定しています。

チェックを付けたら 自動削除するように onEdit で動かしたい、もしくは時間トリガーで 毎日夜間にバッチで処理したいといった場合は、コードを修正する必要があります。

別アプローチでの 行削除処理を考える

GASの紹介、検証記事であれば 上記で十分なんですが、mir のnoteは スプレッドシートのフル活用（＋お遊び）を目的としています。

本当に上で作成したコードで十分なのか？
これ以上早くはならないのか？

もう少し考えてみましょう。

行の一括削除は フィルタで出来る

さんざん引っ張りましたが、実はこの一括行削除は GASなしでもサクっと出来る処理なんです。

Excelでも出来るテクニックなので知ってる人も多いかと思いますが、それはフィルタを使う方法です。

上のようにチェックされている行だけを表示させることは、フィルタを使えば簡単にできますね。実は、この状態で

このように表示されている行を下まで選択して 削除すると、フィルタで表示されているチェックが入った行だけが削除されます。

間にある非表示のチェックの付いていない行は削除されず、フィルタを解除するとチェックされていない行だけが残るんです。

これで十分な気もしますが、この手順を マクロを記録して コードが生成できるか確認してみましょう。

前回 Ctrlを押しながら選択した複数行の削除をマクロ記録した際は、手作業では出来たのに マクロを記録して実行しても再現されないというケースがありました。

今回はどうか？
記録したマクロを実行してみると・・・

いきましたね～。

最初と最初のフィルタと解除の動きが丸見えなんで少しカッコ悪いですし、削除処理には少し時間がかかりますが、それでも処理はかなり早い印象。

改良版のコードでも 42秒かかっていた 500行の処理が、この方法だと

42秒 → 3.2秒　圧倒的スピードです！

【回答2】スマートで処理速度の早い 行削除GAS

ではマクロで どのようなコードが記録されているのか確認しましょう。

function delCkRows() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1:F500').activate();
  spreadsheet.getRange('A1:F500').createFilter();
  spreadsheet.getRange('A1').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['FALSE'])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria);
  spreadsheet.getRange('2:2').activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
  spreadsheet.getRange('A1').activate();
  spreadsheet.getActiveSheet().getFilter().remove();
};

さすがに、マクロ記録だと無駄が多いですね。

単純に A1:F500 の範囲
.setColumnFilterCriteria(1, criteria) の 1

この2点を 変数にするだけでも十分なんですが、コードを少し綺麗にして汎用性のある形にしていきましょう。

今回は フィルタに関連した部分のコード解説は割愛しますんで、フィルタ部分の処理はそのまま使ってもOK。

冗長な書き方の部分を修正したのがコチラ ↓

フィルタを使った チェックされた行の一括削除コード

function delCkRows2() {
  const CKPOS = 1; //チェックボックスの位置(列)
  const startRow = 2;  //タイトル行を除くデータの開始行

  const sh = SpreadsheetApp.getActiveSheet();
  const dataRange = sh.getDataRange();

  //フィルタを設定
  const newFilter = dataRange.createFilter();
  let criteria =  SpreadsheetApp.newFilterCriteria()
                                .setHiddenValues(['FALSE'])
                                .build();

  newFilter.setColumnFilterCriteria(CKPOS, criteria);

  //表示されている行を削除
  sh.deleteRows(startRow, dataRange.getNumRows());

  //フィルタ解除
  newFilter.remove();
}

やや乱暴な書き方をしていますが、だいぶすっきりしましたね。

無駄なgetが減ったので微減ですが、実行時間もさらに短くなりました。

細かい考慮をしていないコードなので

これらに該当する表だと エラーになったり、チェックされた行以外が削除されてしまう可能性があります。

その点は注意です。

スプレッドシートの基礎知識があれば GASも変わる

回答1で作成したコードは JavaScript、主に 配列メソッドの知識が必要なものです。

一方、回答2のコードは スプレッドシートのフィルタ時の挙動の理解が必要となるコードとなっており、JavaScriptにだけ詳しい人ではたどり着けない解法です。

処理速度としては、回答2の フィルタを使った方法が圧倒的でした。しかも、これが マクロ記録するだけでも ほぼ使えるコードになるってのがいいですね。

このように、スプレッドシートでのGASは スプレッドシートの基礎知識があれば 大きく変わります。実は GASを使わず、シート関数や 機能でやった方が良い、（実は出来る）ってケースも多いのです。

ネット上の 行削除を扱った GAS記事で、フィルタを活用した例をあまり見かけないので今回紹介しました～。 

AIはラーメンを食ってるんじゃない、情報を食ってるんだ！

「GASによる 行削除」というシンプルなお題で2回に渡って書いてきましたが、配列メソッドの活用や スプレッドシートの基礎知識など 幅広い学びに繋がったんじゃないかと思います。

AIの活用にも触れましたが、3つの AI チャットのGASコードの回答は今回はイマイチでしたね～。

GoogleのAIに関しては、ちょうど 先日（8月末）、Google Cloud 2023が開催され、そこで発表された Duet AI 提供開始が 話題になりました。

GoogleWorkspaceにおける Microsoft 365 Copilot の対抗ソリューションとなる ものです。

どちらも 月々 $30 といいお値段ですが、現状のGASコードへの回答をふまえると、お高いサービスの成果物がどの程度のクオリティなのか気になるところです。

ここ最近の AIの台頭で、

Excelやスプレッドシートの関数や VBA,GAS なんかを学ぶのは無駄だ
プログラミングも不要になる （自分でコード書く必要はなくなる）

と、その時間（リソース）をもっとAIを学ぶ方に向けるべきという論調もありますが、現状ではまだまだそうでもないかなと思います。

もちろんケースによっては、AIの回答コードがそのまま使えることもあります。

さらにAIに関しては、新たな情報をを蓄積し学習し、日進月歩で進化しています。 まさに、暴食のAI～俺だけレベルという概念を突破する～ ですｗ

今日できないことが、明日には、もしくは1ヶ月後、1年後には出来るようになっているかもしれません。

この AIの進化速度は 人間（特におっさん）では 絶対に歯が立ちませんｗ

このnoteも いずれ AIの肥やしになるのかなー・・と思いつつ、自分が楽しみないがら知識のアウトプットと検証をぼちぼちやっていきますｗ

次回はまた関数ネタでも書こうかなと。