IMPORTRANGEか、それ以外か。

本記事は私の所属しているコミュニティ：ノンプログラマーのためのスキルアップ研究会 の『ノンプロ研 Advent Calendar 2020』2日目の記事です。

GoogleスプレッドシートのIMPORTRANGE関数を知ったときはその便利さにいたく感動しました。
Excelのようにファイルパスを気にしなくてよいのが何よりも便利です。
「ワンソース・マルチユース」万歳です。

IMPORTRANGE - ドキュメント エディタ ヘルプ

自分の業務でもIMPORTRANGEをよく使用します。

社内のデータベースからデータをダウンロード
→スプレッドシートに丸々コピペ
→それを参照する様々なシートが自動で更新される

いわゆる「DOVパターン」について、この記事に多くを学ばせていただきました。ありがとうございました。

ExcelのワークシートをDOVパターンで構成する【方法とメリット】

IMPORTRANGEか、それ以外か。

IMPORTRANGEの使用を続けると、いくつか気になる点が出てきました。

【IMPORTRANGEのモヤモヤポイント】
・参照先のスプレッドシートを開いても、
　参照元データを読み込むのに数秒間かかり
　その間は古いデータが表示される
　（IMPORTRANGEを知らない人には特に不審がられる）

・どのスプレッドシートに参照しているのか管理を怠り、
　参照元のスプレッドシートの改修に二の足を踏む

・いつの間にか誰かに参照され、運用をやめるとややこしくなりそう

そのうち、Google Apps Scriptで「配列を用いてシートのデータをマルっとコピペ」なる技を覚えまして、これはこれで便利。

function overwriteData() {
 
 const refSheet    = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 const values      = refSheet.getDataRange().getValues();
 
 const targetSs    = SpreadsheetApp.openById(/***SS_ID***/);
 const targetSheet = targetSs.getSheetByName(/***SHEET_NAME***/);
 
 targetSheet.getRange(1, 1, values.length, values[0].length).setValues(values);   
}

さらに、参照先（ペースト先）が複数の場合は以下のようにスプレッドシート/GASを構築すると、参照先が明示され管理が便利、と気づきました。

B列にペースト先のスプレッドシートID、C列にシート名を用意。

function overwriteDataAll() {
 
 const refSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 const values   = refSheet.getDataRange().getValues();
 
 const listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("list");
 const lastRow   = listSheet.getLastRow();
 
 for (let i = 2; i <= lastRow; i++ ) {
   const targetSsId      = listSheet.getRange(i, 2).getValue();
   const targetSheetName = listSheet.getRange(i, 3).getValue();  
   
   const targetSs    = SpreadsheetApp.openById(targetSsId);
   const targetSheet = targetSs.getSheetByName(targetSheetName);
   
   targetSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
 } 
}

用途に応じて使いるべきでは

例えば自分の業務範囲で言うと、
「日々データが更新され、かつ参照先が少なければIMPORTRANGEを用いたほうがよさそう」
「逆に、月次で締めるデータはその後データに変更がないので、Google Apps Scriptを用い参照先それぞれにデータをペーストしたほうが良いのでは」と思うようになりました。

新しい関数/スクリプトを知るとその有用さに心動かされ、様々な環境構築を一気に進めたくなりがちですが、時折振り返ってメンテしなおしたり、メンテを前提としてあまり「作り込まない」ことも重要だと考えます。