当两个表单同时提交时，表单提交脚本中出错

Question

我有一个谷歌表格脚本，当表单提交时，它会处理一些事情，然后创建一个新表格，最后作为电子邮件的附件发送表格。 它工作正确，但是当两个表单同时提交时(当第二个表单在触发器完成之前提交时(，电子邮件未发送，根本不起作用

这是我的代码：

function onSubmit(e){
Logger.log('submit ran');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var calculate = ss.getSheets()[2];
var Responses = ss.getSheets()[0];
var report = ss.getSheets()[1];
var report1= ss.getSheets()[3];

var lastRow = Responses.getLastRow();
var copyrange = SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
SpreadsheetApp.flush();
var maxRow1 = report1.getMaxRows();
var lastRow1 = report1.getLastRow();
SpreadsheetApp.flush();
report1.deleteRows(9+count, (172-(9+count)))
SpreadsheetApp.flush();

var email = sB;
var subject = "راید ریپورت "+sa;
var body = se;
var calculate = ss.getSheets()[2];
var Responses = ss.getSheets()[0];
var report = ss.getSheets()[1];

var newSheetid = SpreadsheetApp.create("promoter report "+si,30,60).getId();
var newSheet= DriveApp.getFileById(newSheetid);
DriveApp.getFolderById("1glwG1---------------tW").addFile(newSheet)
newSheet.getParents().next().removeFile(newSheet); 
var sheet = source.getSheets()[3];
var destination = SpreadsheetApp.openById(newSheetid);
sheet.copyTo(destination);

var source = SpreadsheetApp.getActiveSpreadsheet();
var  sheet1= destination.getSheetByName("Sheet1").hideSheet();
var sheet1id=sheet1.getSheetId();
SpreadsheetApp.flush();
var pdf = newSheet.getAs('application/pdf')

MailApp.sendEmail(email,subject ,body, {attachments:[pdf]});
var sf = Responses.getRange(lastRow, 6).setValue("EMAIL_SENT")
SpreadsheetApp.flush();
}

Answer 1

正如您正确注意到的那样，您的代码可能会与模拟表单提交发生冲突

为什么？

因为您执行了对电子表格服务的多次调用，这会显著降低代码速度，如最佳实践中所述。

怎么办？

• 除了根据应用程序脚本最佳实践优化代码外，您还可以使用 LockService
• Lockservice 允许您确保在给定时间只运行脚本的一个实例，并且后续调用"在队列中等待"，直到第一个脚本执行完成

如何使用锁定服务？

基于代码的示例：

function onSubmit(e){
var lock = LockService.getScriptLock();
// Wait for up to 30 seconds for other processes to finish.
lock.waitLock(30000);
Logger.log('submit ran');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var calculate = ss.getSheets()[2];
var Responses = ss.getSheets()[0];
var report = ss.getSheets()[1];
var report1= ss.getSheets()[3];

var lastRow = Responses.getLastRow();
var copyrange = Responses.getRange("e2:m2").copyTo(Responses.getRange(lastRow, 5), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
SpreadsheetApp.flush();

var sh = Responses.getRange(lastRow, 8).getValue();

var cell = calculate.getRange("c2");
cell.setFormula(sh);
SpreadsheetApp.flush();
SpreadsheetApp.flush();
var lastRow = Responses.getLastRow();
var copyrange = Responses.getRange(lastRow, 5,1, 10).copyTo(Responses.getRange(lastRow, 5),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
SpreadsheetApp.flush();
var sa = Responses.getRange(lastRow, 1).getValue();
var sB = Responses.getRange(lastRow, 2).getValue();
var se = Responses.getRange(lastRow, 5).getValue();
var sf = Responses.getRange(lastRow, 6).getValue();
var si = Responses.getRange(lastRow, 9).getValue();
SpreadsheetApp.flush();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var rangclear= report1.getRange("a9:z100");
rangclear.deleteCells(SpreadsheetApp.Dimension.ROWS);
var count= calculate.getRange("b6").getValue();
report.getRange("A:Z").copyTo(report1.getRange("A1"));
SpreadsheetApp.flush();
report.getRange("A:Z").copyTo(report1.getRange("A1"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
SpreadsheetApp.flush();
var maxRow1 = report1.getMaxRows();
var lastRow1 = report1.getLastRow();
SpreadsheetApp.flush();
report1.deleteRows(9+count, (172-(9+count)))
SpreadsheetApp.flush();
var email = sB;
var subject = "راید ریپورت "+sa;
var body = se;
var calculate = ss.getSheets()[2];
var Responses = ss.getSheets()[0];
var report = ss.getSheets()[1];

var newSheetid = SpreadsheetApp.create("promoter report "+si,30,60).getId();
var newSheet= DriveApp.getFileById(newSheetid);
DriveApp.getFolderById("1glwG1WF2rgrg54hfh0nJjxgtW").addFile(newSheet)
newSheet.getParents().next().removeFile(newSheet);
var source = SpreadsheetApp.getActiveSpreadsheet();
var sheet = source.getSheets()[3];
var destination = SpreadsheetApp.openById(newSheetid);
sheet.copyTo(destination);

var source = SpreadsheetApp.getActiveSpreadsheet();
var  sheet1= destination.getSheetByName("Sheet1").hideSheet();
var sheet1id=sheet1.getSheetId();
SpreadsheetApp.flush();
var pdf = newSheet.getAs('application/pdf')

MailApp.sendEmail(email,subject ,body, {attachments:[pdf]});
var sf = Responses.getRange(lastRow, 6).setValue("EMAIL_SENT")
SpreadsheetApp.flush();
// Release the lock so that other processes can continue.
lock.releaseLock();
}

注意：此代码可以进一步优化以运行得更快