我的电子表格就像一个销售工具,在各种单元格中输入信息以运行敏感性分析。一旦销售人员选择了所需的选项;提交它";。然后将提交的数据存储在电子表格中的一张单独的表格上(称之为"数据库"(。
我希望数据库工作表存储所有用户提交的内容。问题是,当多个用户同时使用销售工具工作表时,他们输入的信息会被其他用户的输入覆盖,然后才能提交。
我希望多个用户能够使用";销售工具";纸张同时不相互覆盖,但仍然能够将数据提交给";数据库";床单
供参考,这是电子表格。这是应用程序脚本。
function submitData() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("User Feedback form"); //delcare a variable and set with the User Feedback form worksheet
var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
// close the dialog by clicking the close button in its title bar.
var response = ui.alert("Submit", 'Do you want to submit the data?',ui.ButtonSet.YES_NO);
// Checking the user response and proceed with clearing the form if user selects Yes
if (response == ui.Button.NO)
{return;//exit from this function
}
//Validating the entry. If validation is true then proceed with transferring the data to Database sheet
//if (validateEntry()==true) {
var blankRow=datasheet.getLastRow()+1; //identify the next blank row
datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("C4").getValue()); //POD Lead Name
datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("F4").getValue()); //Member Email
datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C6").getValue()); //FT Score
datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C8").getValue()); //BT Score
datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("C10").getValue()); //SI Score
datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("C12").getValue()); //CI Score
datasheet.getRange(blankRow, 7).setValue(shUserForm.getRange("C14").getValue());//ED Score
datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("F6").getValue()); //FT feedback
datasheet.getRange(blankRow, 9).setValue(shUserForm.getRange("F8").getValue()); //BT feedback
datasheet.getRange(blankRow, 10).setValue(shUserForm.getRange("F10").getValue()); //SI feedback
datasheet.getRange(blankRow, 11).setValue(shUserForm.getRange("F12").getValue()); //CI feedback
datasheet.getRange(blankRow, 12).setValue(shUserForm.getRange("F14").getValue());//ED feedback
// date function to update the current date and time as submittted on
datasheet.getRange(blankRow, 14).setValue(new Date()).setNumberFormat('yyyy-mm-dd'); //Submitted On
//get the email address of the person running the script and update as Submitted By
datasheet.getRange(blankRow, 13).setValue(Session.getActiveUser().getEmail()); //Submitted By
ui.alert(' "Feedback Saved' + shUserForm.getRange("F4").getValue() +' "');
//Clearnign the data from the Data Entry Form
shUserForm.getRange("C4").clear(); //POD Lead Name
shUserForm.getRange("F4").clear();//Member Email
shUserForm.getRange("C6").clear(); //FT Score
shUserForm.getRange("C8").clear(); //BT Score
shUserForm.getRange("C10").clear(); //SI Score
shUserForm.getRange("C12").clear(); //CI Score
shUserForm.getRange("C14").clear();//ED Score
shUserForm.getRange("F6").clear(); //FT feedback
shUserForm.getRange("F8").clear(); //BT feedback
shUserForm.getRange("F10").clear(); //SI feedback
shUserForm.getRange("F12").clear(); //CI feedback
shUserForm.getRange("F14").clear();//ED feedback
//}
}
在您的情况下,运行函数submitData()
时保护工作表如何?并且,当submitData()
的脚本完成时,保护被移除。当这反映在脚本中时,它将变为如下。
修改的脚本:
来源:
这是submitData((submitData()
的当前脚本。
function submitData() {
// This is your current script.
}
到
请按如下方式重命名函数名称。
function submitData_org() {
// This is your current script.
}
并且,请添加以下功能。
function submitData() {
const lock = LockService.getDocumentLock();
if (lock.tryLock(350000)) {
try {
const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(s => {
const p = s.protect();
p.removeEditors(p.getEditors());
p.addEditor(Session.getEffectiveUser());
})
submitData_org(); // Here, your script is called.
sheets.forEach(s => s.protect().remove());
} catch (e) {
throw new Error(e.message);
} finally {
lock.releaseLock();
}
} else {
throw new Error("timeout");
}
}
- 在这个修改中,当
submitData()
运行时,工作表受到保护,而当submitData_org()
的脚本完成时,保护将被删除。此流使用LockService运行
参考文献:
- 等级保护
- 锁定服务