防止Web表单重复数据提交到Google Sheets的教程-js教程-PHP中文网

防止Web表单重复数据提交到Google Sheets的教程

本教程详细介绍了如何通过修改Google Apps Script Web App，有效防止用户重复提交相同的表单数据到Google Sheets。通过在数据写入前进行现有记录检查，确保数据唯一性，并提供相应的代码实现和部署注意事项，提升数据管理的准确性和效率。

1. 问题背景与挑战

在开发基于google sheets作为后端数据库的web应用程序时，一个常见的问题是用户可能会重复提交相同的数据。例如，当用户通过url参数（如https://example.com/page?name=john&email=john@gmail.com）访问页面时，页面加载时会自动将这些参数发送到google sheets。如果用户再次访问该url，或者在新标签页、新设备上打开相同的url，系统可能会再次记录这些相同的参数，导致google sheets中出现重复数据。虽然可以通过javascript阻止页面重新加载时的重复提交，但这并不能解决用户从其他来源（如直接粘贴url、在新标签页打开）访问时的问题。

2. 现有数据提交机制概述

当前的数据提交流程通常包括两个主要部分：前端JavaScript和后端Google Apps Script。

前端JavaScript： 前端JavaScript负责从URL中提取查询参数，并将其作为POST请求的数据发送到Google Apps Script部署的Web App URL。

// 检查是否为页面重新加载，避免不必要的提交
if (performance.navigation.type !== performance.navigation.TYPE_RELOAD) {
  // 从URL中获取查询参数
  const queryParameters = Object.fromEntries(new URLSearchParams(window.location.search));

  // Google Apps Script Web App的部署URL
  const scriptURL = 'https://script.google.com/macros/s/script/exec';

  // 构建FormData对象
  const data = new FormData();
  Object.entries(queryParameters).forEach(e => data.append(...e));

  // 发送POST请求
  fetch(scriptURL, { method: 'POST', body: data })
    .then(response => console.log('Success!', response))
    .catch(error => console.error('Error!', error.message));
}

登录后复制

后端Google Apps Script (Web App doPost 函数)： Google Apps Script接收前端发送的POST请求，并处理数据。其主要任务是将接收到的参数写入指定的Google Sheet中。

var sheetName = 'Sheet1';
var scriptProp = PropertiesService.getScriptProperties();

// 初始化设置，通常用于存储Spreadsheet ID
function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  scriptProp.setProperty('key', activeSpreadsheet.getId());
}

// 处理POST请求
function doPost (e) {
  const { Name, Email, sample } = e.parameter; // 示例参数

  // 示例：处理删除请求的逻辑 (与本次去重无关，但保留原代码结构)
  if (sample === "delete") {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    var ranges = sheet.getDataRange();
    var values = ranges.getValues().filter(r => ![Name, Email].every(e => r.includes(e)));
    if (ranges.length === 0) {
      return ContentService.createTextOutput(`"${Email}" was not found.`);
    }
    ranges.clearContent().offset(0, 0, values.length, values[0].length).setValues(values);
    return ContentService.createTextOutput(`Rows including "${Email}" were deleted.`);
  }

  // 获取脚本锁，防止并发写入冲突
  var lock = LockService.getScriptLock();
  lock.tryLock(10000); // 尝试获取锁，超时10秒

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
    var sheet = doc.getSheetByName(sheetName);

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1;

    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header];
    });

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (err) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': err.message }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock(); // 释放锁
  }
}

登录后复制

3. 防止重复数据提交的解决方案

为了解决重复数据提交的问题，我们需要在Google Apps Script的doPost函数中添加一个检查机制：在数据写入Google Sheet之前，先检查是否存在具有相同关键参数（例如Name和Email）的记录。如果存在，则阻止写入并返回相应的提示。

修改思路： 在doPost函数中，获取到要写入的参数后，首先读取当前Sheet的所有数据。然后，使用filter()和every()方法来判断是否存在与当前提交的Name和Email完全匹配的行。如果找到了匹配项，则表示数据已存在，无需再次写入。

4. Google Apps Script代码修改

以下是doPost函数中需要修改的关键部分。我们将修改数据写入逻辑之前的部分。

Starry.ai

AI艺术绘画生成器

查看详情

function doPost (e) {
  const { Name, Email, sample } = e.parameter; // 获取提交的参数

  // ... (删除请求的逻辑保持不变，如果存在) ...

  var lock = LockService.getScriptLock();
  lock.tryLock(10000);

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
    var sheet = doc.getSheetByName(sheetName);

    // --- 新增的去重逻辑开始 ---
    // 获取Sheet中的所有数据
    var allSheetValues = sheet.getDataRange().getValues();

    // 检查是否存在与当前提交的Name和Email完全匹配的记录
    // 假设Name和Email在Sheet中的列顺序是固定的，或者可以通过headers动态查找
    // 为了简化，这里假设Name和Email是Sheet中的关键识别字段
    const existingMatches = allSheetValues.filter(row => {
      // 这里的row[0]和row[1]是示例，实际应根据Name和Email在Sheet中的列索引来确定
      // 例如，如果Name在第一列，Email在第二列，则使用row[0]和row[1]
      // 更健壮的方法是先获取表头，然后根据表头找到对应列的索引
      const nameColIndex = headers.indexOf('Name'); // 假设headers已获取
      const emailColIndex = headers.indexOf('Email'); // 假设headers已获取
      return row[nameColIndex] === Name && row[emailColIndex] === Email;
    });

    if (existingMatches.length > 0) {
      // 如果找到匹配的记录，则不进行数据追加，并返回提示
      return ContentService.createTextOutput(
        JSON.stringify({ 'result': 'warning', 'message': '数据已存在，未重复添加。' })
      ).setMimeType(ContentService.MimeType.JSON);
    }
    // --- 新增的去重逻辑结束 ---

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1;

    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header];
    });

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (err) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': err.message }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

登录后复制

关键修改点说明：

获取所有数据： var allSheetValues = sheet.getDataRange().getValues(); 用于获取Google Sheet中的所有现有数据。
数据过滤与检查： allSheetValues.filter(row => row[nameColIndex] === Name && row[emailColIndex] === Email); 这一行是核心。它遍历所有行，检查当前行的Name和Email列是否与提交的参数完全匹配。
- nameColIndex 和 emailColIndex 需要根据你的Sheet实际列顺序来确定。如果你的Sheet第一列是Name，第二列是Email，那么它们就是0和1。更健壮的做法是先获取表头，然后通过headers.indexOf('Name')等方式动态获取列索引。
判断与返回： if (existingMatches.length > 0) 判断是否存在匹配的记录。如果存在，则返回一个包含警告信息的JSON响应，而不是继续写入数据。

5. 完整修改后的Google Apps Script doPost 函数

var sheetName = 'Sheet1';
var scriptProp = PropertiesService.getScriptProperties();

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  scriptProp.setProperty('key', activeSpreadheet.getId());
}

function doPost (e) {
  const { Name, Email, sample } = e.parameter;

  if (sample === "delete") {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    var ranges = sheet.getDataRange();
    var values = ranges.getValues().filter(r => ![Name, Email].every(e => r.includes(e)));
    if (ranges.length === 0) {
      return ContentService.createTextOutput(`"${Email}" was not found.`);
    }
    ranges.clearContent().offset(0, 0, values.length, values[0].length).setValues(values);
    return ContentService.createTextOutput(`Rows including "${Email}" were deleted.`);
  }

  var lock = LockService.getScriptLock();
  lock.tryLock(10000);

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
    var sheet = doc.getSheetByName(sheetName);

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    // 动态获取Name和Email列的索引
    const nameColIndex = headers.indexOf('Name');
    const emailColIndex = headers.indexOf('Email');

    // 如果Name或Email列不存在，则可能需要调整逻辑或报错
    if (nameColIndex === -1 || emailColIndex === -1) {
        throw new Error("Sheet headers 'Name' or 'Email' not found.");
    }

    // 获取所有现有数据，从第二行开始（跳过表头）
    var allSheetValues = sheet.getDataRange().getValues();
    if (allSheetValues.length > 1) { // 确保有数据行可供检查
        const dataRows = allSheetValues.slice(1); // 跳过表头行

        const existingMatches = dataRows.filter(row => {
            return row[nameColIndex] === Name && row[emailColIndex] === Email;
        });

        if (existingMatches.length > 0) {
            return ContentService.createTextOutput(
                JSON.stringify({ 'result': 'warning', 'message': '数据已存在，未重复添加。' })
            ).setMimeType(ContentService.MimeType.JSON);
        }
    }

    var nextRow = sheet.getLastRow() + 1;

    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header];
    });

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (err) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': err.message }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

登录后复制

6. 注意事项与部署

Web App 部署新版本： 在修改Google Apps Script代码后，务必部署为新的Web App版本。否则，您的更改将不会在已部署的Web App中生效。
- 在Google Apps Script编辑器中，点击右上角的“部署” -> “新建部署”。
- 选择部署类型为“Web 应用”。
- 在“版本”下拉菜单中选择“新建版本”。
- 点击“部署”。
选择唯一性标识： 在去重逻辑中，选择哪些参数作为判断数据唯一性的依据至关重要。在本例中，我们使用了Name和Email。根据您的实际需求，可能需要选择其他参数或它们的组合。
错误处理与用户反馈： 当数据被识别为重复时，后端返回了JSON格式的警告信息。前端JavaScript可以根据这个响应来向用户提供相应的反馈（例如，显示“您已提交过此信息”）。
并发控制： LockService在原代码中已经存在，它用于防止多个并发请求同时写入Google Sheet，从而避免数据损坏或覆盖。这是良好的实践，建议保留。
性能考虑： 对于非常大的Google Sheet（例如，数万行数据），getDataRange().getValues()可能会消耗较多时间和资源。如果性能成为瓶颈，可以考虑其他优化策略，例如：
- 维护一个单独的哈希表或缓存来快速检查重复项。
- 使用Google Sheets API的查询功能（但Apps Script的filter通常效率足够）。
- 仅读取特定范围的列而不是整个Sheet。