防止重复数据写入Google Sheets：基于URL参数的Web应用优化

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))
}

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() // 释放脚本锁
  }
}

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()
  }
}