CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Simon Stockhaus
Simon Stockhaus

Reputation: 15

Search for value in column and overwrite the row, otherwise add new row

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

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

Log searchSSNs.findNext() and searchSSNs.findNext().getRow().

Result

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

Solution

Change your conditional statement to

if (searchSSNs.findNext() !== null) {

Sidenote: There is a confusion with the name of your textfinder range (searchSSNs or searchCol4?), I assume this is jsut a typing mistake.

Upvotes: 1

Related Questions