別ファイルのデータを IMPORTRANGE で引っ張っていたら、シートを開くたびに「読み込み中…」が長く続く、あるいは #REF! エラーで止まる。参照が増えるほど重くなり、業務用のシートが使い物にならなくなるケースがあります。
A:Z のような列全体の参照を、実際に必要な列と行までに絞ります。あわせて、QUERY と組み合わせて「必要な列だけ・必要な条件の行だけ」を取り込むと、転送量が減ります。
=QUERY(IMPORTRANGE("参照先のURL","シート1!A:F"), "select Col1, Col3, Col5 where Col2 is not null")
データを同じファイルの別シートに置けるなら、IMPORTRANGEではなく通常のシート参照やQUERY(同一ファイル内)にするだけで、読み込みは大きく軽くなります。「ファイルを分けなければならない理由」がなければ、統合も選択肢です。
リアルタイム性が不要なら、IMPORTRANGEをやめて、GASで参照先の値をコピーして貼り付ける方式に変えます。貼り付け先はただの値になるため、開くときの読み込みが発生しません。時間主導型トリガーで、たとえば毎朝1回だけ同期する運用にします。
function copyValues() {
const src = SpreadsheetApp.openById('参照先ファイルのID')
.getSheetByName('元データ');
const values = src.getDataRange().getValues();
const dst = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('コピー先');
dst.clearContents();
dst.getRange(1, 1, values.length, values[0].length).setValues(values);
}
この関数を、スクリプトエディタのトリガー設定で「時間主導型・毎日午前8時」などに登録します。トリガーの上限(実行時間など)については記事「GASのトリガーが動かない時に確認する順序」も参照してください。
参照:Google スプレッドシート公式ヘルプ(ファイルのセル数上限、IMPORTRANGEのアクセス許可)。
「毎週の転記・集計をなくしたい」「CSVの整形を自動化したい」「古いGASを直したい」といったご相談を承っています。1ファイル・1処理の小さな作業から対応し、作業前に必ずメッセージで内容を確認して、対応範囲と金額を先にお伝えします。検証記録と手順書付きで納品します。
相談してみる(Coconala)※出品ページは準備中です。公開まではこのボタンは仮のリンクです。