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

久しぶりにGASネタです。

特定の列にチェックがついていたらその行を削除するという、GASとしてはシンプルな処理を題材を使います。

最近某Googleスプレッドシート系のブログで見たコードが、自分と結構書き方が違うもんだなーと思ったのがきっかけです。

簡単すぎる気もしますが、初心者～もうちょいコード書けるようになりたい人にはちょうどいいお題かなと。

GASの基本というよりは 初心者がなにをすべきかという観点で、行削除という処理を通じて、AI活用の実例や スプレッドシートでのGASあるある、繰り返し処理や、マクロ記録で出来ること、スプレッドシートの基礎知識の重要性などにも触れていいこうかと思います。

↓ 前回のnote 。週にわたって スプレッドシートの画像の扱いについて書いたけど、あんま反応がよくなかったｗ

Googleスプレッドシート GASによる行削除 初心者が挑戦する場合

今回の要件は 非常にシンプルです。

という要件です。GASの例としてはよく見かけるものですね。

そもそも データは削除せず、非表示（フィルタで絞り込み）とした方がよいのでは？ という意見もあるかと思いますが、それは一旦置いときましょう。

GASを理解している人なら サクッとコードが書ける簡単なものですが、初心者がこのお題に取り組む場合どうしたらいいか？

1. 検索してみる（ググってみる）

2. QAサイトで聞いてみる

3. AIチャットに質問してみる

4. マクロを記録を使ってみる

この辺りが定番と言えます。

他にも Twitter (X) などのSNSで発信してみるという方法もありますが、文字数的にコード系は回答付かないことも多いです。

それぞれ実際にどのような結果が得られ、どこに注意すべきかを見ていきましょう。

1. まずは検索 ググるのが基本

最近は自然言語でもググれる

GASがよくわからなくて、このお題に対して「とりあえず何をしたらいいかわからない」という場合は、まずは ググってみるのが基本です。

昭和世代からすると、こんな口語みたいな検索はありえないって感じますが、現在は検索エンジンも性能が向上してるんで、自然言語（普通の会話のような記述）でも 結構思った通りヒットしてくれます。

ちなみに、検索トップに表示される officeの杜 さんの記事も良いんですが、ちょっと初心者にはハードルが高いかもしれません。あと、ちょっと情報が古いかなと。

もちろん 自然言語で検索できるようになったと言っても、普通の人が見て 判断がつきかねるような文章だと、期待するような検索結果は得られません。

求める結果を得る為には、検索の際に入れるべきキーワードがなにかを考える必要があります。

まだまだ Google検索の基本は キーワードのスペース区切り

日本はハイコンテクスト文化（共通認識が多い文化）と言われます。阿吽の呼吸とか、暗黙のルール、忖度、気遣い、傾聴、本音と建て前、空気を読む・・・、これらは日本人の美徳とも言えますが、グローバルでビジネスをする上では足かせになることも多いようです。

一方、多民族国家や移民の多い ヨーロッパ諸国や アメリカでは、文化土壌や宗教、常識が違う人たちのやりとりの中で、とにかく 言語化して伝えるという ローコンテクスト文化 が醸成されたみたいです。

日本人は 「ネットでの検索が下手」な人が多いと言われるのは、これが一つの理由と言えるでしょう。

上記のように Google側も 空気を読んで 検索結果を返してくれるように進化していますが、まだまだ 上手にGoogle検索する為には、自分の求めている情報を 簡潔な複数の単語に分解することが必要です。

今回の場合は

チェック　行 削除　GAS

今回であれば、このあたりが重要な検索キーとなります。

そしてうまく検索できれば、このように teratail や Yahoo!知恵袋などの QAサイトや GAS解説系のブログなどが上位表示されます。

この検索結果の上位のページに、希望の処理 と同じ コードがあってコピペで使えればラッキーですね。

GASを作成する際に初心者でもGoogle検索でゴール（完成）できる人は、

• 検索能力、情報の取捨選択能力が高い

• 応用能力が高い （ここだけ変えれば出来そうだ）

• 多少は GAS以外含め プログラミングの知識がある

こんなタイプかなと思います。

2. QAサイトで質問は最後の手段

上のように上手に検索をしても、残念ながら そのものズバリの回答にたどり着かないないことも多いです。

ここで次のアクションとして、上位に表示された QA系サイトで質問するという方法をとる人もいますが、mir的には QA系サイトで気軽に質問するのはおススメしません。

初心者が プログラミングの質問をすると コード作成依頼になりがち

もちろん、プログラミング系の質問でも 善意でコードを作成して答えてくれる回答者もいます。

ただし、「そもそも論」で説教されたり、「作業依頼ですか？」的な厳しい返しをされることも多く、心が折れたり 逆切れする質問者も少なくありませんｗ（昔よりはマシになりましたが）

悪気はなかったとしても、初心者が QAサイトでプログラミング系の質問をすると、内容的にコード作成（コーディング）依頼になってしまうことが多い のです。

QAサイトの回答者は、自分のスキルアップを目的にコード書きたい人、問題解決に楽しさを見いだせる人、マネタイズ化に向けて 実績＆知名度を上げたい人、本当に親切な人 などなど、優しくコード書いてくれる人もいます。

ただ雑な質問に対しては、多くの回答者は 無視するか、有料のお仕事依頼サイトへどうぞってスタンスになってしまいます。

まず、せめて検索でヒットしたコードがそのものズバリじゃなく、どうアレンジしたらよいかわからなかったとしても、見つけたコードを実行して、試してみてから質問しましょう。

あと、回答に対して 試しもせず「この修正をすれば動くってことですか？」みたいな返答もやめましょうｗ

質問が苦手な人も言語化が下手（あと手抜きすぎ）

さらに 根本部分がよくわからないまま 質問をする人は、質問自体が下手という傾向も多い気がします。

この質問が他の人には理解不能というのも、回答がつかなかったり、回答者の書き込みが厳しめになる要因の一つ。

たとえば 

コードもデータ（スプレッドシートの表の構成）も提示せず、こんな書き方をされても、回答者がわかるわけありません。

「サイトの通りに」と言いつつ、自分なりの理解で コードを魔改造していたり、その○○で解説しているのと違う データ構成のシートで動かそうとしていたり

SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SHEET_NAME');

こういう コードを 'SHEET_NAME' の箇所を修正せずそのまま使ってたりすることも多いです。

「なんだかうまくいきません」の箇所も

このように、明確にしてくれるとありがたいです。

どのような 不具合なのか明確にできれば、回答までのやりとりがぐっと短くできます。

特に エラーログは 提示いただけるとよいです。

これらも、あるあるですね。

気持ちはわかりますが、質問者側も手間をかけてダミーデータを用意する、細かく前提条件を伝える、下手でもコードは全部載せるといった姿勢が必要です。

極めつけは　回答後に 返ってくる　「ちなみに」　から始まるまったく違う条件や前提の登場。

これ本当にやめて欲しいですｗ

親戚のおじちゃんに お年玉で一万円を もらった時に

「ありがとうございます。ちなみに、僕には下に弟がいるんで もう1万円お年玉をいただけますか？」

と言ってるのと一緒なんで。

とりあえず ＱＡサイトは すぐに回答を得られないことも多いですし、最後の手段と思いましょう。

あと、質問に 【大至急】とかつけられても・・・って感じです。そもそも 急ぎとか言ってる質問に限ってレスポンスが遅かったり、回答入れても無反応ってケースも多いんですよね。。

と、回答者の愚痴っぽくなってしまいましたがｗ

 mirは回答者としては  主に ↓ ココにいます

3. 今どきなら AI に聞いてみるのもアリ

検索もいいですが、今どきなら AIチャットに質問投げてみるのもいいですね。

コーディングの依頼や コーディングチェックも AIチャットの得意とするところと言われています。

今回は 無料で使える代表的な 3つのAIチャットサービス

Bing Chat（Bing AI）
ChatGPT
Google Bard

に

という同じ質問を投げて検証してみました。

Bing Chat （Bing AI）に聞いてみる

こちらはBing Chat の回答

Bingチャットは Microsoftが提供する検索エンジン Bingの機能として提供されている AI チャットです。

当初は Edgeブラウザでしか使えませんでしたが、今は Google Chromeでも使えます。また アプリ版Edgeでも利用可能となりました。

↓ 回答のコードがコチラですが・・・

// BingChatの回答
function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  for(var i = 0; i < data.length; i++) {
    if(data[i][0] != true) {
      newData.push(data[i]);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

あー、、こうきたかｗ　って感じですね。 var を使ってるあたり古い情報を多く参照してるってことでしょうか。

これは間違ってると言えなくはないけど・・・。

処理としては「行削除」じゃなくて getValuesで一括取得したデータ（二次元配列） を for ループで回して、各行のA列にあたる部分 data[i][0] が trueではない（チェックされていない）ものだけ 事前に用意した newData（空の入れ物） に push して 新しい二次元配列を作成してから、 シートの値を全削除して setValues(貼り付け)って処理ですね。

このコードを試す前に ChatGPTも見てみましょう。

ChatGPT（無料版）に聞いてみる

ChatGPT の回答はこちら。

// ChatGPTの回答
function deleteRowsWithCheck() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] !== true) {  // 列Aがチェックされていない場合
      newData.push(data[i]);
    }
  }
  
  // シートをクリアして新しいデータを書き込む
  sheet.clearContents();
  if (newData.length > 0) {
    sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
  }
}

ChatGPTの方がコメント入りで少し丁寧ですかね。 でも Bing Chatの回答とほぼ同じコードと言えます。

これは Bing AIが 同じ OpenAIの提供する、GPT-4 ベースだからかでしょうか？（無料版のChatGPTは GPT-3.5）

ChatGPTはもっとも名前が知られている AIチャットサービスです。

登場時に mirも スプレッドシート上で使える GPT関数の noteを書いて、mir的には結構バズりましたｗ

アカウント登録が必須で、フル活用するには課金した方がよいですが、無料でも基本的なAIチャットとしては問題なく使えます。

現在は ブラウザだけでなく iOSおよびAndroidのアプリ版も 日本で利用可能となっています。

https://chat.openai.com/

↑　なぜか埋め込みに変換されない

Bing Chat と ChatGPT の回答コードを試す

先ほど書いた通り、Bing Chat と ChatGPTの回答はどちらもほぼ同じもので、行削除というよりは

という処理です。

これを行削除処理プログラムとして使った場合、 単なるテキストデータの表なら 行削除と同じ結果になるんで問題ないですが

表内に 数式がある場合は、式は値化されてしまいますし、 文字の装飾（太字）や セルの塗りつぶしがあった場合は、GIF画像のように 装飾が ズレるという問題が発生します。

※ ExcelのVBA処理と違って、Googleスプレッドシートは GASで実行した処理も「元に戻す」ボタン（または Ctrl + z）で操作を戻せます。GAS実行後の結果が思うようなものでなければ、戻るを活用しましょう。

これだと 今回の「チェックした行を削除したい」というお題に対しては、あまり良い回答とは言えません。

Google Bard に聞いてみる

それでは GASのことなら 同じGoogleに（餅は餅屋）ってことで、GoogleのAIチャット Bard にも聞いてみましょう。

Bardは Googleが開発した AIチャットサービスです。

Microsoftの Bing Chat は、OpenAIの GPT-4がベースとなっている為、先ほどのように ChatGPTと似た回答をすることもありますが、Bardは（PaLM2ってのがベースの）まったく別モノである為、先の2つとは違う角度からの回答をしてくれることも多いです。

回答のコードはこんな感じ。

//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()) {
      //行を削除
      sh.deleteRow(i);
    }
  }
}

なるほど、オーソドックスな forで下からループしながらA列の値を1つずつ取得し、条件を満たしたら deletRow を繰り返すって処理ですね。

変数を const で宣言しているのもポイント。

これでいけそうに思えますが、実は完全とは言えません。

たとえば表に タイトル行があった場合は

このように タイトル行まで 削除されてしまいます。

これは

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

↑ チェック判定をこのようにシンプルに記述している為、値が入っていた場合は TRUE扱いとなり、チェックボックスではない タイトル行でも 行削除処理が走ってしまうからです。

例えば ここを

if (sh.getRange(i, CKPOS).getValue() == true)

と変えて true であるかをきちんとチェックするか、

if (sh.getRange(i, CKPOS).isChecked())

このように iSChecked() という チェックボックス専用のメソッドを使って判定することで、この問題は解消されます。

iSChecked() は 単体セルに対して使った場合は、そのセルがチェックが入っている時だけ true を返すメソッドです。

チェックボックスをテーマにした GASネタの noteでも触れています。

↓  そこを 修正するとタイトル行は削除されなくなりました。

//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);
    }
  }
}

ちなみに Bardは 同時に3つの回答案を用意してくれるのですが、他の2つの回答のコードを確認するも、あまり変わりばえせずでした。

惜しい。。

このように AIを使ったとしても 、コピペしただけで要望通りの処理をするコードにたどり着けないことも多いわけです。

もちろん チャットなので、回答に対して うまくいかないと返すことはできますが、この先のやりとりは結構噛み合わないことも。。

AIは コードの意味合いについても教えてくれる

Bardの回答のループ処理ですが、最終行から逆順での処理となります。

ここは 初心者が自分でコード書いた時にハマりやすいポイントなのですが、

forでループ処理する部分を

for (let i = lastRow; i >= 1; i--)

ではなく、いつもの感覚で

for (let i = 0; i < lastRow; i++)

と書いてしまうと、一部のチェックがついた行が 削除されず残ってしまうといった問題が発生します。

なぜ、逆順ループでないとダメなのか？

これも AI（今回はBard）に続けて質問すると 教えてくれます。

この質問に対して次のとおりですと言いつつ、上の２つの理由は イマイチ意味合いがわかりませんが ３つ目の

こちらは正しい理由ですね。

1番目の 検索でヒットした以下のサイトにも解説があります。

このような 初歩的な質問は、QAサイトで聞きづらかったり、下手すると「ググれカス」をくらうので、感情のないAIに聞くというのはアリかもしれませんｗ

AIへのコード作成依頼も検索と同じく、依頼内容を適切に言語化できる能力、そして AIからの回答コードを見て「ここを少し修正すれば使えるな」と判断できるベースとなる知識が必要となります。

依頼の仕方が雑（言葉足らず）で、コードを見ても「まったくわかりましぇーん」 な人が AIの回答だけでプログラム完成させるのは、まだまだ難しいのかなと感じるわけです。

4.「マクロを記録」を使ってみる

Excel でもお馴染みの 行った操作手順を マクロとして記録する機能が、Googleスプレッドシートにもあります。

初心者が試すべき4つ目の方法として、今回の 行削除を マクロ記録させたものは使えるのか？ について検証してみましょう。

マクロの記録の流れ

まずはメニューから

拡張機能　＞　マクロ　＞　マクロを記録

で 録画（記録）を開始します。

とりあえず該当の行を選択する部分は、今回はチェックを目視で確認し 手作業で 行選択する方法としましょう。（もっといい方法ありますが 次週書きます）

飛び飛びの選択をする方法は、Excelと Googleスプレッドシートはどちらも同じです。Windowsの場合は

Ctrlキーを押しながら 選択（行クリック）　とします。

この 飛び飛びの複数行選択は、Ctrlキーを押しながら選択する という方法も 知らない人は一定数いるんですよね。やはり基礎知識は大切です。

選択が終わったら、Ctrlキーは押したままで

右クリック ＞ 選択した行を削除

これで、一気に行が削除できました。

記録を停止して マクロに名前をつけて保存すればOK。

記録された マクロを確認し 試す

記録されたマクロは、スクリプトエディタの マクロ.gs という箇所に記録されます。

今回は マクロに名前を付けなかった為、 myFunction という関数名ですがマクロの記録の際に マクロに名前をつけたら、それが関数名となります。

//マクロの記録で生成されたコード
function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRangeList(['2:2', '4:4', '11:11', '16:16', '17:17', '18:18', '23:23', '26:26', '27:27', '30:30']).activate();
  spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
  spreadsheet.getRange('A1').activate();
};

ずいぶんシンプルなコードですが、これで 選択した行を一気に削除ができるんでしょうか？

これで行の一括削除がうまくいけば、あとは 選択した行の

この配列を チェックを判定して生成するコードに書き換えれば よいってことになります。

先ほどの マクロ記録用に手動で行った行削除 を「戻す」で シートを行削除前の状態に戻してから、マクロを実行してテストしてみましょう。

マクロは 自分で作った functionと同じように エディタからも実行できますが、自動でシートのメニューにも追加されるというメリットがあります。

また、

Ctrl + Alt + Shift + 指定した数字キー

とちょっと押しにくいですが、キーボードショートカットによるマクロ実行も出来ます。 

ちなみに 自分でエディタ上でコードを書いた function も、マクロをインポートで マクロ化させて、メニュー表示させたり ショートカット実行の対象とすることも可能。

でも、GAS動かすのが初めてのスプレッドシートだと、マクロでも定番の「承認が必要です」がでますｗ　これは仕方ない。

初回だけ1度承認をして、マクロを実行してみましょう！

えっ？？って感じですよね。

一瞬 チェックの行が選択されたように見えましたが、削除されてない？？

いや、よく見ると連番を振っていたうち 29がない。一番下のチェックされた行、 1行だけが削除されたようです。

つまり、飛び飛びの複数行をCtrlを押して選択して 一括削除は、シートを開いて手作業では出来るけれど、

GASだと

ってことです。対応するメソッドが用意されていないのでしょう。

マクロの記録で生成されたコードの 行削除部分は deleteRows() が使われていますが、これは 「〇行目から下に〇行分を削除する」といった形で 連続する複数行をまとめて削除する為のメソッドです。飛び飛びには対応していません。

Googleスプレッドシートのマクロの記録は こういったことが多々あります。Excelだと シート上の操作はほぼマクロ記録されるので、これが非常に不満。

もちろん、この手順で記録したマクロがそのまま、もしくはアレンジして使えるケースもあります。

ただ、残念ながら今回は（この方法では）マクロの記録で生成された コードは使えませんでした。

残った課題は次回へ

今回は A列にチェックが入った行を削除したいを GASで実現する際、初心者が試すべきアクションとして、

1. 検索してみる（ググってみる）

2. QAサイトで聞いてみる

3. AIチャットに質問してみる

4. マクロを記録を使ってみる

について 紹介・検証しました。

この中だと今回のケースでは、 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ループ で書くのはカッコ悪いなー。もっとクールなコードにしたいなー。って欲が出てきますよねｗ

さらに、このコードの処理を改めて確認してみると

おっっっっっせぇぇぇぇぇーーーーーーー。 

エディタで確認すると 5.8秒もかかっています。30行程度でこの処理時間ｗ

1000件くらいのデータで使うのも躊躇しますよね。実際、2500行くらいのデータだと GASの6分タイムアウトもありえます。

というわけで、

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

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

チェックされた行の一括削除という単純処理ですが、まだこれらの課題が残っています。

果たして AIは解決してくれるのか！？

次回この続きということで、mir の作成したコードとAI回答を比較しながら、GASの理解を深めていきましょう！