日次の売上や案件の進捗を管理しているスプレッドシートが、部署ごと、あるいは取引先ごとに別ファイルとして分かれているケースは少なくありません。
片方を更新したつもりが、もう片方は前回コピーしたときのまま止まっていて、どちらの数字が正しいか分からなくなった経験がある方もいるはずです。
この記事では、別ファイルのスプレッドシート間でデータを自動的に同期する仕組みを、IMPORTRANGE関数との使い分けから同期先を誤って上書きさせない運用まで整理しています。
IMPORTRANGE関数との使い分け
複数のスプレッドシート間でデータをつなぐ方法として、まず候補に挙がるのがIMPORTRANGE関数です。
同期先のセルに数式を入れておくだけで、開くたびに同期元の最新データが自動で反映されます。
権限の許可さえ最初に済ませてしまえば、コードを1行も書かずに使える手軽さが最大の利点です。
ただしIMPORTRANGE関数は、指定した範囲をそのまま数式で表示する仕組みのため、同じシートに手入力の列を混在させると数式がずれて壊れます。
同期先の列を加工したい場合や、複数の同期元を条件付きで1つにまとめたい場合にも向いていません。
Google Apps Scriptは、こうしたIMPORTRANGE関数では対応しきれない場面の選択肢です。
| 判断軸 | IMPORTRANGE関数 | Google Apps Script |
|---|---|---|
| 同期先に手入力の列を混在させたい | 数式が崩れるため不向き | 値だけを狙った範囲に書き込めるため対応可能 |
| 反映のタイミング | シートを開くたびに自動更新 | トリガーで設定した頻度のみ |
| 同期前にデータを加工・抽出したい | 加工できずそのまま転記される | 条件抽出や加工を挟める |
自分の用途がIMPORTRANGE関数だけで足りるかどうかは、この表の3項目を先に確認してから判断してください。
足りると分かった場合は、無理にスクリプトを組む必要はありません。
手順
同期元の確認からトリガー設定まで、4つのステップで完成する流れです。
Step1. 同期元のスプレッドシートIDを確認する
同期先となるスプレッドシートを開き、データを書き込みたいシートの名前を決めます。
ここでは「同期先」という名前で進めます。
続けて、同期元のスプレッドシートを開き、URLに含まれるIDを控えておいてください。
スプレッドシートのURLは https://docs.google.com/spreadsheets/d/【ここがID】/edit という形式です。
d/ の直後から /edit の直前までの文字列がIDです。
同期先のスプレッドシート上部のメニューから拡張機能を開き、Apps Scriptを選ぶとスクリプトエディタが立ち上がります。
Step2. コードを貼り付ける（コード全文）
スクリプトエディタに表示されているコードを全て消し、次のコードを貼り付けます。
function syncSpreadsheetData() {
const sourceSpreadsheetId = '1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcde'; // ★ここを変える: 同期元スプレッドシートのID
const sourceSheetName = '入力'; // ★ここを変える: 同期元シートの名前
const destinationSheetName = '同期先'; // ★ここを変える: 同期先シートの名前
const sourceSheet = SpreadsheetApp.openById(sourceSpreadsheetId).getSheetByName(sourceSheetName);
if (!sourceSheet) {
throw new Error(`同期元にシート「${sourceSheetName}」が見つかりません`);
}
const destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(destinationSheetName);
if (!destinationSheet) {
throw new Error(`同期先にシート「${destinationSheetName}」が見つかりません`);
}
const lastRow = sourceSheet.getLastRow();
const lastColumn = sourceSheet.getLastColumn();
if (lastRow < 1) {
return;
}
const sourceValues = sourceSheet.getRange(1, 1, lastRow, lastColumn).getValues();
destinationSheet.clearContents();
destinationSheet.getRange(1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
}同期元のシートを1行目から最終行まで丸ごと読み込み、同期先の内容を一度消してから書き込み直す仕組みです。
clearContents() は値だけを消す処理のため、同期先にあらかじめ設定した列幅や背景色などの書式は消えずに残ります。
実行するたびに同期先を全部作り直すので、同期元にない行が同期先へ残り続けることはありません。
Step3. 1回実行して同期結果を確認する
スクリプトエディタの実行ボタンを押し、同期先のシートに同期元と同じ内容が反映されるかを確認してください。
Googleアカウントへのアクセス許可を求める画面が出た場合は、内容を確認したうえで許可を進めます。
同期先の行数・列数が同期元と一致しているか、数字にずれがないかをこの時点で必ず目視してください。
Step4. 時間トリガーを設定する
スクリプトエディタの左側にある時計マークの「トリガー」を開き、右下の「トリガーを追加」から新しいトリガーを設定してください。
実行する関数にはsyncSpreadsheetDataを選び、イベントのソースは時間主導型、種類は分ベースまたは時間ベースのタイマーを選びます。
続けて実行する頻度を指定したうえで保存してください。
保存が終われば、指定した頻度で同期元の最新データが同期先へ自動で反映され続けます。
Google Apps Scriptで複数のスプレッドシートを1つにまとめる場合の考え方は、別記事で整理しています。
同期先を直接編集させない運用
Step2のコードは、実行のたびに同期先の内容を丸ごと消して書き直す作りです。
そのため、誰かが同期先に直接メモや修正を書き込んでいても、次の同期タイミングで跡形もなく消えてしまいます。
同期先が上書きされる仕組みだと気づかないまま入力を続けると、あとで内容が消えて混乱するというトラブルにつながります。
対策として、同期先のシートには「このシートへの直接入力は次回同期で消えます」といった一言をシート名か先頭行に残しておくと効果的です。
あわせて、同期先のシートに対する編集権限を、同期を回している担当者以外には閲覧のみに絞っておく方法も有効です。
どうしても同期先に手入力の列を残したい場合は、同期対象の列を右側に限定し、手入力用の列を左側にまとめて getRange() の書き込み範囲から外す形に組み替えれば共存できます。
Google Apps Scriptでどの業務から自動化すべきか迷う場合は、判断基準を別記事で整理しています。
Google Apps Scriptでのスプレッドシート同期について相談してみませんか？
「別ファイルのスプレッドシートを毎回開いて数字を突き合わせるのが手間になっている」「同期先に手入力の列が混ざっていて自動化に踏み切れない」「社内にGoogle Apps Scriptを設計できる人材がいない」
少しでもお心当たりがあれば、お気軽にご相談ください。
現在、Google Apps Scriptを使った業務自動化のお悩みをお伺いする 無料の個別相談 を実施しています。
よくある質問
Q. 同期は双方向にできますか？
できません。
Step2のコードは同期元から同期先への一方向の上書きです。
双方向にしたい場合、どちらの変更を正として扱うかという競合のルールが別途必要になるため、単純なコードの追加では対応できません。
Q. 同期元・同期先の列がずれていると崩れますか？
崩れます。
Step2のコードは列の位置をそのまま同期先へ書き込む作りのため、列の並びが同期元と同期先でずれていると違う列に数字が入り込みます。
列構成がずれやすい場合は、別記事で紹介しているヘッダー名で列を突き合わせる書き方も参考にしてください。
Q. 同期先の行数が同期元より多かった場合、古い行は残りますか？
残りません。
clearContents() で同期先を一度空にしてから書き込み直す仕組みのため、前回より行数が減った場合でも古い行がそのまま残ることはありません。
Q. トリガーの実行頻度はどこまで短くできますか？
Google Apps Scriptの時間主導型トリガーは、最短で1分単位まで設定できます。
ただし同期元のデータ量が多い場合、1分ごとの実行では前回の処理が終わる前に次の実行が始まる可能性があるため、データ量に見合った頻度に調整してください。
