Add/Update Google Sheet Row Data Comparing Values from HTML Form Using Google Apps Script

I have an HTML form that allows users to populate the form from a Google Sheet, and successfully add new rows to the Sheet. I created the Apps Script according to this tutorial.

I am trying to expand the function of my Apps Script to search the rows of entries for duplicates based on data from two columns and if there is a match, overwrite the existing row.

I know I need a for loop that iterates through each row in the Google Sheet to compare to the form data fields in question, but I don't know how to access each.

This is a shortened example of the the form (the actual is much longer):

<form id="form">
  <form id="form">
  <input name="firstName" placeholder="firstName" />
  <input name="lastName" placeholder="lastName" />
  <input name="someOtherField" placeholder="someOtherField" />
  <input name="someFourthField" placeholder="someOtherField" />
  <div  onclick="SaveData()">Save Data</div>
</form>
</form>
<script>
function SaveData() {
  var formData = new FormData(document.getElementById("form"));
  fetch('https://script.google.com/macros/s/AKfycbwQFSXfeOKBHzf41MF6Nh5XIOjaPvr159-blUxsg5smD3BDH8qB4RUZRRo8q9nCJLb18w/exec', 
        {
      method: 'post',
      body: formData,
  })
}
</script>

My Apps Script works perfectly when adding new rows, but either my for loop is not written correctly and thus not finding matches or otherwise setting the nextRow index isn't working:

var sheetName = 'Entries'
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow()   1

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    var range = sheet.getDataRange();
    var rangeData = range.getValues();

    // Here is where the script is failing to find a match in the spreadsheet
    for(i = 1; i > rangeData.length; i  ) {
      if(rangeData[i][0] == e.firstName && rangeData[i][1] == e.lastName) 
      {
        nextRow = i;
      }
    }

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

With the above script, every form entry creates a new row, the match is never true. So I think there is something wrong either with my logic in matchfinding or the syntax I'm using to find the form values and spreadsheet data. So what am I missing?

Thanks!

EDIT: To help elaborate this example, I have attached a screenshot of a sample spreadsheet. As is, if a user submits a form with firstName "Bob" and lastName "Belcher," I would like to have the remaining form data overwrite the existing row with those names. But if the firstName and lastName fields have no match in the spreadsheet, add a new row.

The above script always adds new rows even if there is an existing row. I tried using the logic from this question to achieve my desired result but this caused the script to fail entirely (no updated rows and no added rows). I also tried using the logic steps outlined in this video which uses "indexOf" form data instead of a for loop with if statement to find matches. This solution also did not work

Spreadsheet Screenshot

EDIT EDIT: I have made the whole script available and recorded a short video of the current script behavior versus the desired behavior. Hopefully this clarifies my question. Thanks!

CodePudding user response：

I believe your goal is as follows.

When the HTML form is submitted, you want to check the values of firstName and lastName from the Spreadsheet. And, when those values are existing in the Spreadsheet, you want to update the row. When those values are not existing in the Spreadsheet, you want to append the data as a new row.

When your script is modified for achieving this, how about the following modification?

From:

for(i = 1; i > rangeData.length; i  ) {
  if(rangeData[i][0] == e.firstName && rangeData[i][1] == e.lastName) 
  {
    nextRow = i;
  }
}

To:

for (var i = 0; i < rangeData.length; i  ) {
  if (rangeData[i][0] == e.parameter.firstName && rangeData[i][1] == e.parameter.lastName) {
    nextRow = i   1;
    break;
  }
}

In your showing script, in the case of for(i = 1; i > rangeData.length; i ), no loop is done. And, e.firstName and e.lastName are always undefined. And also, in your script, it is required to finish the loop when the values were found.

Note:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".