Reputation: 15
I'm working on a script that allows users to enter data into a spreadsheet using an HTML form. I'm trying to implement a feature where the script searches through a column for a string from the form: if found, the old data in the row should be overwritten with data from the form; if not, the data should be appended as a new row.
This the snippet I have right now:
var col4 = sheet.getRange(2, 4, sheet.getLastRow(), 1);
var searchCol4 = SSNs.createTextFinder(e.parameter.Col4);
searchCol4.matchEntireCell(true);
var existingCol4Row = searchCol4.findNext().getRow();
if (existingCol4Row !== null) {
var nextRow = existingCol4Row
} else {
var nextRow = sheet.getLastRow() + 1
}
var newRow = headers.map(function(header) {
return e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
The problem I have is that the script only works if it finds a match in the column; if it doesn't, it doesn't add a new row as it should.
If I replace the whole if ...
statement with var nextRow = sheet.getLastRow() + 1
then the script appends new rows, but obviously it doesn't replace old data anymore. I've also tried changing the condition to just existingCol4Row
, with the same result.
Any tips?
EDIT: I got the code working, thanks to ziganotschka. I changed the conditional statement to check if searchCol4.findNext()
was null
rather than searchCol4.findNext().getRow()
. I also made sure the code would only call .getRow()
if searchCol4.findNext()
wasn't null
, as calling the method on a null range seems to make the code fail (anything past that line wouldn't run, including logging). Here's the working snippet:
var Col4 = sheet.getRange(2, 4, sheet.getLastRow(), 1);
var searchCol4 = Col4.createTextFinder(e.parameter.Col4);
searchCol4.matchEntireCell(true);
var existingCol4 = searchCol4.findNext();
if (existingCol4 !== null) {
var nextRow = existingCol4.getRow();
} else {
var nextRow = sheet.getLastRow() + 1
}
Upvotes: 1
Views: 618
Reputation: 26836
Log searchSSNs.findNext()
and searchSSNs.findNext().getRow()
.
If searchSSNs.findNext()
is NULL
, getRow()
will return you the first row of the search range, so in your case row 2.
Thus,
searchSSNs.findNext().getRow()
will never be NULL
Change your conditional statement to
if (searchSSNs.findNext() !== null) {
Sidenote: There is a confusion with the name of your textfinder range (
searchSSNs
orsearchCol4
?), I assume this is jsut a typing mistake.
Upvotes: 1