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
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!
two columnsofI 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.? AboutMy Apps Script works perfectly when adding new rows, when you tested your showing script, your showing script worked?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 ofSaveData()is not run because of<button onclick="SaveData">. How about this?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?