确保执行appendRow并从最后一行返回数据，同时处理与google apps脚本的并发性

Question

我有一个html表单，它接受数据(学生观察)并将该数据保存到电子表格中，这是它应该做的功能生成idB.将包含id的行附加到电子表格(学生观察)C.通过检索保存在工作表上的最后一个id来检查观察是否已保存(添加了行)D.如果生成的id与最后一行检索到的id匹配，则返回保存的id，否则返回0

问题:

代码在大多数情况下工作，问题是有时用户得到0(而不是它应该返回的id)，我不确定为什么如果追加操作似乎没有失败。

我代码:

//save observacion submitted
function saveObservation(studentICID, studentOSISICID, studentName, studentClass, dateObserv, catObsev, textObserv, textCommit){
//return variable initialized
var success = 0;
//lock the app to deal with concurrency for 3 seconds
var lock = LockService.getPublicLock();
lock.waitLock(3000);  // wait 3 seconds before conceding defeat.
try{
// Log the email address of the person running the script.
var submittedByEmail = Session.getActiveUser().getEmail().slice(0, Session.getActiveUser().getEmail().indexOf("@"));
var now = new Date();
//get current date/time
var dateTime = Utilities.formatDate(now, "GMT-5", "yyyy-MM-dd' 'HH:mm:ss");
//get the sheet to log observations
var sODA = ssODA.getSheetByName("Observaciones");
var rowsBeforeObservation = sODA.getLastRow();
//get observation id
var observID = getObservationID(studentICID, studentOSISICID);
//log observation 
sODA.appendRow([observID, dateTime,submittedByEmail,studentICID,studentOSISICID,studentName,studentClass,dateObserv,catObsev,textObserv,textCommit]);
//log observation to Logger for backup
Logger.log([
observID, 
dateTime,
submittedByEmail,
studentICID,
studentOSISICID,
studentName,
studentClass,
dateObserv,
catObsev,
textObserv,
textCommit
]);
//update spreadsheet
SpreadsheetApp.flush();
//check if observation was logged
var lastobservIDOnSheet = sODA.getRange(sODA.getLastRow(),1,1,1).getValue();
if(lastobservIDOnSheet == observID){
success = observID;
}
Logger.log('success: ' + success);
return success;
}catch(e){
//Logger.log("error in saveObservation: " + e);
var eventcode = "saveObsFailed";
var event = "failure";
log_event_text(eventcode,event,e)
}finally { //release lock
lock.releaseLock();
}
}

我已经试过了:

1. 起初我认为我必须锁定保存操作，因为我认为这是并发性的问题，所以我添加了锁定部分

2. 然后一位老师正在保存一个学生的观察结果，并报告说它得到的是0而不是观察id，所以我添加了电子表格冲洗，如这个类似的问题

   所示

但是，最近一位老师报告得到0，日志证实了这一点(他是唯一一个使用HTML表单的人)。我不确定这个来自哪里，因为我甚至测试了它自己和其他用户作为工作之前，也只有一个人保存，所以在这一点上，我不确定我还应该尝试什么，或者我的锁/flush实现是错误的?

提前谢谢你。

Answer 1

尝试在写入和读取电子表格之间使用SpreadsheetApp.flush()。

var lastobservIDOnSheet = soda . gerange (sODA.getLastRow()，1).getValue();

问题可能是有时你没有收到activeUsers的电子邮件

试试这样:

function saveObservation(studentICID, studentOSISICID, studentName, studentClass, dateObserv, catObsev, textObserv, textCommit) {
var success = 0;
var lock = LockService.getPublicLock();
lock.waitLock(3000);  // wait 3 seconds before conceding defeat.
try {
var submittedByEmail = Session.getActiveUser().getEmail().slice(0, Session.getActiveUser().getEmail().indexOf("@"));
if (submittedByEmail) {
var now = new Date();
var dateTime = Utilities.formatDate(now, "GMT-5", "yyyy-MM-dd' 'HH:mm:ss");
var sODA = ssODA.getSheetByName("Observaciones");
var rowsBeforeObservation = sODA.getLastRow();
var observID = getObservationID(studentICID, studentOSISICID);
sODA.appendRow([observID, dateTime, submittedByEmail, studentICID, studentOSISICID, studentName, studentClass, dateObserv, catObsev, textObserv, textCommit]);
} else {
throw "Error: Getting Active User Email";
return 0;
}
SpreadsheetApp.flush();
var lastobservIDOnSheet = sODA.getRange(sODA.getLastRow(), 1).getValue();
if (lastobservIDOnSheet == observID) {
success = observID;
}
throw "Success"
return success;
} catch (e) {
throw e;
var eventcode = "saveObsFailed";
var event = "failure";
log_event_text(eventcode, event, e)
} finally { //release lock
lock.releaseLock();
}
}