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函数中需要修改的关键部分。我们将修改数据写入逻辑之前的部分。
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。
7. 总结
通过在Google Apps Script Web App的doPost函数中引入数据唯一性检查机制，我们可以有效地防止重复数据被写入Google Sheets。这不仅提高了数据质量，也优化了数据管理流程。请务必在每次代码修改后，部署新的Web App版本以确保更改生效。
