validation - 在 doPost 中设置数据验证直到发布后才应用于数据

Question

我有一个谷歌表，我使用 doPost 函数从集合表单中导入数据。我获得了每条记录的多个副本，因此我在脚本中添加了一个数据验证规则，该规则要求导入的记录 ID 尚未在电子表格中。该规则无法消除书面重复的帖子。它不会在发布时应用于记录。相反，当在其上方插入新记录时，它会在记录中设置。如何解决此问题以在发布数据之前应用规则来验证数据？

这是我的脚本...

//https://script.google.com/macros/s/AKfycbz6LGmlBb0IXW2TMBL1CZNHvLzGDSCywDH8gwmmX_e3nd2bXdU/exec

function doPost(e) 

{var sheet = SpreadsheetApp.openById('1dLC9b3-LhGeS0QznOGw-cy0w1TrGBhCyY1e74raTZqQ');
var target = sheet.getSheetByName('iForm Data').getRange('B2');
var rule = SpreadsheetApp.newDataValidation().requireFormulaSatisfied('=ISERROR(MATCH({B2},{B3:B},0))').setAllowInvalid(false).build();
target.setDataValidation(rule);

{if (typeof e !=="undefined")

{var iFormPostTable = SpreadsheetApp.openById("1dLC9b3-LhGeS0QznOGw-cy0w1TrGBhCyY1e74raTZqQ");
var dSheet = iFormPostTable.getSheetByName("iForm Data");                
var pSheet = iFormPostTable.getSheetByName("XML Post");                 
var tSheet = iFormPostTable.getSheetByName("Web Service Transactions");  

var key = "audits";

var postResult = e.parameter

var keyResult = postResult.audits;

pSheet.getRange("A2").setValue(postResult);
pSheet.getRange("B2").setValue(keyResult);

if (keyResult.length > 0)
{var xmlDoc = Xml.parse(keyResult, true);

function _getElement(xmlTag, xmlDoc)
{

 var xmlRecords = xmlDoc.getElement().getElements('record');

try {
return(xmlRecords[0].getElement(xmlTag).getText());
} catch (e) {
return(e.message);
}
}

dSheet.insertRowAfter(1);
dSheet.getRange("A2").setValue(Date());
dSheet.getRange("B2").setValue(_getElement("ID", xmlDoc));
dSheet.getRange("C2").setValue(_getElement("audit_date", xmlDoc));
dSheet.getRange("D2").setValue(_getElement("route", xmlDoc));
dSheet.getRange("E2").setValue(_getElement("stop", xmlDoc));
dSheet.getRange("F2").setValue(_getElement("store_number", xmlDoc));
dSheet.getRange("G2").setValue(_getElement("concept", xmlDoc));
dSheet.getRange("H2").setValue(_getElement("city", xmlDoc));
dSheet.getRange("I2").setValue(_getElement("state1", xmlDoc));
dSheet.getRange("J2").setValue(_getElement("auditor", xmlDoc));
dSheet.getRange("K2").setValue(_getElement("selector", xmlDoc));
dSheet.getRange("L2").setValue(_getElement("pallet_initial", xmlDoc));
dSheet.getRange("M2").setValue(_getElement("selection_zone", xmlDoc));
dSheet.getRange("N2").setValue(_getElement("steak_date", xmlDoc));
dSheet.getRange("O2").setValue(_getElement("tomato_date", xmlDoc));
dSheet.getRange("P2").setValue(_getElement("lettuce_date", xmlDoc));
dSheet.getRange("Q2").setValue(_getElement("cilantro_date", xmlDoc));
dSheet.getRange("R2").setValue(_getElement("chicken_date", xmlDoc));
dSheet.getRange("S2").setValue(_getElement("errors", xmlDoc));
dSheet.getRange("T2").setValue(_getElement("shorts", xmlDoc));
dSheet.getRange("U2").setValue(_getElement("list_shorts", xmlDoc));
dSheet.getRange("V2").setValue(_getElement("overages", xmlDoc));
dSheet.getRange("W2").setValue(_getElement("list_overages", xmlDoc));
dSheet.getRange("X2").setValue(_getElement("mispicks", xmlDoc));
dSheet.getRange("Y2").setValue(_getElement("list_mispicks", xmlDoc));
dSheet.getRange("Z2").setValue(_getElement("corrected", xmlDoc));
dSheet.getRange("AA2").setValue(_getElement("out_of_stock", xmlDoc));
dSheet.getRange("AB2").setValue(_getElement("list_outs", xmlDoc));
dSheet.getRange("AC2").setValue(_getElement("pallet_rework", xmlDoc));
dSheet.getRange("AD2").setValue(_getElement("reasons_for_rebuild", xmlDoc));
dSheet.getRange("AE2").setValue(_getElement("rebuild_number", xmlDoc));
dSheet.getRange("AF2").setValue(_getElement("total_number", xmlDoc));
dSheet.getRange("AG2").setValue(_getElement("additional_info", xmlDoc));

tSheet.insertRowAfter(2);
tSheet.getRange("A3").setValue(Date());
tSheet.getRange("B3").setValue(key);
tSheet.getRange("C3").setValue("Data Posted");

 var response = ContentService.createTextOutput();
response.setContent("Post received!");
return(response);
} else {

tSheet.insertRowAfter(2);
tSheet.getRange("A3").setValue(Date());
tSheet.getRange("B3").setValue(key);
tSheet.getRange("C3").setValue("Invalid Key Attempt");
}
}  
}
}

标签： validationgoogle-apps-scriptgoogle-sheets