0

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 class="btn-success btn" 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!

11
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. What is two columns of 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.? About My Apps Script works perfectly when adding new rows, when you tested your showing script, your showing script worked? Commented Jul 2, 2022 at 4:10
  • Hi @Tanaike, no worries at all. The workflow I am trying to achieve is this: - User Submits form - Apps Script compares the first two input fields of the form to the first two values from each row of the spreadsheet - If both input values already exist in in the spreadsheet, overwrite the exisitng row - If no match is found, add a new row with the input values The script above works, but it only adds new rows, it does not find the matching rows even when the input values are the same as existing rows. Please let me know if that helps or if I should elaborate more. Thanks! Commented Jul 2, 2022 at 12:58
  • Thank you for replying. About The script above works, I think that your showing Javascript script doesn't work. By this, your Web Apps is not run. Because in your HTML, I think that even when the button is clicked, the function of SaveData() is not run because of <button onclick="SaveData">. How about this? Commented Jul 2, 2022 at 23:43
  • Thanks for the suggestion. The JavaScript functions as normal. When I click the button that triggers the fetch request for the Apps Script URL, it does process and execute the script, and adds a new row with the html form data. The part that is not working is the for loop that looks previous entries with matching values as the form input fields. Commented Jul 3, 2022 at 2:29
  • Thank you for replying. About The JavaScript functions as normal., unfortunately, I cannot replicate your situation. In your showing script, I think that even when a button is clicked, the function of "SaveData" is not run with <button onclick="SaveData">Save Data</button>. So, I'm worried that you might have miscopied your scripts. In this cacse, I'm worried that even when we proposed a modified script, that might not be useful for your actual situation. Can you confirm your showing script again? Commented Jul 3, 2022 at 2:48

1 Answer 1

0

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:

Sign up to request clarification or add additional context in comments.

2 Comments

Thank you @Tanaike! This solution successfully added the functionality I was looking for! I appreciate your time and patience assisting me with this script.
@StephanB Thank you for replying and testing it. I'm glad your issue was resolved. I could correctly understand your question with your cooperation. Thank you, too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.