スプレッドシートの列から、重複した値を除く

スプレッドシートで大量のデータを扱っていると、ユーザーは顧客リストや注文履歴などの特定の列から、重複した値を除く必要が出ます。重複した値を除いて特別な一覧を別の列へ抜き出したい場面は、日常の業務で頻繁に発生します。Google Apps Script（GAS）を使えば、ユーザーはボタンのクリックや自動実行の仕組みでこの作業を自動化できます。なので、ユーザーは手作業による並べ替えやコピーの作業時間を大幅に削減できます。

ただし、プログラムが単純にデータを取得して重複を消すプログラムを書いた場合、空白のセルの処理で結果がずれます。数値と文字の混ざり、日付の比較ルール、元データを更新したときに古いデータが残る現象も、結果を狂わせる原因です。というのは、実際の業務データには画面で見えない差異が多く含まれるからです。本稿では、スプレッドシートの列から重複した値を除く処理において、データがずれる理由と、正しい結果を出すプログラミングの組み立て方を解説します。内容を確認しましょう。

A列のデータ抽出で空セルや型の違いによって抽出結果が意図せずずれる原因
1列のデータから重複を排除して隣の列へ出力する具体的な記述
大量データ処理時における処理速度低下とGASの実行時間制限への対策
数値・日付・文字列が混在する実務データで一意化を正しく行うための比較ロジック
処理結果のクリーンアップと後続処理への安全なデータ引き渡し

A列のデータ抽出で空セルや型の違いによって抽出結果が意図せずずれる原因

スプレッドシートの指定した列からデータが取得されて重複を取り除こうとする際、空のセルやデータ型の違いが誤判定を引き起こします。GASで sheet.getRange("A2:A").getValues() のようにプログラムが終わりの行を指定せずにデータを取ると、データが入っていない下の大量の空セルまで二次元の配列としてプログラムに読み込まれます。なので、プログラムが単純に重複を取り除くと、空の文字（""）が一つのデータとして残ってしまいます。その結果、プログラムが本来出力したい位置から結果が1行ずれます。

さらに、JavaScriptの配列操作機能である filter(String) をプログラムが使うときにも注意が必要です。というのは、filter(String) は要素を文字に変換したときの条件で空文字や null、undefined を消すからです。シート上に数値の 0 や真偽判定の false がある場合、プログラムがこれらを文字ではなく値そのものとして判定するため、必要なデータが消えてしまいます。逆に文字に変わるタイミングの違いによって、消したいデータが残る場合もあります。また、スプレッドシートのセルには、見た目が同じ数字でも「文字の100」と「数値の100」が混ざるケースが多く存在します。JavaScriptの Set 機能は厳しい比較（===）を行うので、機能がこれらを別の値として扱います。なので、重複が消えていないように見える現象が起きます。

このようなデータのズレを防ぐためには、取得した二次元の配列を一次元に変える flat() 処理、空のセルを消す filter() の正確な条件、データ型を揃える手順をユーザーが正しく理解する必要があります。なので、ユーザーは取得したデータを事前に整えましょう。特に全員で使うスプレッドシートでは、入力した人による形式のバラつきがそのまま残っています。だからこそ、GASのプログラムでデータの違いを消す仕組みを準備しましょう。

1列のデータから重複を排除して隣の列へ出力する具体的な記述

この処理は、A列に毎日追加される顧客のメールアドレスや商品コードのリストから、プログラムが重複のない一覧をB列へ自動で作る場合に使います。集計の前準備として重複のないキーの一覧を抜き出したいときにも、以下のコードを動かしましょう。

const sheet = SpreadsheetApp.getActiveSheet();
const values = sheet.getRange("A2:A").getValues().flat().filter(String);
const unique = [...new Set(values)];
sheet.getRange(2, 2, unique.length, 1).setValues(unique.map(v => [v]));

上記のプログラムはとても短く便利ですが、ユーザーがそのまま使うとデータがずれる問題が出ます。原因は、プログラムが sheet.getRange("A2:A") によってA2セルからシートの一番下までのすべての行を取得している点にあります。シートの行数が数万行に及ぶ場合、データがない空のセルも大量に読み込まれるので、flat() と filter(String) でパソコンのメモリに大きなデータが作られます。ここで filter(String) は空文字の "" を不要な値と判定して消してくれます。ただし、前述の通り数値の 0 や false も不正確に処理されます。もしデータの中に数値の 0 が正しい値として入っている場合は、ユーザーが filter(v => v !== "" && v !== null && v !== undefined) のように明確な比較の式に書き換えましょう。なので、必要なデータが消えるトラブルを防げます。

また、プログラムが画面に書き出すときのズレとして、前の書き出し結果が残る問題に気をつける必要があります。というのは、消した後のデータ件数が前より減った場合、後ろの行に古いデータが残ってしまうからです。例えば、最初にA列に100件のデータがあり、プログラムが重複を消した50件がB列の2行目から51行目に書き出されたとします。その後、A列のデータが更新されて重複を消した後の件数が30件に減った場合、ユーザーが上記のプログラムを動かすとB列の2行目から31行目までは新しい30件で書き換わります。ただし、32行目から51行目には前回の古いデータが残ってしまいます。なので、実際のデータは30件なのに、B列には50件が存在するように見えて集計の計算が狂います。

この問題を避けるためには、プログラムが setValues() を実行する前に、書き出す場所であるB列の範囲を消す処理が必要です。なので、プログラムが sheet.getRange("B2:B").clearContent() などを書いてデータを事前に消し去りましょう。さらに、重複を消した結果が0件の場合にプログラムが getRange や setValues を動かすと、プログラムが止まるエラーが出ます。なので、プログラムに if (unique.length > 0) という件数の確認を入れる安全対策を行いましょう。動かす前に確認しましょう。

大量データ処理時における処理速度低下とGASの実行時間制限への対策

スプレッドシートのサイズが大きくなり、行数が数万件から数十万件に増えると、重複を消すだけの処理でも動く時間が伸びます。なので、GASの制限時間である6分に達して処理が途中で止まる危険が高まります。動く速度を遅くする一番の理由は、Google Apps Scriptとスプレッドシートの間で行うデータのやり取りです。GASにおける「シートからのデータ読み込み（getValues）」や「シートへのデータ書き込み（setValues）」は通信を行う重い処理です。なので、繰り返し処理の中でプログラムがこれらを何度も呼び出すと、動く速さが大幅に落ちます。

重複を消す処理を速くするルールは、一括読み込み、メモリ内の処理、一括書き出しの順序をプログラムが徹底することです。例のプログラムのように、プログラムが getRange で取った二次元の配列をまとめてJavaScriptの配列に入れ、Set や filter を使って素早く重複を調べます。そのあと、プログラムが最後の結果を一回の setValues でシートに戻しましょう。この組み立ては、動く速さの面でとてもすぐれています。

ただし、データがさらに増えた場合、A2:A のように列全体を取る書き方自体が作業を遅くします。というのは、データがない一番下の行までプログラムが範囲を広げると、空のセルを調べる作業だけで多くの時間が消えるからです。これを防ぐためには、プログラムが sheet.getLastRow() を使ってデータが存在する最後の行番号を取得しましょう。なので、sheet.getRange(2, 1, lastRow - 1, 1) のように必要な範囲だけを正確に取得するプログラムに変える方法が役立ちます。プログラムが必要なセルだけを選んで読み込むことで、メモリの消費を抑えてデータ転送の時間を短くできます。

また、大量の文字を比較するとき、Set 機能はハッシュテーブルの仕組みを使って素早く重複を確認します。なので、機能が indexOf や includes を使って何度も探すプログラムと比べて圧倒的に速く動きます。大量のデータから重複を取り除く場合は、ユーザーが必ず Set や Map の仕組みを使ったプログラムを使いましょう。プログラムの工夫と通信の削減の両方から作業速度を速くしましょう。

数値・日付・文字列が混在する実務データで一意化を正しく行うための比較ロジック

実際の仕事で使うスプレッドシートの列には、文字だけでなく、数値、日付、全角や半角が混ざった文字など、多くのデータ型が入っています。JavaScriptの Set 機能は、データの同じさを比べるときに厳しい判定を行います。なので、ユーザーがこの仕組みを知らないと、見た目は同じデータなのに重複が消えない問題が起きます。あるいは、消してはいけないデータが消える問題が起きます。

特に間違えやすいデータは日付です。スプレッドシートで日付の形になっているセルをプログラムが getValues() で取ると、GASの中ではJavaScriptの Date データとして扱われます。JavaScriptの中では、2つの Date データが同じ「2023/10/01」という日付を示していても、パソコンのメモリ内では別のデータとして扱われます。というのは、データが置かれている場所の情報が異なるからです。そのため、new Set([date1, date2]) にプログラムがデータを入れても、プログラムはこれらを別のデータと判断します。なので、両方のデータが消えずに残ってしまいます。日付の列から重複を消したい場合は、ユーザーが getValues() で取った Date データを .getTime() で数字に変えましょう。または、ユーザーが Utilities.formatDate() を使って標準的な日付の文字に変えてから Set に渡しましょう。

また、文字の書き方の違いも重複を消す処理の邪魔をします。例えば、文字の前後に入った不要なスペース、全角と半角の違い、大文字と小文字の違いは、Set においてすべて違うデータとして扱われます。手入力されたスプレッドシートのデータを処理する場合は、ユーザーが重複を消す前に文字を整える必要があります。なので、ユーザーが trim() で余計なスペースを消し、toLowerCase() で大文字を小文字に揃える作業を行いましょう。

このように、ただ Set にプログラムが配列を入れるだけでは不十分です。ユーザーが扱う列のデータ型を考え、あらかじめ比較できる形に揃える作業を入れましょう。そうすることで、ユーザーは初めて仕事で使える正確な重複のない一覧を作ることができます。設定を確認しましょう。

処理結果のクリーンアップと後続処理への安全なデータ引き渡し

重複を消してプログラムが作ったデータは、別の計算に使われたり、違うシステムに読み込まれたり、グラフの元データとして使われます。そのため、GASによる重複消去の処理は、プログラムが正しい値をシートに書くだけでは不十分です。後ろの作業やほかの人が安全に使える状態を作ることが大切です。なので、ユーザーは全体の流れを意識してプログラムを作りましょう。

最初に考えるべき作業は、書き出すシートの場所をプログラムがきれいに消す処理と、書き出す範囲を自動で変える仕組みです。前述の通り、書き出す列に昔のデータが残っていると集計が壊れます。安全な手順としては、まずプログラムが書き出す場所のデータを clearContent() で消しましょう。そのあとに、重複を消したデータの数に合わせてプログラムが getRange(2, 2, unique.length, 1) で正確な範囲を計算し、setValues() を動かします。このとき、重複を消した結果が0件の場合に getRange の行数に 0 をプログラムが渡すと、GASの画面にエラーが出て処理が止まります。なので、データの数が1件以上あることをプログラムが条件分岐の if で確認してから書き出しを行いましょう。

さらに、出来上がったデータを次の作業に渡すとき、プログラムがシートのセルに書くだけでなく、プログラムの戻り値として別のプログラムに渡す方法も使えます。CacheService や PropertiesService を使って一時的にデータを保存する設計も便利です。また、スプレッドシートの見た目を整えるために、プログラムが書き出したあとに枠線を自動で引き直しましょう。プログラムが背景の色を消したり、sort() であいうえお順や昇順に並べ替えてから出力すると、確認する人が作業しやすくなります。