CODEWITHSUNDEEP
formsvalidationgoogle-apps-scriptdialog
Joan S
Joan S

Reputation: 1

In Form Script - Obtain Google Form values and pop up a dialog to form submitter

Google Form/Sheet - When a user clicks Submit on the form, I want to check if one of the responses is already in the Google Sheet. If it is, I want to prevent submission of the form & pop-up a message telling them their data is already assigned.

I tried adding Google Script at the spreadsheet level. But the pop-up messaging does not work, as the form is what the end-user is interacting with, not the sheet.

Code in the sheet:

//Add code to form submit on the Add Key form
function onFormSubmit(e) {
  /* a variable name with Camel Case - every new word Capital */

  /* tell it to trigger on form submit */
  var ss = SpreadsheetApp.getActiveSpreadsheet();  /* sheet for data to goto */
  var sheet_SLAAssignedIdsResponses = ss.getSheetByName("SLAAssignedIdsRESPONSES"); 

  /* calls to the spreadsheet takes a lot of time - resource & same with writing to spreadsheet */
  /* better to to NOT go row by row - SLOW!!! */
  /* have it in memory in an array - faster, then modify it and spit it back out */
  /* house it in memory & read/write when have to */
  var lastRow_SLAAssignedIdsResponses = sheet_SLAAssignedIdsResponses.getLastRow(); /* getting the last added row */  

  var StudentAssignmentExistsAlready = "";
  var SLAIdValue = "";
  var SLAIdCount = "";
  var SLAStudentName  = "";

  // Assign formulas to G and H
   // G - Count # of times SLA ID appears assigned.
  var cell = sheet_SLAAssignedIdsResponses.getRange("G" + lastRow_SLAAssignedIdsResponses); 
    //=IF(ISBLANK(A2),"",COUNTIF(D:D,D2:D))
  cell.setFormula('=IF(ISBLANK(A2:A),"",COUNTIF(D:D,D2:D))');

  // H - Full Name
  var cell = sheet_SLAAssignedIdsResponses.getRange("H" + lastRow_SLAAssignedIdsResponses); 
  // IF(ISBLANK(E2:E)=FALSE,TEXT(E2:E,"00"),"")
  //=B2:B&" "&C2:C
  cell.setFormula('=B2:B&" "&C2:C');

  /*********************************************************/
  // Get the incoming SLAIdValue trying to be assigned.
  SLAIdValue = sheet_SLAAssignedIdsResponses.getRange("D" + lastRow_SLAAssignedIdsResponses).getValue(); 
  //SLAIdValue = sheet_SLAAssignedIdsResponses.getRange(lastRow_SLAAssignedIdsResponses, 3).getValue();
  Logger.log("The value of SLA ID " + SLAIdValue);

  // Get the COUNT for SLAId
  //SLAIdCount = sheet_SLAAssignedIdsResponses.getRange(lastRow_SLAAssignedIdsResponses, 6).getValue();
  SLAIdCount = sheet_SLAAssignedIdsResponses.getRange("G" + lastRow_SLAAssignedIdsResponses).getValue(); 
  Logger.log("The value of SLA ID COUNT " + SLAIdCount);

  // Get the current student assigned
  //SLAIdCount = sheet_SLAAssignedIdsResponses.getRange(lastRow_SLAAssignedIdsResponses, 6).getValue();
  SLAStudentName = sheet_SLAAssignedIdsResponses.getRange("H" + lastRow_SLAAssignedIdsResponses).getValue(); 
  Logger.log("The value of SLA Student Name " + SLAStudentName);

  /*********************************************************/
    // 1 - Look to see if the count for the current row is > 1 - If so, this SLA account is already assigned.
    // 2 - Find the SLAId already assigned for this student that is not equal to this one.

   **if (SLAIdCount >= 2 ){    
     Logger.log("Pop up response ");
   //  var ss = SpreadsheetApp.getActiveSpreadsheet();  /* sheet for data to goto */
    // var sheet_SLAAssignedIdsResponses = ss.getSheetByName("SLAAssignedIdsRESPONSES");

     var sh=SpreadsheetApp.getUi();
     var response=sh.alert("Duplicate SLA ID Assignemnt", "Please select a different Google Id for the Non-District Student.", sh.ButtonSet.OK);

     // Proceed with deleting the LastRow, as we cannot allow this entry. 


   }**

  } // OnFormSubmit END

I am hoping I can pipe back a dialog box to let the form submitter that the information they are trying to assign is already assigned to someone else.

Right now, I can't get a dialog box to go back. I need help with the google apps script for Forms.

Upvotes: 0

Views: 555

Answers (1)

Ken Adams
Ken Adams

Reputation: 155

you do know that Google Form settings have an inbuilt option to restrict one submission per user.

Upvotes: 0

Related Questions