【Googleスプレッドシート】Apps Scriptで選択範囲の内容を一括変換!setValuesの一括処理

スプレッドシートで大量のデータを扱うとき、セルごとに値を書き換えると処理が遅くなり、時間がかかってしまうことがあります。そんなときに役立つのが、Apps ScriptのsetValuesメソッドを使った一括処理です。setValuesを使えば、配列データを一気にシートに書き込めるため、処理速度が大幅に向上します。この記事では、選択範囲の内容をsetValuesで一括変換する具体的な方法を、コード例を交えて解説します。

Apps ScriptのsetValuesでできること

setValuesは、配列に格納されたデータをスプレッドシートの指定範囲に一括で書き込むメソッドです。通常のsetValueは1セルずつしか書き込めませんが、setValuesを使えば複数のセルを一度に更新できます。このため、スクリプトの実行時間を大幅に短縮できます。例えば、1000行のデータをループで1セルずつ書き込むと数秒かかるところが、setValuesなら一瞬で終わります。また、選択範囲の内容を変換する場合も、まずgetValuesで配列として読み込み、加工した後にsetValuesで書き戻すという流れが基本です。

選択範囲の内容をsetValuesで一括変換する手順

基本的な書き方:選択範囲を読み込んで書き戻す

1. スプレッドシートとシートを取得する
   スクリプトエディタを開き、以下のコードでアクティブなスプレッドシートとシートを取得します。
   var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
2. 選択範囲を取得する
   アクティブな範囲はgetActiveRange()で取得します。var range = sheet.getActiveRange(); このrangeオブジェクトに対してgetValuesを呼びます。
3. データを配列として読み込む
   var data = range.getValues(); これで二次元配列が得られます。data[row][col] で各セルの値にアクセスできます。
4. 配列の内容を変換する
   例として、すべての値を大文字に変換する場合、二重ループで各要素にtoUpperCaseを適用します。
   for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
if (typeof data[i][j] === 'string') {
data[i][j] = data[i][j].toUpperCase();
}
}
}
5. 変換した配列をsetValuesで書き込む
   range.setValues(data); これで元の範囲に一括書き込みされます。配列のサイズが範囲と一致している必要があります。

応用例:数値の倍にする・日付形式を変換する

1. 数値を2倍にする
   データが数値の場合、data[i][j] = data[i][j] * 2; のように演算します。ただし数値以外のセルがあるとエラーになるため、typeofでチェックしてください。
2. 日付の書式を変換する
   getValuesで日付を取得するとJavaScriptのDateオブジェクトになります。Utilities.formatDateを使って文字列に変換し、setValuesで書き戻します。
   data[i][j] = Utilities.formatDate(data[i][j], Session.getScriptTimeZone(), 'yyyy/MM/dd');

setValues使用時の注意点とよくあるエラー

配列のサイズが範囲と合わない場合

setValuesでは、書き込む二次元配列の行数と列数が、指定した範囲の行数・列数と完全に一致していなければエラーになります。例えば、3行4列の範囲に対して2行4列の配列を渡すと、「範囲のサイズがデータのサイズと一致しません」というエラーが表示されます。この問題を防ぐには、getValuesで取得した範囲サイズをそのまま使うか、配列の次元を範囲に合わせて調整する必要があります。

値の型が原因で予期しない結果になる

setValuesに渡す配列の要素は、文字列・数値・日付・ブール値などスプレッドシートで扱える型である必要があります。オブジェクトや関数を入れるとエラーになります。また、空のセルを表現するには空文字列”やnullを使いますが、nullはセルを空にします。逆にundefinedを入れるとエラーになるため注意してください。

パフォーマンスを最大限に引き出すコツ

setValuesは1回のAPI呼び出しで範囲全体を更新するため、setValueをループで呼ぶより圧倒的に高速です。ただし、スクリプト内で何度もsetValuesを呼ぶと逆に遅くなるため、可能な限り1回のsetValuesにまとめましょう。例えば、複数の範囲を更新する必要がある場合でも、一つの大きな配列にまとめて書き込めないか検討します。また、SpreadsheetApp.flush()を適宜使うことで、書き込みを確実に反映できます。

まとめ

setValuesを使うことで、スプレッドシート上の大量データを一括変換する処理が格段に高速になります。基本的な流れは、getValuesで選択範囲を配列として取得し、変換ループを実行してからsetValuesで書き戻すだけです。注意点として、配列のサイズと範囲の一致、データ型の確認を忘れないでください。このテクニックを応用すれば、複数シートの同時更新や条件付き変換など、さらに複雑な処理も効率化できます。ぜひ実際のスクリプトに組み込んで、処理時間の短縮を体感してください。