【そのまま使える】Google Apps ScriptとGoogle スプレッドシートでポートフォリオを見える化する
こんにちは！KCompany代表のケータです！
本日はGoogle Apps Script(GAS)とスプレッドシートでポートフォリオの見える化を自動化したものをご紹介します！
もし使ってみたいという方がいらっしゃったら、ページの最後の部分をご確認いただければ幸いです。
なお、事例としてポートフォリオの見える化としていますが、応用もできます。後述しますが、処理としてはスプレッドシートにある情報を用いて複数のメアド（ユーザー）に対して、①Googleドライブを一括作成する、②テンプレートから新規スプレッドシートを一括作成する、③スプレッドシートを一括更新する、④Gmailで更新通知を一括送信する。という流れになっています。
ですので、請求書であるとか契約書であるとか、大量のユーザーに対して何かを共有し、かつ更新をかけたい、といった用途であれば何でもいけると思います。
Google Apps ScriptとGoogle スプレッドシートでポートフォリオの見える化するスクリプトを開発した背景
とある教育機関において、ポートフォリオを作成していきたいが、100人以上の生徒に対して、学習の進捗を把握してもらうため毎週ポートフォリオを更新したいといったご要望がありました。
手動で行うのは困難であり、GASを使えば簡素化できるのではないかということで開発をすることにしました。
全体的なストーリーとしては、以下の通りです。
ユーザーのメアドや成績情報が集約された「マスターシート」およびポートフォリオのテンプレートとなる「テンプレート」シートのあるスプレッドシート（マスタースプレッドシート）を用意する。このスプレッドシートでGASを組み以下の実行を行う。
授業全体の親フォルダを手動で作成する。
GASで親フォルダ内に各ユーザーごとのGoogleドライブを一括で作成する。同時に、各ユーザーに対して閲覧権限を付与する。
各ユーザーのフォルダ内に、スプレッドシートを一括で作成する。同時に、各ユーザーに対して閲覧権限を付与する。
各ユーザーのフォルダ内に格納されたスプレッドシートに対して、マスタースプレッドシートで毎週の成績の反映が終わったら、一括で更新処理を行う。
一括で更新処理行われたら、各ユーザーに対して個別にGmailで更新通知を行う。
Google Apps ScriptとGoogle スプレッドシートでポートフォリオの見える化するスクリプトのご紹介
さっそくですが、今回のサンプルのご紹介です。機能としては大きく分けて3種類あります。
Googleドライブフォルダ作成機能
親フォルダIDを指定し、そのフォルダ内に各ユーザー用のGoogleドライブフォルダを一括で作成します。
作成時に、各ユーザーのメアドに対して、閲覧権限で共有を行います。
ポートフォリオ更新/新規作成機能
マスタースプレッドシートにある「テンプレート」シートをベースに、各ユーザーごとのポートフォリオの新規作成および更新を行います。
新規作成では、マスタースプレッドシートにあるGoogleドライブフォルダIDに対して、新しいスプレッドシートを一括で作成していきます。同時にマスタースプレッドシートにあるメールアドレスに対して閲覧権限を付与します。
更新では、マスタースプレッドシートに対して反映されている各週の成績を、「テンプレート」シートをベースに一括で反映していきます。
Gmailでポートフォリオ更新を通知
ポートフォリオの更新時に、Gmailで各ユーザーに対して更新があったことを通知します。
画面と動作イメージは下記の通りです。
マスタースプレッドシートの構成
まず、マスタースプレッドシートの構成を紹介します。
マスタースプレッドシートの中の「マスターデータ」シートの説明です。
①ユーザー情報
学籍番号（ID）や氏名、メールアドレスの情報がのっています。ここに記載されたメールアドレスに対して、ドライブやスプレッドシートの閲覧権限を付与します。
②成績情報
授業毎の成績情報が反映されています。この部分は今回サンプル用に手動ないし計算式などで値が入力されています。この部分について、自動化したいなどといった場合は別途処理を作成する必要がでてきます（CSVアップロードをして連携させるなど）
③ユーザー毎のドライブ/スプレッドシート
ここの部分には後でGoogleドライブフォルダやスプレッドシートを一括作成した場合に該当ファイル・フォルダへのURLが転記されます。
マスタースプレッドシートの中の「テンプレート」シートの説明です。
「マスターデータ」にある各ユーザーの成績を、こちらのテンプレートに転記してその内容が各ユーザーの個別のフォルダにある各スプレッドシートに反映される、といった処理になります。
Googleドライブフォルダ作成機能 (GASで一括フォルダ作成)
今回の成果物でポートフォリオを作成したいといった場合、まず各ユーザーのGoogleドライブフォルダを作成する必要があります。さらに前段階として、そのフォルダをどこに格納するのか（親フォルダをどこにするのか）？というのを決める必要があります。
親フォルダについては、手動でご自身で作成して頂く必要があります。今回はサンプルとして「基本情報学」という親フォルダを作成します。
親フォルダを作成したら親フォルダのIDを取得します。IDはGoogleドライブのURLを取得した時の下記の「{フォルダID}」の部分がIDに該当します。
https://drive.google.com/drive/u/1/folders/{フォルダID}マスタースプレッドシートの「メニュー＞カスタムメニュー＞Gドライブフォルダ作成」を実行します。
そして、先ほどのフォルダIDを入力します。
すると、ユーザーの分だけGoogleドライブフォルダが作成されます。フォルダの作成が完了するとマスタースプレッドシートにGoogleドライブのURLが転記されます。
今回はサンプルで3名分の作成としていますので、3つのフォルダが生成されます。
また、マスタースプレッドシートにあるメールアドレスに対して閲覧権限での共有もされています。
動画で一連の流れを確認したい方は以下の動画スクリーンショットを参照ください。
これでポートフォリオ作成をするための準備が整いました。
ポートフォリオ更新/新規作成機能 (GASでスプレッドシートを一括更新/作成)
現段階では各ユーザーごとのポートフォリオのスプレッドシートがまだありません。まずはポートフォリオを新規作成します。
以下２つの画像の流れの通りに実行することで、各ユーザーごとのスプレッドシートが作成されます。
マスタースプレッドシートにポートフォリオ用のスプレッドシートのURLも記載されています。
各ユーザーごとのドライブフォルダの中に格納されていることも確認できます。
この段階ですとまだ成績情報等は何も反映されておらず、何も情報のないスプレッドシートとなっています。
動画で一連の流れを確認したい方は以下の動画スクリーンショットを参照ください。
そのため、成績情報を反映するために「ポートフォリオを更新する」を実行する必要があります。
こちらを実行すると、下記のように各ユーザーのポートフォリオの内容が更新されます。
以下の動画スクリーンショットでは、3つのポートフォリオでそれぞれ更新されていることを確認できます。
Gmailでポートフォリオ更新を通知（GASでメール送信）
最後に、ポートフォリオ更新時に、各ユーザーに対してポートフォリオ更新の通知をGASを通じてGmailで行います。
注意点
自分用に自力でカスタマイズしたい！という場合は、スプレッドシートおよびスクリプトを多少なりとも修正する必要があります。
使ってみたいけど、カスタマイズしたい＆自力でやるのはめんどう！という方は、下記【お仕事のご依頼について】からお問合せ頂くのでも大丈夫です。
Google Apps Scriptとスプレッドシートでポートフォリオの見える化するスクリプトを実際に使うには
ここまで読んでくださって、実際に使ってみたい！という方がいらっしゃいましたら、以下からぜひご利用いただければ幸いです。閲覧用のファイルを用意してますので、そちらをコピーし、ご自身の環境にてご利用いただくイメージになります。
もしくは、下記のメルマガ登録を頂ければ、特典として閲覧用のファイルを共有させて頂きます。
【お仕事のご依頼について】
こちらの記事に記載した内容以外にも、Google Apps Script開発やGoogle Workspaceを活用したデジタル化推進のお仕事を行っています。【GAS】そのまま使えるシリーズのカスタマイズのご相談ももちろん大丈夫です。
ちょっとしたお困りごとでも、こちらのサイトのお問い合わせフォームからご連絡いただければ、折り返しご連絡いたします。
Google Apps Scriptとスプレッドシートでポートフォリオの見える化するスクリプトへのアクセスについて
ここから先は
¥ 980
よろしければサポートをお願いします！いただいたサポートは、サービス開発・改善のために使わせていただきます。主に書籍代や勉強会の参加費用などを想定しています。よろしくお願いします。