每次添加新行时，都试图阻止我的邮件合并函数从第2行运行

Question

正如标题所说：

每次添加新行时，程序应该只运行最近插入的行，因为我正在使用"；成功；字符串以阻止其运行已合并的行。

如何确保每次运行函数时，它只运行或合并新插入的行，而不是从第2行开始？尽管我在我的代码中添加了"；成功；字符串，以便它看到它并跳过该行。

以下是我的代码：

var Success = "Success";
function createBulkPDFs()
{
const pdfFolder = DriveApp.getFolderById("1_jdFncxgkyCuH181MpwyYf25gWrWHMO");
const docFile = DriveApp.getFileById("1XAn-1MqH6ER01dnbATLzG9gYWQpFDKfQhUwWHRRcyo");
const tempFolder = DriveApp.getFolderById("141fjXWkg0fU3q0u22WUPkJDl82JakTj");
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const data = currentSheet.getRange(2, 1, currentSheet.getLastRow() - 1, 32).getDisplayValues();
let errors = [];
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
for (var i = 2; i <= lr; i++)
{
var check = ss.getRange(i, 33).getValue();
if (check != Success)
{
data.forEach(row =>
{
try
{
createPDF(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9],
row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18],
row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[27],
row[28], row[29], row[30], row[31], row[3], docFile, tempFolder, pdfFolder);
errors.push(["Success"]);
}
catch (err)
{
errors.push(["Failed"]);
}
}); //close forEach
currentSheet.getRange(2, 33, currentSheet.getLastRow() - 1, 1).setValues(errors);
}
}
}
function createPDF(RepTime, Rep_Name, RepIC_No, Ven_Company, RepDs, Ven_Comp_Regis, Ven_Cont_no, Ven_Fax_no, Ven_Add,
PODquestionone, PODquestiontwo, PODquestionthree, PODquestionfour, PODquestionfive, PODquestionsix, RPOquestionone,
RPOquestiontwo, RPOquestionthree, RPOquestionfour, RPOquestionfive, RPOquestionsix, RPOquestionseven,
CIACquestionone, CIACquestiontwo, Prepquestionone, MACCquestionone, MACCquestiontwo, MACCquestionthree,
MACCquestionfour, WCquestionone, WCquestiontwo, WCquestionthree, pdfName, docFile, tempFolder, pdfFolder)
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
//for(var i = 2; i<=lr; i++){
var checks = ss.getRange(i, 33).getValue();
//var che = ss.getRange(i,1).getValue();
//if(checks != ""){
const tempFile = docFile.makeCopy(tempFolder);
const tempDocFile = DocumentApp.openById(tempFile.getId());
const body = tempDocFile.getBody();
body.replaceText("{RepTime}", RepTime);
body.replaceText("{RepFullName}", Rep_Name);
body.replaceText("{RepICNo}", RepIC_No);
body.replaceText("{VCompany}", Ven_Company);
body.replaceText("{RepDesig}", RepDs);
body.replaceText("{VCompanyRegis}", Ven_Comp_Regis);
body.replaceText("{VConNo}", Ven_Cont_no);
body.replaceText("{VFaxNo}", Ven_Fax_no);
body.replaceText("{VAddress}", Ven_Add);
body.replaceText("{PODQuestionone}", PODquestionone);
body.replaceText("{PODQuestiontwo}", PODquestiontwo);
body.replaceText("{PODQuestionthree}", PODquestionthree);
body.replaceText("{PODQuestionfour}", PODquestionfour);
body.replaceText("{PODQuestionfive}", PODquestionfive);
body.replaceText("{PODQuestionsix}", PODquestionsix);
body.replaceText("{RPOQuestionone}", RPOquestionone);
body.replaceText("{RPOQuestiontwo}", RPOquestiontwo);
body.replaceText("{RPOQuestionthree}", RPOquestionthree);
body.replaceText("{RPOQuestionfour}", RPOquestionfour);
body.replaceText("{RPOQuestionfive}", RPOquestionfive);
body.replaceText("{RPOQuestionsix}", RPOquestionsix);
body.replaceText("{RPOQuestionseven}", RPOquestionseven);
body.replaceText("{CIACQuestionone}", CIACquestionone);
body.replaceText("{CIACQuestiontwo}", CIACquestiontwo);
body.replaceText("{PrepQuestionone}", Prepquestionone);
body.replaceText("{MACCQuestionone}", MACCquestionone);
body.replaceText("{MACCQuestiontwo}", MACCquestiontwo);
body.replaceText("{MACCQuestionthree}", MACCquestionthree);
body.replaceText("{MACCQuestionfour}", MACCquestionfour);
body.replaceText("{WCQuestionone}", WCquestionone);
body.replaceText("{WCQuestiontwo}", WCquestiontwo);
body.replaceText("{WCQuestionthree}", WCquestionthree);
tempDocFile.saveAndClose();
const pdfContentBlob = tempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(pdfContentBlob).setName(pdfName);
tempFolder.removeFile(tempFile);
}

Answer 1

if (check != Success)返回false的一个很可能的解释是活动工作表ss与currentSheet不同。

您可以通过跳过activate()和getActiveSheet()请求来避免这种错误，而是始终按名称检索您感兴趣的表单。

现在，如果您使用表单提交触发器，您可以通过使用事件对象以更优雅的方式重组代码

• formSubmit事件对象允许您直接检索与最新表单响应相关的所有相关信息，即响应的值和将此表单响应插入工作表的范围
• 您不需要在整个工作表中循环查找正确的行

要在formSubmit触发器上运行的样本：

const pdfFolder = DriveApp.getFolderById("1_jdFncxgkyCuH181MpwyYf25gWrWHMO");
const docFile = DriveApp.getFileById("1XAn-1MqH6ER01dnbATLzG9gYWQpFDKfQhUwWHRRcyo");
const tempFolder = DriveApp.getFolderById("141fjXWkg0fU3q0u22WUPkJDl82JakTj");
function createBulkPDFs(event)
{ 
let error;
var row = event.values;
// have a look at the values:
Logger.log(row);
try {
createPDF(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[27],row[28], row[29], row[30], row[31], row[3], docFile, tempFolder, pdfFolder);
error = "Success";
} catch (err){
error="Failed";
}
var row = event.range.getRow();
var currentSheet = event.range.getSheet();
currentSheet.getRange(row, 33).setValue(error);
}
function createPDF(...){
...
}

将函数与事件对象一起使用时，请记住，它们只能由事件触发(表单提交(-如果您尝试手动运行函数，则会出错，因为不会定义事件对象。