Notification when data already exists in a Google Sheet

Question

I am a beginner to app script. I am making a web app using Google app script to add resident's information to the google sheet. The web app to add residents but I should like the app to notify me if a record with the same house number and street already exists in the spreadsheet. Using the attached image of the spreadsheet, I should like to be notified if House Number 16 in the street Jalan Sanggul 4 is entered more than once. I have also attached my code which adds the data to the google sheet. This could be achieved easily by referencing the username, as these are unique but in this case, the username is generated only after the data has been input. The validation needs to reference the house number and street only. I hope anyone could help me.

Link to my web app - https://script.google.com/macros/s/AKfycbwcuxcbp3VL6t-fjB3Jaf0T4FiCb5Oa_g08s6Nx_bDMIRpF7_cmnwJ-ivwDMWF-CeIFUg/exec

Username : Mathavan

Password : Apple

Link to my app script code - https://script.google.com/d/1B9kBn4q5Hk2RsC_e3HutDeLCeHgyI-WmfHl8Mgtc5P4PN4VnnIYVY2GU/edit?usp=sharing

function appendData(values, spreadsheetId,range)
{
  var valueRange=Sheets.newRowData();
  valueRange.values=values;
  var appendRequest=Sheets.newAppendCellsRequest();
  appendRequest.sheetID=spreadsheetId;
  appendRequest.rows=valueRange;
  var results=Sheets.Spreadsheets.Values.append(valueRange,spreadsheetId, range,{valueInputOption: "RAW"});
}

Answer 1

From what I understand of the code, the form myForm in Form.html receives the data and uses the function handleFormSubmit(formObject) to call processForm(formObject) in the backend.

processForm(formObject) then either updates the data (which currently will never happen, since the form isn't sending the username) or appends a new row, so this is probably the place to check whether a row with the submitted data already exists.

Since you don't have the ID, it's probably better to use another function instead of checkID(ID). Something like the following would check if there is any row whose house number and street match the submitted data (this might get slow if there's a lot of data, so please check this answer for ways to make it faster):

function isHouseRegistered(number, street) {
  const houseNumberColumnIndex = 1;
  const streetColumnIndex = 2;

  // Get all house data in sheet
  const sheetRows = SpreadsheetApp.openById(globalVariables().spreadsheetId).getRange(globalVariables().dataRange).getValues();

  // Iterate through rows and return when the specified house is found
  for (const row of sheetRows) {
    if (row[houseNumberColumnIndex] == number && row[streetColumnIndex] === street) {
      return true;
    }
  }

  return false;
}

If the row is found, you can throw an exception so that it returns to the web app's frontend. Something like this:

function processForm(formObject){  
  if(formObject.username && checkID(formObject.username)){//Execute if form passes an ID and if is an existing ID
    updateData(getFormValues(formObject),globalVariables().spreadsheetId,getRangeByID(formObject.username)); // Update Data
  } else if (isHouseRegistered(formObject.housenumber, formObject.street)) {
    throw new Error("The specified house is already registered.");
  } else { //Execute if form does not pass an ID
    appendData(getFormValues(formObject), globalVariables().spreadsheetId, globalVariables().insertRange); //Append Form Data
  }
  return getLastTenRows();//Return last 10 rows
}

Then, on the frontend, you'll need to change the handleFormSubmit(formObject) function to include a failure handler and implement the function to show the message to the user. For example:

function handleFormSubmit(formObject) {
   google.script.run.withSuccessHandler(createTable).withFailureHandler(showErrorMessage).processForm(formObject);
    document.getElementById("myForm").reset();
}

function showErrorMessage(error) {
    alert(error.message);
}

The above code is untested, so some changes may be necessary.