1. 问题背景与挑战
在构建基于google sheets作为数据存储后端的web应用时，常见的一种数据提交方式是通过url参数。例如，用户访问 https://example.com/page?name=john&email=john@gmail.com 时，页面加载后会将 name 和 email 等参数发送到google apps script（gas），进而写入google sheet。
然而，这种机制面临一个挑战：如果用户重复访问该URL（无论是刷新页面、在新标签页打开，还是在不同设备上访问），即使前端JavaScript可能通过 performance.navigation.type 等方式阻止了页面刷新时的重复提交，也无法阻止用户从新入口再次提交相同数据。这会导致Google Sheet中出现大量重复条目，影响数据质量和后续分析。因此，我们需要一种在服务器端（即Google Apps Script）进行重复数据校验的机制。
2. 现有数据提交机制概述
为了更好地理解解决方案，我们首先回顾一下现有前端JavaScript和后端Google Apps Script如何协同工作。
2.1 前端JavaScript（数据捕获与发送）
前端JavaScript负责从当前URL中提取查询参数，并将这些参数通过 fetch API发送到部署为Web应用的Google Apps Script。
if (performance.navigation.type != performance.navigation.TYPE_RELOAD) {
// 从URL中获取查询参数
const queryParameters = Object.fromEntries(new URLSearchParams(window.location.search));
// Google Apps Script Web应用部署的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请求到Google Apps Script
fetch(scriptURL, { method: 'POST', body: data })
.then(response => console.log('Success!', response))
.catch(error => console.error('Error!', error.message))
}这段代码确保只有在非页面刷新的情况下才执行数据发送，但如前所述，它无法阻止跨会话或跨设备的重复提交。
2.2 后端Google Apps Script（数据接收与写入）
Google Apps Script作为Web应用的 doPost 函数，接收前端发送的数据，并将其写入指定的Google Sheet。
var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
function intialSetup () {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
const { Name, Email, sample } = e.parameter; // 提取URL参数
// 省略了处理"delete"逻辑的部分，此处主要关注数据添加
if (sample == "delete") {
// ... 删除逻辑 ...
}
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]
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]) // 将新行数据写入Sheet
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock() // 释放脚本锁
}
}上述 doPost 函数在获取参数后，会直接构造新行并追加到Google Sheet中，并未进行任何重复性检查。
3. 核心解决方案：在Google Apps Script中实现重复数据校验
为了解决重复数据问题，我们需要在 doPost 函数的数据写入逻辑之前，引入一个校验步骤。这个步骤将检查即将写入的数据（例如 Name 和 Email）是否已存在于Google Sheet中。
3.1 修改后的Google Apps Script代码
我们将在 try 块内部，实际写入数据之前，添加重复性检查逻辑。
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; // 提取URL参数
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 existingValues = sheet.getDataRange().getValues(); // 获取Sheet中所有数据
// 过滤出与当前提交的Name和Email都匹配的行
var matchedRows = existingValues.filter(row => [Name, Email].every(param => row.includes(param)));
if (matchedRows.length > 0) {
// 如果找到匹配的行，说明数据已存在，不进行追加
return ContentService.createTextOutput("Values were not appended. Data already exists.");
}
// --- 新增的重复数据校验逻辑结束 ---
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 (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}3.2 代码详解
-
var existingValues = sheet.getDataRange().getValues();:
- sheet.getDataRange() 获取Sheet中包含数据的所有单元格范围。
- .getValues() 将该范围内的数据作为一个二维数组返回。例如，如果Sheet有两列（Name, Email）和两行数据，它可能返回 [['Name', 'Email'], ['John', 'john@gmail.com']]。
-
var matchedRows = existingValues.filter(row => [Name, Email].every(param => row.includes(param)));:
- filter() 是JavaScript数组的一个方法，它会遍历 existingValues 数组中的每一行（row），并根据回调函数的返回值（true 或 false）来决定是否保留该行。
- [Name, Email].every(param => row.includes(param)) 是过滤条件的核心：
- [Name, Email] 是一个包含当前提交的 Name 和 Email 参数的数组。
- every() 也是JavaScript数组的方法，它会检查数组中的所有元素是否都满足提供的测试函数。在这里，它检查 Name 和 Email 这两个参数是否都存在于当前遍历的 row（即Sheet中的某一行数据）中。
- row.includes(param) 检查当前Sheet行（row）是否包含 param（即 Name 或 Email）。
- 综合起来，matchedRows 将包含所有在 Name 和 Email 两列中都与当前提交值匹配的行。
-
if (matchedRows.length > 0) { ... }:
- 如果 matchedRows 数组的长度大于0，说明在Sheet中找到了至少一行与当前提交的 Name 和 Email 完全匹配的数据。
- 在这种情况下，脚本将返回 ContentService.createTextOutput("Values were not appended. Data already exists.");，表示数据已存在，不执行追加操作。
通过以上修改，Google Apps Script在接收到数据后，会首先执行服务器端的重复性检查。只有当提交的数据在Sheet中不存在时，才会继续执行数据追加操作。
4. 重要注意事项
-
Web应用版本部署：
- 关键步骤！ 每次修改 Google Apps Script 代码（尤其是 Web 应用的 doPost 函数）后，必须将其部署为新版本，否则您的更改不会生效。
- 在 Apps Script 编辑器中，点击“部署” -> “新建部署”，选择类型为“Web 应用”，并确保“版本”选择“新建”。
-
唯一性判断标准：
- 在示例中，我们使用 Name 和 Email 两个参数来判断数据的唯一性。在实际应用中，您需要根据业务逻辑选择一个或多个参数作为唯一标识符。例如，如果 Email 本身就是唯一的，则可以只检查 Email。
- 确保所选的唯一性参数在Sheet中具有实际的唯一性。
-
并发处理与锁机制：
- 原始代码中使用了 LockService.getScriptLock() 和 lock.tryLock(10000) 来处理并发请求。这是一个良好的实践，可以防止多个用户同时提交数据时可能导致的数据冲突或乱序问题。在进行重复性检查时，锁机制依然重要，它确保在检查和写入过程中，Sheet的数据状态是稳定的。
-
响应与用户体验：
- 当检测到重复数据时，脚本返回了 ContentService.createTextOutput("Values were not appended. Data already exists.");。前端JavaScript可以通过检查 fetch 请求的响应来告知用户数据是否成功提交或因重复而被阻止。这有助于提升用户体验。
5. 总结
通过在Google Apps Script的 doPost 函数中引入服务器端重复数据校验机制，我们能够有效解决Web应用中因URL参数导致的重复数据提交问题。这种方法不仅弥补了客户端校验的不足，确保了Google Sheet中数据的准确性和唯一性，也为构建更健壮、更可靠的数据收集系统提供了基础。务必记住，每次对Apps Script Web应用代码进行修改后，都需要部署新版本才能使更改生效。
