how to search in spreadsheet using google apps script?

Question

I have a reading log in a spreadsheet. Column 3 is the book name, 4 is the start page, and 5 is the end page. I want to make a script that could find the current book name in the reading log, then offset(0,2).getValue() to get the end page, and put it next to the active cell in column 3. This is what I try:

function OnEdit(e){
var s = e.source.getActiveSheet();
var r = e.source.getActiveCell();
if (r.getColumn() == "3" && r.getValue() != "") {
e.range.offset(0,1).setValue(SpreadsheetApp.getActive().createTextFinder(r.getValue()).findPrevious().offset(0,2).getValue())
}
}

However, this script doesn't work. It doesn't fill in anything at all. I expect the function will do the following:

1. When I add a new book name on Column 3, it search at Column 3 for the book name I currently enter, and result the the range of the cell just before this one but not this one.
2. Get the cell two column on the right of the result.
3. Get its value.
4. Put the value in the cell one column on the right of the cell I just edit.

Example:

Book Name	Start Page	End Page
1212121	p.12	p.13
1443444	p.6	p.9
1212121 [just edited]	[put in p.13]

Which part of my script is incorrect? The search part? Or other parts?

Answer 1

There are several parts of your script that use the wrong syntax or are not thought through.

Function name

• your script should be named onEdit(e)

Column to watch

• you are focussing on an edit in Column 3 (the title).
• you actually need to focus on the edit in Column 4. This is because at the time of the edit in Column 3, there is no value to be returned in Column 4
• OTOH, if you focus on the edit in Column 4, you can test for the value in Column 3 AND Column 4.

IF

• you need to test that the values in BOTH Column D AND Column C aren't empty - this is simple troubleshooting.

TEXTFINDER - FINDNEXT

• you need to create a range for the TextFinder to analyse,
• the search is from the top down, so use FINDNEXT
• you should have some troubleshooting to detect if the TextFinder does NOT find a match

Delete the edited data

• once the title has been found , and the starting page has been updated, then you should delete the newly entered data from the bottom of the column(s)

---

Alternative Code

function onEdit(e){
  var s = e.source.getActiveSheet()
  var r = e.source.getActiveCell()
  
  var titleValue = r.offset(0,-1).getValue()
  
  // Logger.log("DEBUG: the active cell = "+r.getA1Notation()+", the column is "+r.getColumn()+", the row is "+r.getRow()+",  the page value = "+r.getValue()+", the title cell= "+r.offset(0,-1).getA1Notation()+"and the title value = "+titleValue)
  
  if (r.getColumn() == "4" && r.getValue() != "" && titleValue != "") {
    
    // Create  a text finder for the title range.
    // set the Range
    var searchRange= s.getRange(2,3,r.getRow()-2) // number of rows allows for header
    // Logger.log("DEBUG: the search range = "+searchRange.getA1Notation())

    // create a textfinder
    var textFinder = searchRange.createTextFinder(titleValue)

    // Return the first occurrence of 'title Value'.
    var firstOccurrence = textFinder.findNext()
    //Logger.log("DEBUG: the first occurance of the title = "+firstOccurrence.getA1Notation())
    
    //define the start value range
    var startRange = firstOccurrence.offset(0,1)
    //Logger.log("DEBUG: the start cell is "+startRange.getA1Notation() )
    
    // update the start page value
    startRange.setValue(r.getValue())


  }
  else{
    // The IF statement wasn't successful
  Logger.log("the if is NOT working")
  }
}