【GAS】コピペで完成！スプレッドシートで相互評価システムを作る (1) コード解説（サーバーサイド編）

過去に投稿した記事

について、技術的な解説をしたいと思います。

🎯 作ったもの

こんな感じのWebアプリです。

特徴

• ログイン不要: Googleアカウントでアクセス制御（校内限定公開にできる）
• 動的フォーム: スプレッドシートのヘッダーを変えるだけで、質問項目が自動で変わる
• リアルタイム集計: 自分の評価済みステータスが一覧でわかる
• 管理者機能: ワンクリックで全員の平均点を算出して名簿に書き戻し
• 排他制御: 同時アクセスしてもデータが壊れない堅牢設計

🛠 システムの仕組み

このシステムは以下の3つのファイル（とスプレッドシート）で構成されています。

1. スプレッドシート: データベース兼、管理画面
2. main.gs: サーバーサイドロジック（★今回はここを解説）
3. index.html / css.html: フロントエンド（Vue.js等は使わずVanilla JSで実装）

📋 スプレッドシートの構成

スクリプトを動かすために、以下の3つのシートを用意しています。

1. 設定シート (設定)

アプリのタイトルや、入力期間・結果公開期間のフラグを管理します。

	A	B
1	設定項目	値
2	ページタイトル	2025年度 上期相互評価
3	入力期間中	TRUE
4	結果公開中	FALSE

2. メンバーシート (メンバー)

評価対象者のリストです。

• A列: Email (識別キー)
• B列: グループ/氏名 (評価対象-表示用)
• C列: 評価する本人の氏名等
• D列以降: 集計結果が自動で書き込まれます

3. データシート (相互評価データ)

回答データと質問定義を行います。ここがこのシステムのキモです。

• 1行目: 質問の文章
• 2行目: 質問タイプ (R: 4段階評価, T: テキスト感想)
• 3行目以降: ユーザーからの回答データが蓄積されます
  

💻 コード解説 (main.gs)

実際のコードにおける「こだわりのポイント」をいくつか紹介します。

1. 質問項目の動的生成 (fetchEvaluationForm)

通常、質問項目を増やすにはHTMLとGASの両方を修正する必要がありますが、このシステムではスプレッドシートのヘッダーを読み取ってJSONを生成し、フロントエンドに渡しています。

// スプレッドシートから質問とタイプを取得
const headers = sheet.getRange(1, 1, 2, sheet.getLastColumn()).getValues();
const questionList = headers[0].slice(SYSTEM_CONFIG.COLUMN_OFFSET); // 質問文
const questionTypes = headers[1].slice(SYSTEM_CONFIG.COLUMN_OFFSET); // タイプ(R/T)

これにより、エンジニアでない人でもスプレッドシートを編集するだけで、評価項目の増減が可能になります。

2. 排他制御とUpsert処理 (saveEvaluationData)

Webアプリ化する際、最も怖いのが「同時書き込みによるデータの競合」です。 LockService を使うことで、安全に書き込みを行っています。
また、同じ人に対して2回送信した場合は、行を増やすのではなく**既存の回答を探して上書き（Update）**する処理を入れています。これにより「修正したいけどできない」というトラブルを防げます。

const lock = LockService.getScriptLock();
if (lock.tryLock(10000)) {
  try {
    // ... 既存データの検索 ...
    if (updateIndex >= 0) {
       // 既存行を更新
       sheetData.getRange(updateIndex + 3, ...).setValues([rowData]);
    } else {
       // 新規追加
       sheetData.appendRow(rowData);
    }
  } finally {
    lock.releaseLock();
  }
}

3. 管理者用メニューの追加 (onOpen)

GASエディタを開かなくても集計ができるよう、スプレッドシートのメニューバーにカスタムメニューを追加しています。

function onOpen() {
  SpreadsheetApp.getUi().createMenu("結果集計")
    .addItem("評価結果の集計", "aggregateResults")
    .addItem("評価結果クリア", "resetAggregation")
    .addToUi();
}

🚀 導入方法（配布テンプレート）

以前の記事にテンプレートのリンクがあります。そちらから入手してください。

📝 ソースコード全文

main.gs の全文を掲載します。ご自身でカスタマイズしたい方は参考にしてください。

/**
 * 相互評価システム (Server-side Script)
 * Final Version (JSON版)
 * © 2025 Shigeru Suzuki
 */

// ==================================================
// 1. 設定・定数
// ==================================================
const SYSTEM_CONFIG = {
  SHEET_NAME_SETTINGS: '設定',
  SHEET_NAME_MEMBERS: 'メンバー',
  SHEET_NAME_DATA: '相互評価データ',
  COLUMN_OFFSET: 3,
  ROW_OFFSET: 2,
  
  RATING_OPTIONS: ['4:そう思う', '3:やや思う', '2:やや思わない', '1:思わない'],
  RATING_SCORES: [4, 3, 2, 1]
};

// ==================================================
// 2. Webアプリ エントリーポイント
// ==================================================
function doGet(e) {
  const template = HtmlService.createTemplateFromFile('index');
  const config = _fetchSystemConfig();
  return template.evaluate()
    .addMetaTag('viewport', 'width=device-width, initial-scale=1')
    .setTitle(config.pageTitle || '相互評価システム');
}

// ==================================================
// 3. クライアント用API
// ==================================================

function fetchUserAndGroupData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const myEmail = Session.getActiveUser().getEmail();
  
  const sheetMembers = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_MEMBERS);
  const memberData = sheetMembers.getDataRange().getValues();
  memberData.shift(); 

  const myData = memberData.find(row => row[0] === myEmail);
  if (!myData) {
    return JSON.stringify({ error: '名簿にあなたのメールアドレスが登録されていません。' });
  }

  const allGroups = memberData.map(row => row[1]).filter(g => g !== "");
  const uniqueGroups = [...new Set(allGroups)];
  const groupList = uniqueGroups.map(groupName => [groupName, '']);

  const sheetEvaluate = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_DATA);
  if (sheetEvaluate.getLastRow() > 2) {
    const evalData = sheetEvaluate.getRange(3, 1, sheetEvaluate.getLastRow() - 2, 3).getValues();
    
    const myEvaluationsMap = new Map();
    evalData.forEach(row => {
      if (row[1] === myEmail) {
        myEvaluationsMap.set(row[2], row[0]);
      }
    });
    
    groupList.forEach(groupRow => {
      const targetName = groupRow[0];
      if (myEvaluationsMap.has(targetName)) {
        groupRow[1] = myEvaluationsMap.get(targetName);
      }
    });
  }

  return JSON.stringify({
    systemConfig: _fetchSystemConfig(),
    userEmail: myEmail,
    userName: myData[2],
    userGroup: myData[1],
    groupList: groupList
  });
}

function fetchEvaluationForm(targetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const myEmail = Session.getActiveUser().getEmail();
  const sheet = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_DATA);
  
  const headers = sheet.getRange(1, 1, 2, sheet.getLastColumn()).getValues();
  const questionList = headers[0].slice(SYSTEM_CONFIG.COLUMN_OFFSET);
  const questionTypes = headers[1].slice(SYSTEM_CONFIG.COLUMN_OFFSET);

  let answerList = [];
  if (sheet.getLastRow() > 2) {
    const data = sheet.getRange(3, 1, sheet.getLastRow() - 2, sheet.getLastColumn()).getValues();
    const existingRow = data.find(row => row[1] === myEmail && row[2] === targetName);
    if (existingRow) {
      answerList = existingRow.slice(SYSTEM_CONFIG.COLUMN_OFFSET);
    }
  }
  
  if (answerList.length === 0) {
    answerList = new Array(questionList.length).fill('');
  }

  return JSON.stringify({
    targetName: targetName,
    questionList: questionList,
    questionTypes: questionTypes,
    ratingOptions: SYSTEM_CONFIG.RATING_OPTIONS,
    answerList: answerList
  });
}

function saveEvaluationData(jsonString) {
  const lock = LockService.getScriptLock();
  if (lock.tryLock(10000)) {
    try {
      const data = JSON.parse(jsonString);
      const targetName = data.targetName;
      
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const sheetMembers = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_MEMBERS);
      const memberData = sheetMembers.getDataRange().getValues();
      const exists = memberData.some(row => row[1] === targetName);
      if (!exists) {
        throw new Error("不正な評価対象です。");
      }

      const sanitizedAnswers = data.answerList.map(ans => {
        return (ans || "").toString().replace(/\r?\n/g, '');
      });

      const myEmail = Session.getActiveUser().getEmail();
      const now = new Date();

      const sheetData = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_DATA);
      const rowData = [now, myEmail, targetName, ...sanitizedAnswers];
      const lastRow = sheetData.getLastRow();

      if (lastRow <= 2) {
        sheetData.appendRow(rowData);
      } else {
        const range = sheetData.getRange(3, 1, lastRow - 2, 3);
        const metaData = range.getValues();
        const updateIndex = metaData.findIndex(row => row[1] === myEmail && row[2] === targetName);

        if (updateIndex >= 0) {
          sheetData.getRange(updateIndex + 3, 1, 1, rowData.length).setValues([rowData]);
        } else {
          sheetData.appendRow(rowData);
        }
      }
      return JSON.stringify({ status: 'success' });

    } catch (e) {
      return JSON.stringify({ status: 'error', message: e.message });
    } finally {
      lock.releaseLock();
    }
  } else {
    return JSON.stringify({ status: 'busy', message: 'サーバー混雑中。再試行してください。' });
  }
}

function fetchFeedbackResults(targetGroupName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_DATA);
  
  const headers = sheet.getRange(1, 1, 2, sheet.getLastColumn()).getValues();
  const questionList = headers[0].slice(SYSTEM_CONFIG.COLUMN_OFFSET);
  const questionTypes = headers[1].slice(SYSTEM_CONFIG.COLUMN_OFFSET);

  const lastRow = sheet.getLastRow();
  if (lastRow <= SYSTEM_CONFIG.ROW_OFFSET) {
    return JSON.stringify({ summaryData: [], commentList: [] });
  }

  const allData = sheet.getRange(
    SYSTEM_CONFIG.ROW_OFFSET + 1, 1, lastRow - SYSTEM_CONFIG.ROW_OFFSET, sheet.getLastColumn()
    ).getValues();
  const targetData = allData.filter(row => row[2] === targetGroupName);

  const summaryData = [];
  summaryData.push(['項目', ...SYSTEM_CONFIG.RATING_OPTIONS]);

  const commentList = [];

  questionTypes.forEach((type, idx) => {
    const qText = questionList[idx];
    
    if (type === 'R') {
      const counts = new Array(SYSTEM_CONFIG.RATING_OPTIONS.length).fill(0);
      let sum = 0;
      let validCount = 0;

      targetData.forEach(row => {
        const ans = row[idx + SYSTEM_CONFIG.COLUMN_OFFSET];
        const selectIndex = SYSTEM_CONFIG.RATING_OPTIONS.indexOf(ans);
        if (selectIndex >= 0) {
          counts[selectIndex]++;
          sum += SYSTEM_CONFIG.RATING_SCORES[selectIndex];
          validCount++;
        }
      });

      let ave = validCount > 0 ? (sum / validCount).toFixed(2) : "0.00";
      const label = `${qText}\n(評価者:${validCount}人, 平均:${ave})`;
      summaryData.push([label, ...counts]);

    } else if (type === 'T') {
      const comments = [];
      comments.push(qText);
      
      targetData.forEach(row => {
        const ans = row[idx + SYSTEM_CONFIG.COLUMN_OFFSET];
        if (ans && ans.toString().trim() !== "") {
          comments.push(ans.toString().replace(/\r?\n/g, '').trim());
        }
      });
      commentList.push(comments);
    }
  });

  return JSON.stringify({
    summaryData: summaryData,
    commentList: commentList
  });
}

// ==================================================
// 4. 管理者用メニュー
// ==================================================

function onOpen() {
  SpreadsheetApp.getUi().createMenu("結果集計")
    .addItem("評価結果の集計", "aggregateResults")
    .addItem("評価結果クリア", "resetAggregation")
    .addToUi();
}

function resetAggregation() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_MEMBERS);
  if (sheet.getLastColumn() > SYSTEM_CONFIG.COLUMN_OFFSET) {
    sheet.getRange(1, SYSTEM_CONFIG.COLUMN_OFFSET + 1, sheet.getLastRow(), sheet.getLastColumn() - SYSTEM_CONFIG.COLUMN_OFFSET).clear();
  }
}

function aggregateResults() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetData = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_DATA);
  const sheetMembers = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_MEMBERS);

  const headers = sheetData.getRange(1, SYSTEM_CONFIG.COLUMN_OFFSET + 1, 2, sheetData.getLastColumn() - SYSTEM_CONFIG.COLUMN_OFFSET).getValues();
  const questionList = headers[0];
  const questionTypes = headers[1];

  if (sheetData.getLastRow() <= 2) return;
  const answers = sheetData.getRange(3, 1, sheetData.getLastRow() - 2, sheetData.getLastColumn()).getValues();
  
  const targets = [...new Set(answers.map(row => row[2]))];
  const resultsMap = {};

  targets.forEach(target => {
    const targetAnswers = answers.filter(row => row[2] === target);
    const count = targetAnswers.length;
    const rowResult = [count];

    questionTypes.forEach((type, qIdx) => {
      if (type === 'R') {
        let sum = 0;
        let valid = 0;
        targetAnswers.forEach(ansRow => {
          const val = ansRow[qIdx + SYSTEM_CONFIG.COLUMN_OFFSET];
          const sIdx = SYSTEM_CONFIG.RATING_OPTIONS.indexOf(val);
          if (sIdx >= 0) {
            sum += SYSTEM_CONFIG.RATING_SCORES[sIdx];
            valid++;
          }
        });
        rowResult.push(valid > 0 ? (sum / valid) : '');
      } else {
        rowResult.push('');
      }
    });
    resultsMap[target] = rowResult;
  });

  const memberData = sheetMembers.getDataRange().getValues();
  const newHeader = ['評価者数', ...questionList];
  sheetMembers.getRange(1, SYSTEM_CONFIG.COLUMN_OFFSET + 1, 1, newHeader.length).setValues([newHeader]);

  const outputData = [];
  for (let i = 1; i < memberData.length; i++) {
    const groupName = memberData[i][1];
    if (resultsMap[groupName]) {
      outputData.push(resultsMap[groupName]);
    } else {
      outputData.push(new Array(newHeader.length).fill(''));
    }
  }

  if (outputData.length > 0) {
    sheetMembers.getRange(2, SYSTEM_CONFIG.COLUMN_OFFSET + 1, outputData.length, outputData[0].length).setValues(outputData);
  }
}

function _fetchSystemConfig() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SYSTEM_CONFIG.SHEET_NAME_SETTINGS);
  const values = sheet.getRange(2, 2, 3, 1).getValues();
  return {
    pageTitle: values[0][0],
    isInputEnabled: values[1][0],
    isOutputEnabled: values[2][0]
  };
}

さいごに

このシステムは、サーバーレスかつ無料で運用できるため、中小規模のチームやプロジェクト単位の振り返りに最適です。 ぜひカスタマイズして使ってみてください！

次回は、このシステムを動かすための「フロントエンド編（index.html）」を解説します。 フレームワークを使わず、モダンなSPA（シングルページアプリケーション）を構築する方法をご紹介します。