Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

Google Apps ScriptAdvent Calendar 2022

Day 10
@takatama(高玉 広和)

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

Last updated at Posted at 2022-12-09

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

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

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

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

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

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

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

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

  • スプレッドシートを作成後、上部メニューから、拡張機能 ＞ Apps Script、を選びスクリプトエディターを開きます。
  • 次に、スクリプトエディターで、次の2つのファイル、コード.gsとIndex.htmlを作ります。
    • コード.gsは最初から準備されていますが、Index.htmlは、スクリプトエディターの左ペインでプラス（＋）ボタンを押しHTMLを選択して、（Index.html）ではなくIndexとだけ入力して作成します。
コード.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');
}
Index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>あなたのメールアドレスは <?= email ?> です。</p>
  </body>
</html>
  • スクリプトエディター右上にある「デプロイ」ボタンから「新しいデプロイ」を選び、アプリの種類で「ウェブアプリ」を選択の上、デプロイします。
    • 「次のユーザーとして実行」は「自分」を選びます。
    • 「アクセスできるユーザー」は「＜組織名＞ 内の全員」を選びます。
    • 上記をそれぞれ選ぶことで、スプレッドシートを相手に共有する必要がなくなります。
  • 権限が求められたら付与してください。

なお、スクリプトを書き換えた場合は、スクリプトエディターで再デプロイが必要になりますのでご注意ください（詳しくはこちらで紹介しています）。

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

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

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

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

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

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

image.png

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

image.png

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

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

コード.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に変換して出力します。

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

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

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

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

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

image.png

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

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

コード.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');
}
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

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

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

  • 提出物にユーザーIDを表すQRコードを貼る
  • 先生が提出物を受け取ると、QRコードリーダーで読み取り、提出物の評価をスプレッドシートに記入する
    • 先生はAdminとして登録しておき、Adminがアクセスした場合はウェブアプリにQRコードリーダーを表示する
    • QRコードリーダーにはプルダウンメニューを表示し「どの課題に対する提出か？」を一緒に記録する
  • 学生がウェブアプリにアクセスすると、提出物の受領時刻と、評価結果を確認できる

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

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

トラブルシューティング

Q. 自分でアクセスしたときはメールアドレスが取得できるのに、他人がアクセスするとメールアドレスが取得できません。

  • A. 個人で使えるGoogle Driveではなく、法人や学校で契約したGoogle Workspaceを使っているかどうかを確認してください。

Q. 他人がアクセスすると、スプレッドシートにアクセス権を求める画面が表示されます。

  • A. デプロイするときに、次の設定にしているか確認してください。もし設定が違う場合は、再でプリをしてください（詳しくはこちらで紹介しています）。
    • 「次のユーザーとして実行」は「自分」を選びます。
    • 「アクセスできるユーザー」は「＜組織名＞ 内の全員」を選びます。

Q. スクリプトを書き換えて、ブラウザーからアクセスし直しているのですが、書き換えた機能が反映されていないようです。

  • A. 開発用と本番用のURLがあります。開発用は最新のスクリプトが反映されていますが、本番用だと再デプロイが必要です。再デプロイはこちらをご覧ください。

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

2
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?