我對 Google 的 Apps Script 的經驗有限，但直到現在我還是設法將功能代碼拼湊在一起。最終，我要做的是創建幾個 Apps Script 連接按鈕：“新建”、“保存”、“下載”和“關閉”。到目前為止，對我來說最困難的是“關閉”按鈕。當用戶點擊“關閉”時，系統將首先提示用戶確認他們希望“關閉”資料表單。單擊“是”后，Apps 腳本應檢查 CAR 日志中是否存在匹配的 CAR 編號（CAR 編號是唯一的），如果找到，則通過對話框提示用戶確認匹配的“CAR 日志”中的資料行應被“輸入表”中的資料覆寫。如果用戶點擊“YES”，
帶有輸入表格和 CAR 日志的示例 Google 表格：
這是我到目前為止撰寫的代碼：
function FormClose() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var uiConfirmClose = SpreadsheetApp.getUi();
var responseConfirmClose = uiConfirmClose.alert('Close & Clear all Form Data', 'Are you sure?', uiConfirmClose.ButtonSet.YES_NO);
var destination = ss.getSheetByName('CAR Log');
var CurrentCARNo = ss.getRange("D5").getValue();
var UsedCARNosRange = SpreadsheetApp.openById("MYSPREADSHEETID").getSheetByName("CAR Log").getRange("A:A");
var UsedCARNos = UsedCARNosRange.getValues();
var uiOverwrite = SpreadsheetApp.getUi();
var responseOverwrite = uiOverwrite.alert('CAR No. ' CurrentCARNo ' Already Exists in Log', 'Would you like to overwrite the log with the data in this form?', uiOverwrite.ButtonSet.YES_NO);
// Confirm Close: YES
if (responseConfirmClose == uiConfirmClose.Button.YES) {
Logger.log('The user clicked "Yes."');
// Check for Existing CAR No.
for (var i in UsedCARNos){
// FOUND
if (UsedCARNos[i][0].match(CurrentCARNo)!=null){
// Overwrite?
if (responseOverwrite == uiOverwrite.Button.YES) {
Logger.log('The user clicked "Yes."');
ss.getRange('D5:E5').copyTo(destination.getRange(destination.getLastRow() 1,1,1,1),SpreadsheetApp.CopyPasteType.PASTE_VALUES,true);
}
else {
Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
}
}
// NOT FOUND
else {
Logger.log('Data not found. Copy data to log.');
ss.getRange('D5:E5').copyTo(destination.getRange(destination.getLastRow() 1,1,1,1),SpreadsheetApp.CopyPasteType.PASTE_VALUES,true);
}
}
}
// Confirm Close: NO
if (responseConfirmClose == uiConfirmClose.Button.NO) {
Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
return;
};
}
我將非常感謝任何方向。
uj5u.com熱心網友回復：
如果我清楚地理解您的問題，您的主要目標是在您的第一個對話框提示 ( ) 上單擊No按鈕時停止運行您的腳本responseConfirmClose：
發現
- 在您的腳本上，
responseOverwrite在第一個對話框responseConfirmClose提示之后立即運行，即使在單擊No之后，因為它正在被初始化并在代碼的開頭運行。
建議
- 根據您的邏輯，我建議將您的
responseOverwrite變數放在您的第一個if條件陳述句中（如果 在對話框中選擇了“是”responseConfirmClose按鈕），如下面的調整腳本所示：
[更新]
調整腳本
function runClose() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var uiConfirmClose = SpreadsheetApp.getUi();
var destination = ss.getSheetByName('CAR Log');
var carLogData = destination.getDataRange().getDisplayValues();
var uiOverwrite = SpreadsheetApp.getUi();
var entryFormData = ss.getSheetByName('Entry Form').getDataRange().getDisplayValues();
var responseConfirmClose = uiConfirmClose.alert('Close & Clear all Form Data', 'Are you sure?', uiConfirmClose.ButtonSet.YES_NO);
if (responseConfirmClose == uiConfirmClose.Button.YES) {
Logger.log('The user clicked "Yes."');
for(var i in carLogData){
var row = parseInt(i) 1;
if(carLogData[i][0] == entryFormData[4][3]){
Logger.log("Found a match for \"" "CAR NO. " entryFormData[4][3] "\" in row #" row " on the \"CAR Log\" sheet");
var responseOverwrite = uiOverwrite.alert('CAR No. ' entryFormData[4][3] ' Already Exists in Log', 'Would you like to overwrite the log with the data in this form?', uiOverwrite.ButtonSet.YES_NO);
// Overwrite?
if (responseOverwrite == uiOverwrite.Button.YES) {
Logger.log('The user clicked "Yes."');
var copyData = [[entryFormData[4][3], //CAR No
entryFormData[8][3], //Date Opened
entryFormData[8][9], //Originator
entryFormData[8][15], //NCP # (if applicable)
entryFormData[10][2], //Action Type
entryFormData[10][10], //Source
entryFormData[12][1], //Issue to
entryFormData[12][10], //QMS Reference & Clause
entryFormData[17][0], //Description of Nonconformance / Opportunity for Improvement
entryFormData[19][0], //Root Cause
entryFormData[21][0], //Corrective Action
entryFormData[23][5], //Corrective Action Discussed with and Agreed Upon b
entryFormData[23][15], //Corrective Action Planned Completion Date
entryFormData[28][3], //Approved by
entryFormData[28][11], //Date Approve
entryFormData[33][3], //Followed up by
entryFormData[33][11], //Date of Follow up
entryFormData[35][5], //Corrective Action Effective?
entryFormData[38][0], //Evidence
entryFormData[40][3], //Close Out by
entryFormData[40][11]]]; //Date Closed
destination.getRange("A" row ":U" row).setValues(copyData);
}
else {
Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
}
}
}
}else{
Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
}
//Code here to empty the fields on "Entry Form" e.q.:
ss.getRange("D9:E9").clearContent();
ss.getRange("J9:K9").clearContent();
ss.getRange("P9:Q9").clearContent();
ss.getRange("C11:I11").clearContent();
ss.getRange("K11:O11").clearContent();
ss.getRange("B13:F13").clearContent();
ss.getRange("K13:P14").clearContent();
//continue here for the rest of the fields
}
示例演示：
Entry Form資料樣本
CAR Log資料樣本
- After clicking YES on the dialog prompt, since there's already a matching
CAR Noon row #4 on theCAR Log, it will then be overridden with new data:
Entry Formwill be cleared after that Or it will be cleared when No is clicked from the prompt:
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/435846.html