表計算お助けツール&ノウハウ

IMPORTRANGEが遅い・エラーになる時の代替案

困っていること

別ファイルのデータを IMPORTRANGE で引っ張っていたら、シートを開くたびに「読み込み中…」が長く続く、あるいは #REF! エラーで止まる。参照が増えるほど重くなり、業務用のシートが使い物にならなくなるケースがあります。

まず原因の切り分け

手順(軽くする→代替案の順)

対処1:参照範囲を必要最小限に絞る

A:Z のような列全体の参照を、実際に必要な列と行までに絞ります。あわせて、QUERY と組み合わせて「必要な列だけ・必要な条件の行だけ」を取り込むと、転送量が減ります。

=QUERY(IMPORTRANGE("参照先のURL","シート1!A:F"), "select Col1, Col3, Col5 where Col2 is not null")

対処2:同じファイル内で済むなら通常の参照にする

データを同じファイルの別シートに置けるなら、IMPORTRANGEではなく通常のシート参照やQUERY(同一ファイル内)にするだけで、読み込みは大きく軽くなります。「ファイルを分けなければならない理由」がなければ、統合も選択肢です。

対処3:GASで「値」を定期コピーする(本命の代替案)

リアルタイム性が不要なら、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)

※出品ページは準備中です。公開まではこのボタンは仮のリンクです。