Google Sheets物品借出系统自动化需求：数据格式化与邮件推送

一、数据自动格式化（生成Desired Output表格） #

通过Google Apps Script监听表单提交事件，自动提取并整理数据到目标工作表：

1. 打开目标Google表格，点击「扩展程序」>「Apps脚本」进入编辑器
2. 替换默认代码为以下脚本（根据实际工作表名称调整变量）：

function onFormSubmit(e) {
  // 定义工作表名称
  const rawSheetName = "Raw Data";
  const inventorySheetName = "Inventory List";
  const outputSheetName = "Desired Output";
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rawSheet = ss.getSheetByName(rawSheetName);
  const inventorySheet = ss.getSheetByName(inventorySheetName);
  const outputSheet = ss.getSheetByName(outputSheetName);
  
  // 获取表单提交的最新行数据
  const response = e.values;
  const userName = response[1]; // 假设姓名在Raw Data的第2列（索引从0开始）
  const borrowDate = new Date(response[0]); // 提交日期（第1列）
  const returnDate = new Date(borrowDate);
  returnDate.setMonth(returnDate.getMonth() + 1); // 计算归还日期（+1个月）
  
  // 获取Inventory List的物品数据，构建匹配映射
  const inventoryData = inventorySheet.getDataRange().getValues();
  const itemMap = {};
  inventoryData.forEach(row => {
    if (row[0]) { // 假设物品名称在Inventory List第1列
      itemMap[row[0]] = {
        fullName: row[1], // 物品全名在第2列
        serialNum: row[2] // 序列号在第3列
      };
    }
  });
  
  // 遍历Item1到Item10，处理非空物品
  for (let i = 2; i <= 11; i++) { // 假设Item1从Raw Data第3列开始（索引2）
    const itemName = response[i];
    if (itemName && itemMap[itemName]) {
      // 准备写入Desired Output的行数据
      const outputRow = [
        userName,
        itemMap[itemName].fullName,
        itemMap[itemName].serialNum,
        borrowDate,
        returnDate
      ];
      // 追加到目标工作表
      outputSheet.appendRow(outputRow);
    }
  }
  
  // 调用邮件发送函数
  sendBorrowNotification(userName, borrowDate, returnDate, response[12]); // 假设提交者邮箱在第13列
}

二、自动邮件推送 #

在同一个脚本中添加邮件发送函数，将借出记录发送到指定邮箱：

function sendBorrowNotification(userName, borrowDate, returnDate, submitterEmail) {
  // 预设邮箱
  const presetEmails = ["email1@example.com", "email2@example.com"];
  const allRecipients = [...presetEmails, submitterEmail].join(",");
  
  // 格式化日期
  const formattedBorrowDate = Utilities.formatDate(borrowDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
  const formattedReturnDate = Utilities.formatDate(returnDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
  
  // 获取最新的借出记录（Desired Output中对应用户的最新条目）
  const outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Desired Output");
  const lastRow = outputSheet.getLastRow();
  const recentItems = [];
  for (let i = lastRow; i > 0; i--) {
    const row = outputSheet.getRange(i, 1, 1, 5).getValues()[0];
    if (row[0] === userName && row[3].getTime() === borrowDate.getTime()) {
      recentItems.push(`- 物品全名：${row[1]}，序列号：${row[2]}`);
    } else {
      break; // 假设提交的记录是连续追加的，找到非目标记录即停止
    }
  }
  
  // 邮件内容
  const subject = `物品借出通知：${userName}已借出物品`;
  const body = `
  您好，以下是最新的物品借出记录：
  
  借出用户：${userName}
  借出日期：${formattedBorrowDate}
  预计归还日期：${formattedReturnDate}
  
  借出物品明细：
  ${recentItems.join("\n")}
  
  请留意归还时间。
  `;
  
  // 发送邮件
  MailApp.sendEmail({
    to: allRecipients,
    subject: subject,
    body: body
  });
}

三、设置自动触发（实现无人干预） #

1. 在Apps脚本编辑器中，点击左侧的「触发器」图标（闹钟样式）
2. 点击「添加触发器」，配置以下参数：
   • 选择要运行的函数：onFormSubmit
   • 选择部署类型：Head
   • 选择事件源：表单提交
   • 选择事件类型：来自表单的提交
3. 点击「保存」，按照提示完成脚本授权（需允许脚本访问表格和发送邮件）

注意事项 #

• 需根据实际表格列索引调整脚本中的列位置（比如姓名、邮箱、物品列的索引）
• 若Inventory List的物品名称存在重复，需调整映射逻辑确保匹配正确
• 日期处理中，setMonth会自动处理月末情况（如1月31日+1个月为2月28/29日）

内容的提问来源于stack exchange，提问作者tairann