Others

本業で作ったもの

テスト

UserList：ID/ Nickname/ Pass/ メールアドレス

QuizMaster：シート名/ フォームID /種別

PointLog：日時/ 生徒ID/ ポイント数/ 内容 / 種別

csv.

const SS = SpreadsheetApp.getActiveSpreadsheet();

function doGet() {

return HtmlService.createTemplateFromFile("index")

.evaluate()

.addMetaTag('viewport', 'width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no')

.setTitle("QuizPay");

}

// フォーム送信（小テストのみ）を受け取る関数

function onSpreadsheetFormSubmit(e) {

if (!e) return;

const sheet = e.range.getSheet();

const sheetName = sheet.getName();

// QuizMasterシートを確認

const masterSheet = SS.getSheetByName("QuizMaster");

if (!masterSheet) return;

const masterData = masterSheet.getDataRange().getValues();

let category = null;

for (let i = 1; i < masterData.length; i++) {

if (masterData[i][0] === sheetName || masterData[i][0].trim() === sheetName.trim()) {

category = masterData[i][2]; // C列: 種別

break;

}

// 小テスト呼び出し

if (category === "小テスト") {

processQuiz(e, sheetName, category);

}

//会員登録呼び出し

if (category === "会員登録") {

processRegistration(e);

}

// 会員登録

function processRegistration(e) {

const namedValues = e.namedValues;

// フォームの質問項目名に合わせて取得

// ※フォーム側で「メールアドレスを収集する」をONにすると "メールアドレス" というキーが入ります

const email = namedValues["メールアドレス"] ? namedValues["メールアドレス"][0] : "";

const id = namedValues["ID"] ? namedValues["ID"][0].trim() : "";

const pass = namedValues["Pass"] ? namedValues["Pass"][0].trim() : "";

const name = namedValues["Nickname"] ? namedValues["Nickname"][0].trim() : "名無し";

if (!id || !email) return;

const userSheet = SS.getSheetByName("UserList");

const users = userSheet.getDataRange().getValues();

// 重複チェック (A列が生徒IDと仮定)

const exists = users.some(row => row[0] == id);

if (exists) {

// 重複エラーメール送信

const subject = "【登録失敗】IDが既に使用されています";

const body = `ID: ${id} は既に登録されています。\n心当たりがない場合は先生に連絡してください。`;

MailApp.sendEmail(email, subject, body);

} else {

// 登録成功 -> UserListに追加 [ID, 氏名, パスワード, メール]

userSheet.appendRow([id, name, pass, email]);

// 完了メール送信

const subject = "【登録完了】ポイントシステムへようこそ";

const body = `${name} 様\n\n会員登録が完了しました。\nID: ${id}\nPass: ${pass}\n\nアプリにログインしてポイントを貯めましょう！`;

MailApp.sendEmail(email, subject, body);

}

// 採点処理

function processQuiz(e, sheetName, category) {

const namedValues = e.namedValues;

// ID取得

let studentId = "";

const idKeys = ["生徒ID", "ID", "学籍番号", "学籍番号（半角）"];

for (const k of idKeys) {

if (namedValues[k]) { studentId = namedValues[k][0].trim(); break; }

}

// スコア取得（Googleフォーム標準の "スコア" 列から）

let points = 0;

let scoreRaw = "";

const scoreKeys = ["スコア", "Score", "点数"];

for (const k of scoreKeys) {

if (namedValues[k]) { scoreRaw = namedValues[k][0]; break; }

}

if (scoreRaw) {

const match = scoreRaw.match(/^(\d+)/); // "15 / 20" の 15 を抽出

if (match) points = parseInt(match[1]);

}

// PointLogへ記録

if (studentId) {

const logSheet = SS.getSheetByName("PointLog");

logSheet.appendRow([

new Date(),

studentId,

points,

"テスト回答: " + sheetName,

category

]);

}

// Webアプリログイン・表示・送金処理

function getRandomImageUrl() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Photos");

if (!sheet) return "https://picsum.photos/400/200"; // シートがない場合の予備

const data = sheet.getRange("A1:A" + sheet.getLastRow()).getValues();

// 2次元配列を1次元にして、空行を除去

const urls = data.map(row => row[0]).filter(url => url !== "");

if (urls.length === 0) return "https://picsum.photos/400/200";

// ランダムに1つ選択

const randomIndex = Math.floor(Math.random() * urls.length);

return urls[randomIndex];

}

function loginCheck(id, password) {

const userSheet = SS.getSheetByName("UserList");

const users = userSheet.getDataRange().getValues();

let validUser = null;

// A列:ID, B列:氏名, C列:パスワード

for (let i = 1; i < users.length; i++) {

if (users[i][0] == id && users[i][2] == password) {

validUser = { id: users[i][0], name: users[i][1] };

break;

}

if (!validUser) return { success: false, message: "Incorrect ID or Pass" };

const ptData = calculateUserPoint(validUser.id);

return {

success: true,

id: validUser.id,

name: validUser.name,

currentPoint: ptData.current,

totalPoint: ptData.total,

classTotal: calculateClassTotalPoint(),

title: getTitle(ptData.total)

};

}

// ポイント計算（保有残高と、小テスト累計を分けて算出）

function calculateUserPoint(userId) {

const logSheet = SS.getSheetByName("PointLog");

const logs = logSheet.getDataRange().getValues();

let current = 0, total = 0;

for (let i = 1; i < logs.length; i++) {

if (logs[i][1] == userId) {

const p = Number(logs[i][2]);

const type = logs[i][4];

current += p; // 全ての増減（送金含む）

if (type === "小テスト" && p > 0) total += p; // 称号用累計

}

return { current, total };

}

function calculateClassTotalPoint() {

const logSheet = SS.getSheetByName("PointLog");

const logs = logSheet.getDataRange().getValues();

return logs.slice(1).reduce((sum, row) => {

return sum + (row[4] === "小テスト" && Number(row[2]) > 0 ? Number(row[2]) : 0);

}, 0);

}

function getTitle(totalPoints) {

if (totalPoints >= 1000) return "Legend";

if (totalPoints >= 500) return "Elite";

if (totalPoints >= 300) return "Pro";

if (totalPoints >= 100) return "Noob";

return "New Comer";

}

function sendPoints(senderId, recipientId, amount) {

amount = parseInt(amount);

if (senderId === recipientId) return { success: false, message: "You can't send yourself" };

const senderPt = calculateUserPoint(senderId).current;

if (senderPt < amount) return { success: false, message: "Insufficient balance" };

const logSheet = SS.getSheetByName("PointLog");

logSheet.appendRow([new Date(), senderId, -amount, `送金 to ${recipientId}`, "交換"]);

logSheet.appendRow([new Date(), recipientId, amount, `受取 from ${senderId}`, "交換"]);

return { success: true, newBalance: senderPt - amount };

}

HTML

<!DOCTYPE html>

<html>

<head>

<style>

:root { --main: #4a90e2; --accent: #ff9800; --bg: #f4f7f6; }

body { font-family: sans-serif; background: var(--bg); margin: 0; padding: 15px; text-align: center; color: #333; }

.card { background: white; padding: 20px; border-radius: 16px; box-shadow: 0 4px 15px rgba(0,0,0,0.08); max-width: 420px; margin: 0 auto 20px; }

.hidden { display: none !important; }

.header-img {

width: 100%;

height: 180px;

border-radius: 12px;

object-fit: cover;

margin-bottom: 15px;

background: #eee;

transition: opacity 0.5s;

}

input { width: 100%; padding: 12px; margin: 10px 0; border: 1px solid #ccc; border-radius: 8px; font-size: 16px; box-sizing: border-box; }

button { width: 100%; padding: 14px; border: none; border-radius: 8px; font-weight: bold; cursor: pointer; color: white; margin-top: 10px; font-size: 16px; transition: 0.2s; }

.btn-blue { background: var(--main); }

.btn-green { background: #2ecc71; }

.btn-orange { background: var(--accent); }

.btn-gray { background: #eaedf0; color: #555; border: 1px solid #ccc; }

.badge { display: inline-block; padding: 6px 16px; background: linear-gradient(45deg, #ffd700, #ff8c00); color: white; border-radius: 20px; font-weight: bold; font-size: 0.9em; margin-bottom: 10px; }

.pt-big { font-size: 2.8em; font-weight: bold; color: var(--main); }

#video { width: 100%; border-radius: 10px; background: #000; height: 250px; object-fit: cover; }

.error { color: #e74c3c; font-size: 0.9em; }

</style>

</head>

<body>

<h2 id="app-title">QuizPay</h2>

</div>

<div id="user-title" class="badge">Badge</div>

<span id="class-total" style="font-weight:bold; font-size:1.2em;">0</span> pt

</div>

<small>Available Amount</small><br>

<span id="user-pt" class="pt-big">0</span> pt

</div>

<button class="btn-green" onclick="showView('view-qr')">Show QR and Recieve</button>

</div>

<h3>Pay with Balance</h3>

</div>

</div>

<button class="btn-gray" onclick="stopScan()">Cancel</button>

</div>

<button class="btn-green" onclick="executeSend()">Confirm</button>

</div>

let myId = "";

let videoStream = null;

// ページ読み込み時に実行

window.onload = function() {

loadRandomImage();

};

// Photosシートからランダムに画像を取得してセット

function loadRandomImage() {

const imgTag = document.getElementById("random-img");

google.script.run.withSuccessHandler(url => {

imgTag.src = url;

imgTag.style.opacity = 1; // 読み込み後にふわっと表示

}).getRandomImageUrl();

}

function showView(vid) {

["view-login", "view-dash", "view-qr", "view-scan", "view-send"].forEach(id => {

document.getElementById(id).classList.add("hidden");

});

document.getElementById(vid).classList.remove("hidden");

// ログイン以外の画面では画像を隠してスッキリさせる（お好みで）

if (vid !== "view-login") {

document.getElementById("random-img").classList.add("hidden");

} else {

document.getElementById("random-img").classList.remove("hidden");

}

function handleLogin() {

const id = document.getElementById("login-id").value;

const ps = document.getElementById("login-pass").value;

if(!id || !ps) return;

google.script.run.withSuccessHandler(res => {

if(res.success){

myId = res.id;

document.getElementById("user-name").innerText = res.name + " さん";

document.getElementById("user-pt").innerText = res.currentPoint.toLocaleString();

document.getElementById("class-total").innerText = res.classTotal.toLocaleString();

document.getElementById("user-title").innerText = res.title;

document.getElementById("qrcode").innerHTML = "";

new QRCode(document.getElementById("qrcode"), { text: myId, width: 160, height: 160 });

document.getElementById("qr-id-text").innerText = "ID: " + myId;

showView("view-dash");

} else {

document.getElementById("msg-login").innerText = res.message;

}

}).loginCheck(id, ps);

}

function handleLogout() {

myId = "";

document.getElementById("login-id").value = "";

document.getElementById("login-pass").value = "";

document.getElementById("msg-login").innerText = "";

loadRandomImage(); // ログアウト時に次の画像を仕込む

showView("view-login");

}

// --- QR・送金関係の関数（変更なし） ---

function startScan() {

showView("view-scan");

const video = document.getElementById("video");

const canvas = document.getElementById("canvas");

const ctx = canvas.getContext("2d");

navigator.mediaDevices.getUserMedia({ video: { facingMode: "environment" } }).then(s => {

videoStream = s; video.srcObject = s; video.play();

const tick = () => {

if(!videoStream) return;

if(video.readyState === video.HAVE_ENOUGH_DATA){

canvas.height = video.videoHeight; canvas.width = video.videoWidth;

ctx.drawImage(video, 0, 0, canvas.width, canvas.height);

const code = jsQR(ctx.getImageData(0,0,canvas.width,canvas.height).data, canvas.width, canvas.height);

if(code){ stopScan(); document.getElementById("target-id-disp").innerText = code.data; showView("view-send"); return; }

}

requestAnimationFrame(tick);

};

requestAnimationFrame(tick);

});

}

function stopScan() { if(videoStream){ videoStream.getTracks().forEach(t => t.stop()); videoStream = null; } showView("view-dash"); }

function executeSend() {

const amt = document.getElementById("send-amt").value;

const tid = document.getElementById("target-id-disp").innerText;

if(!amt || amt <= 0) return;

google.script.run.withSuccessHandler(res => {

if(res.success) { alert("Completed"); handleLogout(); }

else { document.getElementById("msg-send").innerText = res.message; }

}).sendPoints(myId, tid, amt);

}

</script>

</body>

</html>

テスト生成装置

シート

FormID：ID/フォームID/タイトル

Type1：得点/質問/正解/選択肢2/選択肢3/選択肢4

Type2：得点/質問/正解/選択肢2/選択肢3/選択肢4

Type3：得点/タイトル/本文/質問/正解/選択肢2/選択肢3/選択肢4

csv.

/**

* スプレッドシートの編集時に実行する関数

* トリガー設定でこの関数を指定してください

*/

function processNewInput(e) {

if (!e || !e.range) return;

const range = e.range;

const sheet = range.getSheet();

const value = e.value;

// FormIDシートのA列に "new" と入った場合のみ処理

if (sheet.getName() === "FormID" && range.getColumn() === 1 && String(value).toLowerCase().trim() === "new") {

range.setValue("Processing...");

SpreadsheetApp.flush();

try {

executeSingleFormCreation(sheet, range.getRow());

} catch (err) {

range.setValue("Error: " + err.message);

console.error(err);

}

/**

* フォーム作成メインロジック

*/

function executeSingleFormCreation(formIdSheet, row) {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const destSsId = '（ここにIDを入力カッコは消去）';

//上にID

const destSs = SpreadsheetApp.openById(destSsId);

const type1Data = getDataFromSheet(ss.getSheetByName('Type1'));

const type2Data = getDataFromSheet(ss.getSheetByName('Type2'));

const type3Data = getDataFromSheet(ss.getSheetByName('Type3'));

const quizNumber = row - 1;

const title = "Quiz " + quizNumber;

// 1. フォーム作成とクイズ設定（標準設定のみ）

const form = FormApp.create(title);

form.setIsQuiz(true);

form.setProgressBar(true);

form.setShowLinkToRespondAgain(false);

// 2. 回答先連携

form.setDestination(FormApp.DestinationType.SPREADSHEET, destSsId);

// 3. 連携シートのリネームと移動

SpreadsheetApp.flush();

Utilities.sleep(4000);

let sheets = destSs.getSheets();

for (let s of sheets) {

if (s.getName().indexOf("フォームの回答") !== -1) {

s.setName(title);

// 一番右端（末尾）に移動

destSs.setActiveSheet(s);

destSs.moveActiveSheet(destSs.getSheets().length);

break;

}

// 4. QuizMasterへの記録

let masterSheet = destSs.getSheetByName('QuizMaster') || destSs.insertSheet('QuizMaster');

if (masterSheet.getLastRow() === 0) {

masterSheet.appendRow(['シート名', 'フォーム ID', '種類']);

}

masterSheet.appendRow([title, form.getId(), '小テスト']);

// 5. 質問の追加

form.addTextItem().setTitle("ID").setRequired(true);

addQuestions(form, type1Data, 5, "Section 1: Vocabulary & Grammar");

addQuestions(form, type2Data, 2);

addType3Questions(form, type3Data, 1, "Section 2: Reading Comprehension");

// 6. 元の管理シート（FormID）へ情報を書き戻す

formIdSheet.getRange(row, 1).setValue(quizNumber);

formIdSheet.getRange(row, 2).setValue(form.getId());

formIdSheet.getRange(row, 3).setValue(title);

}

// --- 補助関数 ---

function getDataFromSheet(sheet) {

if (!sheet) return [];

const data = sheet.getDataRange().getValues();

if (data.length <= 1) return [];

data.shift();

return data;

}

function addQuestions(form, dataPool, count, sectionTitle) {

if (dataPool.length === 0) return;

if (sectionTitle) form.addPageBreakItem().setTitle(sectionTitle);

const selected = [...dataPool].sort(() => 0.5 - Math.random()).slice(0, count);

selected.forEach(row => {

const item = form.addMultipleChoiceItem();

item.setTitle(row[1]).setPoints(row[0] || 0)

.setChoices(shuffleChoices(item, row[2], row[3], row[4], row[5]));

});

}

function addType3Questions(form, dataPool, count, sectionTitle) {

if (dataPool.length === 0) return;

if (sectionTitle) form.addPageBreakItem().setTitle(sectionTitle);

const selected = [...dataPool].sort(() => 0.5 - Math.random()).slice(0, count);

selected.forEach(row => {

form.addSectionHeaderItem().setTitle(row[1]).setHelpText(row[2]);

const item = form.addMultipleChoiceItem();

item.setTitle(row[3]).setPoints(row[0] || 0)

.setChoices(shuffleChoices(item, row[4], row[5], row[6], row[7]));

});

}

function shuffleChoices(item, correct, ...wrongs) {

let choiceMap = new Map();

if (correct !== "" && correct !== undefined) {

choiceMap.set(String(correct), item.createChoice(String(correct), true));

}

wrongs.forEach(w => {

let val = String(w);

if (val !== "" && val !== "undefined" && !choiceMap.has(val)) {

choiceMap.set(val, item.createChoice(val, false));

}

});

return Array.from(choiceMap.values()).sort(() => 0.5 - Math.random());

}

Others

NazoWhale Models

Deformed Paper models