#60 課題に添付したファイルの一覧を作成する、そして IMPORTRANGE 関数

2022年12月9日 19:02

もともとは、

Classroomの課題でコピーを配布し、各自が5回の得点を入力します。それを各回ごとに一覧表にしたい

というニーズを満たすにはどうしたらよいのかを考えていたのですが…　GAS のプログラムでは記事タイトルにあるように、課題に添付されたファイルの一覧を作成するところまでを行うことにしました。
最終的な成果物である一覧表も、GAS で生成できないわけではないのですが、その部分をプログラム化してしまうと、目的ごとに一覧表の体裁が異なると、それをプログラムに反映させなければならないので、共通化にはならないような感じがしたので、プログラムと手作業の分業としました。

具体的なイメージ

Google Classroom で課題として配布されたのは、下図のようなスプレッドシートです。

このスプレッドシートに、それぞれの生徒が入力した内容を下図のように一つのシートに集約するイメージです。　※上図の配布したスプレッドシートの色付きのセルを行と列を入れ替えて貼り付ける感じです。

他のスプレッドシートから任意のセルの値を参照するためには、IMPORTRANGE 関数を用いることにしました。この IMPORTRANGE 関数を使用するために必要な、それぞれの生徒に配布したファイルの ID を取得するための GAS のプログラムを作成した感じです。

作成したプログラム

「作成した」というよりは、以前に作成した以下のプログラムを、少しだけ変更した感じになっています。　※詳細は後述しますが、プログラムそのものは、ほとんど変更していません。

課題を作成した先生のアカウントでログインした状態で、以下の URL にアクセスして、「管理シート」を、自身の Google ドライブにコピーします。

https://docs.google.com/spreadsheets/d/1k_QjXrD0aA5oZetX-NmbpEVHTBG4ed-v6kmr71bliIs/copy

コピーしたスプレッドシートでプログラムを実行するときには、初回だけ権限の確認が行われます。その辺りの説明は、以下の記事をご覧ください。

今回のプログラムは、前述のように #56 のプログラムをもとに作成してあります。#56 は同様の処理を Google スライド用に特化させたものですが、今回のプログラムは Google スプレッドシート用となっています。不要な部分の関数（プログラム）を削除すると、うまく必要な権限の設定が行われなかったために、プログラム中には使用していない #56 の関数が残ったままになっています。
その結果として、権限の確認時に Google スライドに関する権限が求められています。
本来であれば、原因を追究して対処するべきなのでしょうが、違和感を覚えるだけで実害はないと考えられたので、このままにしておきます。
今後、対処法がわかれば対応します。

操作手順

コピーされたスプレッドシートは、下図のようなボタンが 3つ配置されたシート「設定」の他に、次の 3つのシートで構成されています。

シート「クラス一覧」
シート「課題一覧」
シート「提出物一覧」　← このプログラムの実行結果

コピー元となっているスプレッドシート
（#56 をスプレッドシート用に変更しただけ）

列 F から右にも説明してありますが、上から順番にボタンを押して、ドロップダウンリストで、処理対象を選択していけば、シート「提出物一覧」にファイル一覧が作成されます。

「参加クラスの取得」ボタンを押す。
セル D2 のドロップダウンリストで、処理対象となるクラスを選択する。
「課題を取得」ボタンを押す。
セル D5 のドロップダウンリストで、処理対象となる課題を選択する。
「提出物の一覧を取得」ボタンを押す。

課題を提出した直後は、システム側でファイルの割り当て処理を行っているために、Google Classroom で「割り当て済み」と表示されていても、このプログラムの実行結果と相違がある場合があります。
そのような場合には、数分待ってから再度実行してみてください。

シート「提出物一覧」には、Classroom API で取得できる多くの情報が記録されていますが、実際に使用するのは列 C「name.fullName」と列 O「FileID」だけだと思います。

列 A：id
列 B：userId
列 C：name.fullName
列 D：creationTime
列 E：updateTime
列 F：state
列 G：late
列 H：draftGrade
列 I：assignedGrade
列 J：alternateLink
列 K：courseWorkType
列 L：associatedWithDeveloper
列 M：assignmentSubmission
列 N：MimeType
列 O：FileID

列 C の内容から一覧表を作成する際に必要な生徒名などの情報を VLOOKUP 関数などで用意してください。

以降では、添付されているファイルが Google スプレッドシートであることを前提に、IMPORTRANGE 関数で集約することを想定していますが、他の形式のファイルでも別の応用が可能かもしれません。
面白いアイディアなどがあれば、コメントなどで教えてください。

IMPORTRANGE 関数について

前項までのプログラムで得られたシート「提出物一覧」によって、生徒に配布された Google スプレッドシートの「FileID」がわかります。
この情報を用いて、IMPORTRANGE 関数でそれぞれの生徒の入力した内容を集約します。今回の例を用いて、少しだけ IMPORTRANGE 関数の使用例を説明しておきます。

Google が提供している INPORTANGE 関数についてのヘルプ記事も、参照してください。

下図のような一覧表を作成する場合に、1行目には、生徒が入力する Google スプレッドシートから抽出するセルを文字列として指定しています。　※今回は、冒頭に例示した生徒に配布したスプレッドシートにおいて、セルに色が付けられていた部分からデータを取得します。

4行目以降に、次のように関数を設定します。

セル A4
='提出物一覧'!C2

セル B4
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/"&'提出物一覧'!$O2,"集計用紙!"&B$1)

このように設定したセル A4 と B4 の内容を、オートフィル機能で他のセルにもコピーしました。

IMPORTRANGE 関数ではじめてアクセスしたファイルに対しては、下図のように「#REF!」と表示され、実際の値が表示されません。
「アクセスを許可」ボタンを押すことで、値が表示されます。　※この確認は、参照するファイルごとに行わなければなりません。

注意するポイント

IMPORTRANGE 関数に限りませんが…
オートフィル機能でコピーしても参照先がずれないように、$ を付ける位置に注意してください。
IMPORTRANGE 関数の 2番目の引数には、シート名も指定しなければならないため、生徒が配布したファイルのシート名を変更してしまった場合には参照できなくなります。
IMPORTRANGE 関数は、スプレッドシートを開いたタイミングでそれぞれの指定されたスプレッドシートからデータを取得してくるため、常に最新の状態が表示されます。
※前述のヘルプ記事によれば、スプレッドシートを開いたままの状態にしていると、1時間間隔で更新が確認されるようです。
IMPORTRANGE 関数のヘルプ記事でも説明されていますが、IMPORTRANGE 関数による参照数が多すぎるとエラーになる場合があります。どれぐらいの参照数でエラーになるのかは明記されていません。

最後に

前述したように…
現時点では意図しない権限を求められるプログラムとなっています。#56 のプログラムを流用し、使用していない部分のプログラムを削除すると、実行に必要な権限が求められなくなり、実行時にエラーになってしまうのです。
本体であれば、原因を追究して対処すべきなのでしょうが、試行錯誤したものの原因究明には至らなかったので、ひとまず現状のままで公開することにしました。うまく対処方法がわかれば、対応しようと思ってはいます…

最後に、お決まりのフレーズなどを書いておきます。

一応の動作確認は行っているものの、不慮のトラブルによって損害等が生じても、責任はとれませんので予めご了承ください。
コメントを含めても 350行程度のスクリプトであり、実行に際して目的外の場所への書き出しや収集などは行っていないはずです。　※350行には使用していない関数も含んでいます。
特別なエラー処理は行っていないので、意図しないケースでエラーが発生してしまうかもしれません。どうにもならない場合には、ご連絡ください。

わたし自身にしてみると、このような「スクリプトを作ること」が目的になっているような感じですが、このスクリプトが何かの役に立てば幸いです。