Google Apps Script to Move Data from Form Entry to Log w/ UI Input Driven If Statements-CodePudding

I have limited experience with Google's Apps Script but have managed to piece together functional code, until now. Ultimately, what I'm trying to do is to create several Apps Script connected buttons: "New", "Save", "Download" and "Close". The most difficult for me, so far, has been the "Close" button. When the user clicks "Close," the system will first prompt the user to confirm that they wish to "Close" out the data form. After clicking "YES," Apps Script should check for an existing matching CAR Number (CAR numbers are unique) in the CAR Log and, if found, prompt the user with a dialog box to confirm that the data in the matching "CAR Log" row should be overwritten with the data in the "Entry Form". If the user clicks "YES", the data from "Entry Form" is copied and pasted to the row in the "Car Log" with the matching CAR No. If the user clicks "NO", cells containing data on the "Entry Form" will be cleared so that new data can be entered.

Sample Google Sheet w/ Entry Form and CAR Log:

Here's the code I've written so far:

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;
  };
}

I will be very grateful for any direction.

CodePudding user response：

If I understand your question clearly, your main goal is to stop your script from running when No button gets clicked on your first dialog prompt (responseConfirmClose) :

FINDINGS

On your script, responseOverwrite runs right away after the first dialog responseConfirmClose prompt, even after clicking No, because it is being initialized & run at the beginning of your code.

SUGGESTION

On your logic, I would suggest to place your responseOverwrite variable inside of your first if conditional statement (if YES button was selected on the responseConfirmClose dialog) as seen on the tweaked script below:

[UPDATED]

Tweaked Script

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
}

Sample Demonstration:

Sample Entry Form with data

Sample CAR Log with data

After clicking YES on the dialog prompt, since there's already a matching CAR No on row #4 on the CAR Log, it will then be overridden with new data:

Entry Form will be cleared after that Or it will be cleared when No is clicked from the prompt: