スプレッドシートの内容をアクセスした人に応じて表示するウェブアプリ【Google Workspace（for Education）用】

Googleスプレッドシートの情報を、Aさんには全て見せるけど、Bさんには一部しか見せない、というように、アクセスする人に応じて出し分ける方法をご紹介します。

Aさん用、Bさん用、...と複数のスプレッドシートを自動生成することもできますが、Aさん、Bさんが情報を「見るだけで十分」な場合は、Google Apps Scriptのウェブアプリとして公開するのが便利です。

https://developers.google.com/apps-script/guides/web

ウェブアプリにアクセスした人が誰なのかは、Google Workspace（for Education）の機能を使い、メールアドレスから特定します。逆に言うと、Google Workspaceを使っていない場合はうまく動作しません。ご注意ください。

以前ご紹介した、Apps ScriptだけでQRコード[^1]リーダーを作る記事と合わせて使えば、学校での提出物管理システムを作ることもできます（後述します）。

https://qiita.com/takatama/items/5ef6b31c4f228f0393e1

まず、基本的なウェブアプリ（といってもそこそこ複雑ですが...）を作ってから、画面に氏名を表示する機能と、管理者には全ての情報を見せる機能を追加していきます。

# Google Workspaceでウェブアプリにアクセスしてきた人のメールアドレスを取得する

Google Workspaceを使っている場合は、```Session.getActiveUser().getEmail()```でアクセスしてきた人のメールアドレスを取得できます。Google Workspaceを使っていない場合は空文字列になります。

https://developers.google.com/apps-script/reference/base/session#getactiveuser

まずは簡単なウェブアプリをApps Scriptを使って作ってみます。アクセスしてきた人のメールアドレスをウェブ画面に表示します。Templated HTMLの「テンプレート変数へのプッシュ」を利用します。

https://developers.google.com/apps-script/guides/html/templates#pushing_variables_to_templates

- スプレッドシートを作成後、上部メニューから、拡張機能 ＞ Apps Script、を選びスクリプトエディターを開きます。
- 次に、スクリプトエディターで、次の2つのファイル、```コード.gs```と```Index.html```を作ります。
  - ```コード.gs```は最初から準備されていますが、```Index.html```は、スクリプトエディターの左ペインでプラス（＋）ボタンを押し```HTML```を選択して、（Index.html）ではなく```Index```とだけ入力して作成します。

```javascript:コード.gs
function getEmail() {
  const email = Session.getActiveUser().getEmail();
  if (email === '') throw 'メールアドレスを取得できません。';
  return email;
}

function doGet() {
  const html = HtmlService.createTemplateFromFile('Index');
  const email = getEmail(); 
  html.email = email;
  return html.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
```

```html:Index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>あなたのメールアドレスは <?= email ?> です。</p>
  </body>
</html>
```

- スクリプトエディター右上にある「デプロイ」ボタンから「新しいデプロイ」を選び、アプリの種類で「ウェブアプリ」を選択の上、デプロイします。
  - 「次のユーザーとして実行」は「自分」を選びます。
  - 「アクセスできるユーザー」は「＜組織名＞ 内の全員」を選びます。
  - 上記をそれぞれ選ぶことで、スプレッドシートを相手に共有する必要がなくなります。
- 権限が求められたら付与してください。

なお、スクリプトを書き換えた場合は、スクリプトエディターで再デプロイが必要になりますのでご注意ください（詳しくは[こちら](https://qiita.com/takatama/items/5ef6b31c4f228f0393e1#%E3%82%A6%E3%82%A7%E3%83%96%E3%82%A2%E3%83%97%E3%83%AA%E3%81%AE%E5%86%8D%E3%83%87%E3%83%97%E3%83%AD%E3%82%A4)で紹介しています）。

URLが払い出されるので、PCもしくはスマートフォンのウェブブラウザーからアクセスしてください。

もし「メールアドレスを取得できません。」と表示される場合は、Google Workspaceを使っていないのかもしれません（Google Workspaceは個人で使えるGoogle Driveとは異なり、法人や学校で契約するグループウェアです）。

# アクセスした人に応じて、表示するスプレッドシートの内容を変更する

取得したメールアドレスから表示する内容を抽出してHTMLを作成します。

今回は次の2つのシート、```Data```シートと```Users```シートが準備されているとします。Dataシートでは、いつ、どのユーザーが、どの課題を提出したかを記録しているイメージです。

- Dataシート
  - タイムスタンプ、（メールアドレスとは異なる）ユーザーID、情報

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/6760/8973528a-2ece-b7f9-52cd-34cf7b42ac33.png)

- Usersシート
  - ユーザーID、メールアドレス

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/6760/deb381c3-ca25-66bd-31c2-ec5273718dda.png)

まず、```コード.gs```に、メールアドレスからユーザIDを取得する```fetchUserId(email)```と、ユーザーIDを含む（複数の）行を取得する```fetchRows(userId)```をそれぞれ追加します。また```doGet()```の中で、取得した行をテンプレート変数にプッシュしておきます。

なお、```fetchRows(userId)```では、```Utilities.formatDate()```を使ってDate型を文字列に変換する処理を入れています。

https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format

```javascript:コード.gs
// アクセスしてきた人のメールアドレスを取得
function getEmail() {
  const email = Session.getActiveUser().getEmail();
  if (email === '') throw 'メールアドレスを取得できません。';
  return email;
}

const USERS_SHEET_NAME = 'Users';
// メールアドレスからユーザIDを取得する
function fetchUserId(email) {
  const rows = SpreadsheetApp.getActive().getSheetByName(USERS_SHEET_NAME).getDataRange().getValues().slice(1)
    .filter(row => row[1] === email);
  if (rows.length < 1) throw `${email}に相当するユーザー情報が定義されていません。`;
  if (rows.length > 1) throw `${email}に相当するユーザー情報が複数定義されています。`;
  return rows[0][0];
}

// Date型を文字列に変換
function formatDate(rows) {
  return rows.map(row => [Utilities.formatDate(row[0], 'JST', 'yyyy/MM/dd HH:mm:ss'), row[1], row[2]]);
}

const DATA_SHEET_NAME = 'Data';
// ユーザーIDを含む（複数の）行を取得する
function filterRows(userId) {
  return formatDate(
    SpreadsheetApp.getActive().getSheetByName(DATA_SHEET_NAME).getDataRange().getValues().slice(1)
      .filter(row => row[1] === userId));
}

function doGet() {
  const html = HtmlService.createTemplateFromFile('Index');
  const email = getEmail();
  // テンプレート変数にプッシュする
  html.email = email;
  html.rows = filterRows(fetchUserId(email));
  return html.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
```

次に、```Index.html```では、受け取った行の情報```rows```をHTMLに変換して出力します。

```html:Index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>あなたのメールアドレスは <?= email ?> です。</p>
    <table>
      <tr>
        <th>時刻</th>
        <th>ユーザーID</th>
        <th>情報</th>
      </tr>
      <? for (let row of rows) { ?>
        <tr>
          <td><?= row[0] ?></td>
          <td><?= row[1] ?></td>
          <td><?= row[2] ?></td>
        </tr>
      <? } ?>
    </table>
  </body>
</html>
```

公開したウェブアプリのURLに```0001@example.com```がアクセスした場合、ウェブ画面には次のように表示されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/6760/d51cdd59-8d4d-9315-2c29-36f71abf6ca0.png)

# 氏名表示と管理者機能の追加

さらに、以下の2つの機能追加に挑戦してみます。

- ウェブ画面にアクセスしてきた人の氏名を表示する
- 管理者の場合はすべての情報を表示する

まず、Usersシートに、氏名列と、種別列を追加します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/6760/7d578891-c2e8-838d-422d-61944da8da44.png)

```コード.gs```にメールアドレスからユーザー情報を取得する```fetchUser(email)```を追加します。JSON形式のユーザー情報を取得することで、少し汎用的に作り変えます。

さらにユーザー情報から関連する行を取得する```fetchRowsByUser(user)```を追加して、種別がAdminなら全ての行を、Memberならそのユーザーに関係する行のみを取得できるようにします。

```javascript:コード.gs
// getEmail()、formatDate()、filterRows()は省略

// メールアドレスからユーザー情報を取得する
function fetchUser(email) {
  const rows = SpreadsheetApp.getActive().getSheetByName(USERS_SHEET_NAME).getDataRange().getValues().slice(1)
    .filter(row => row[1] === email);
  if (rows.length < 1) throw `${email}に相当するユーザー情報が定義されていません。`;
  if (rows.length > 1) throw `${email}に相当するユーザー情報が複数定義されています。`;
  return {
    userId: rows[0][0],
    email: rows[0][1],
    name: rows[0][2],
    type: rows[0][3]
  };
}

// ユーザー情報から関連する行を取得する
function filterRowsByUser(user) {
  if (user.type.toLowerCase() === 'admin') {
    return formatDate(SpreadsheetApp.getActive().getSheetByName(DATA_SHEET_NAME).getDataRange().getValues().slice(1));
  }
  return filterRows(user.userId);
}

function doGet() {
  const html = HtmlService.createTemplateFromFile('Index');
  const email = getEmail();
  const user = fetchUser(email);
  // テンプレート変数にプッシュする
  html.user = user;
  html.rows = filterRowsByUser(fetchUser(email));
  return html.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
```

```html:Index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      table td {
        border-top: 1px solid black;
        padding: 3px;
      }
    </style>
  </head>
  <body>
    <p><?= user.name ?>さん、ようこそ！</p>
    <table>
      <tr>
        <th>時刻</th>
        <th>ユーザーID</th>
        <th>情報</th>
      </tr>
      <? for (let row of rows) { ?>
        <tr>
          <td><?= row[0] ?></td>
          <td><?= row[1] ?></td>
          <td><?= row[2] ?></td>
        </tr>
      <? } ?>
    </table>
  </body>
</html>
```

```0001@example.com```の名前は鈴木 花子、種別はAdminのため、ウェブ画面には次のように表示されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/6760/7de9ccda-1465-ff11-deef-f2b4f6151d2f.png)

# 学校での提出物管理システムへの応用

この記事は、Google Workspace for Educationを導入されている、ある高校の先生からの問い合わせで生まれました。
その先生は、冒頭でご紹介した[QRコードリーダー](https://qiita.com/takatama/items/5ef6b31c4f228f0393e1)と、ここで紹介したウェブアプリを組み合わせて、次のような提出物管理システムを運用されています。

- 提出物にユーザーIDを表すQRコードを貼る
- 先生が提出物を受け取ると、[QRコードリーダー](https://qiita.com/takatama/items/5ef6b31c4f228f0393e1)で読み取り、提出物の評価をスプレッドシートに記入する
  - 先生はAdminとして登録しておき、Adminがアクセスした場合はウェブアプリにQRコードリーダーを表示する
  - QRコードリーダーには[プルダウンメニューを表示](https://qiita.com/takatama/items/5ef6b31c4f228f0393e1#%E5%BF%9C%E7%94%A8%E7%B7%A8qr%E3%82%B3%E3%83%BC%E3%83%89%E3%83%AA%E3%83%BC%E3%83%80%E3%83%BC%E3%81%AB%E3%83%97%E3%83%AB%E3%83%80%E3%82%A6%E3%83%B3%E3%83%A1%E3%83%8B%E3%83%A5%E3%83%BC%E3%82%92%E8%BF%BD%E5%8A%A0%E3%81%97%E3%81%A6qr%E3%82%B3%E3%83%BC%E3%83%89%E3%81%A8%E4%B8%80%E7%B7%92%E3%81%AB%E8%A8%98%E9%8C%B2%E3%81%99%E3%82%8B)し「どの課題に対する提出か？」を一緒に記録する
- 学生がウェブアプリにアクセスすると、提出物の受領時刻と、評価結果を確認できる

高校だと一人の先生が受け持つ学生数がとても多いため、こうしたシステムがあるととても助かるそうです。特に、この記事に書いた方法だと、学生に伝えるURLが一つで済むのが便利とのこと。評価結果を書いたスプレッドシートを学生の数だけ自動生成することもできますが、URLも複数になってしまい、その配布が大変になるためです。

他にも便利な応用例があれば、ぜひ教えてください！

# トラブルシューティング

## Q. 自分でアクセスしたときはメールアドレスが取得できるのに、他人がアクセスするとメールアドレスが取得できません。
- A. 個人で使えるGoogle Driveではなく、法人や学校で契約したGoogle Workspaceを使っているかどうかを確認してください。

## Q. 他人がアクセスすると、スプレッドシートにアクセス権を求める画面が表示されます。
- A. デプロイするときに、次の設定にしているか確認してください。もし設定が違う場合は、再でプリをしてください（詳しくは[こちら](https://qiita.com/takatama/items/5ef6b31c4f228f0393e1#%E3%82%A6%E3%82%A7%E3%83%96%E3%82%A2%E3%83%97%E3%83%AA%E3%81%AE%E5%86%8D%E3%83%87%E3%83%97%E3%83%AD%E3%82%A4)で紹介しています）。
  - 「次のユーザーとして実行」は「自分」を選びます。
  - 「アクセスできるユーザー」は「＜組織名＞ 内の全員」を選びます。

## Q. スクリプトを書き換えて、ブラウザーからアクセスし直しているのですが、書き換えた機能が反映されていないようです。
- A. 開発用と本番用のURLがあります。開発用は最新のスクリプトが反映されていますが、本番用だと再デプロイが必要です。再デプロイは[こちら](https://qiita.com/takatama/items/5ef6b31c4f228f0393e1#%E3%82%A6%E3%82%A7%E3%83%96%E3%82%A2%E3%83%97%E3%83%AA%E3%81%AE%E5%86%8D%E3%83%87%E3%83%97%E3%83%AD%E3%82%A4)をご覧ください。

[^1]: QRコードは株式会社デンソーウェーブの登録商標です。