google-apps-scriptgoogle-sheetsduplicates

google script Prevent duplicate data Submit


I'm a beginner of learning google script. I need to submit data from one sheet to another using google script. When I click submitData, it should check whether it is previously entered data or not. If previously entered data, code must stop. If it is new entry, data mast sent to "output" sheet. Can anybody help me to figure out my error?

EDIT: I prepared a bio data format. I will share this google sheet among my school friends and asking to fill their details and click SUBMIT button. I want them to stop sending duplicate data. Their Identity Number is to be mention in "A3" cell. These data stored in "output" sheet. Identity number is stored in Column B. "A3" cell data match with Column B data in OUTPUT file and, if duplicate, msg prompt "duplicate".

Now When I run it , Data submitting to "output" sheet even DUPLICATE data entered in "A3" Cell.

 function submitData() {

 var ss        = SpreadsheetApp.getActiveSpreadsheet();
 var formSS    = ss.getSheetByName("input"); //Data entry Sheet
 var datasheet = ss.getSheetByName("output");
 var ttt       = formSS.getRange("A3").getValue();
 var values    = ss.getSheetByName("output").getDataRange().getValues();
    for (var i = 0; i < values.length; i++) {
  var row = values[i];
      if (row[1] == ttt) {  //column B of the "output" sheet has project IDs (Unique Number)
          
   SpreadsheetApp.getUi().alert('Duplicate data. you need to click  update');
      }
 else
{  
 var values = [[formSS.getRange("E2").getValue(),
                  formSS.getRange("A3").getValue()]],
datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 2).setValues(values);
    }
  }
}  


      

Solution

  • The "error" is that there isn't anything that stop the script execution when row[1] == ttt is true. One possible fis is to add

    return;
    

    immediately after

    SpreadsheetApp.getUi().alert('Duplicate data. you need to click  update');