#103 複数のGoogle スプレッドシートを一度に印刷する方法（補足） ：いただいたコメントへの回答（IMPORTRANGE関数を使う？）
以下の記事にコメントを頂きました。このコメントでのやり取りは、共有しておいた方がいいと思ったので、別記事として残しておきます。
こんにちは。いつも本当に素敵な記事をありがとうございます。
夏休みの生活記録のようなものを各生徒に配信したので、それを一括で印刷したいと思い、こちらのGasを活用させていただこうとしたのですが、
各生徒のシートを集約すると、シート内が#REF!だらけになってしまいました。集約したいシート以外のシートのセルを参照できないことが原因かとは思いますが、グラフやセルの色などはそのままに、他のシートのセルの値を参照している場合には#REF!にならないように、値を持ってくるという事は可能でしょうか？
いただいたコメントには、以下のように返答しています。
コメントありがとうございます。
小難しい話になりますが... このプログラムは単純に対象のシートを copyTo メソッドでコピーしているだけなので、その結果としてシート内の数式のリンクが切れてしまい、参照できなくなっているのだと思います。 ※その結果の #REF!
https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=ja#copytospreadsheet
対応策としては、リンク先となるシートが固定できるのであれば、以下 URL で説明されているように IMPORTRANGE関数 を用いて参照するようにしてやれば、一旦は #REF! と表示されるものの［アクセスを許可］をクリックすれば値が参照できます。
https://allabout.co.jp/gm/gc/488144/
こんな方法で対応できますか？
上記のコメントでは、あまり長文になるのもはばかられると思ったので、簡単な記述となっていますが、上記の返答内容について文字数を気にせずに書き連ねます。
GAS のプログラムでの対応が難しい理由
１．ただコピーしているだけ
このプログラムでは、以下 URL のリファレンスにあるように Class Sheet の copyTo メソッドによって、集約先となるスプレッドシートにコピーしています。
これは、下図のような UI の操作を API としておこなっているものです。
別のシートを参照している数式がシート内に存在している場合、上記の「copyTo メソッドでのコピー」「UI の操作でのシートのコピー」のどちらで行っても、#REF! となってしまいます。
これは、
集約した先のスプレッドシート（ワークブック）に当該シートが存在しない
プログラムではシート名が重複しないように変更している
という要因で、当該の数式が目的のセルを参照できなくなって #REF! となっているものです。
２．元のシートのセルを参照するようにしては？
対応策としてコメントで提示したのは IMPORTRANGE関数 を用いて、元のシートのセルを参照するようにしてはどうか？というものです。
IMPORTRANGE関数 を用いて参照するようにすると、下図のように参照するスプレッドシートごとに「アクセスを許可」のボタンを押して、権限を確認しなければなりません。
３．GAS のプログラムで対応できない？
まぁ、IMPORTRANGE関数を設定するのは面倒ですよね…
元のシートで別のシートを参照しているようであれば、IMPORTRANGE関数を用いた表記に変更するというのは、できないわけではないのですが、いろいろと制限が多いように感じます。
様々な表記が行われている数式をチェックして、他のシートを参照している場合には IMPORTRANGE 関数で置き換えなければならない。
シート内のすべてのセルに対して、数式が設定されていないかをチェックして処理しなければならない。
他のシートの参照は、単一引用符（シングルクォーテーション：' '）で囲まれているのでしょうが、すべての数式での表記を網羅しきれるかが確信できません。
また、それらのチェックと処理をすべてのセルに対して行うと、相応の時間を要してしまうので、集約処理に要する時間が長くなってしまいます。
基準となるスプレッドシートを先生側で作成し、別のシートを参照している数式も先生側で設定しているのであれば、その数式を IMPORTRANGE 関数を用いるようにしたら、今回の問題視されている事象は発生しなくなります。
まとめ
上述のような理由から、今回は GAS のプログラム側での対応を断念しています。ご理解ください…🙇
この記事が気に入ったらサポートをしてみませんか？