0

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?

5
  • HI, welcome to WebApps. You've only shared a snippet of code. Would you please edit your question to include the complete function. Would you also describe your understanding of how you initiate execution of this function.
    – Tedinoz
    Sep 16, 2022 at 0:57
  • I'm sorry the table doesn't show up. May someone tell me why isn't the table showing up? It already happened twice.
    – Flyson
    Sep 16, 2022 at 14:43
  • Based on this data, do you expect to have multiple entries for each "book title"; each title having a different start and end date?
    – Tedinoz
    Sep 22, 2022 at 0:23
  • Yes, there will be repeated book titles. What I want to do is actually find if I already read the book, if I already read this book, and return the end page of the last entry to the start page of this new entry. I'll put in the book name first when I add a new entry. This is a real application, just think of you reading a book, you stop at p,13, next time you read this book you would start at p.13, is it?
    – Flyson
    Sep 24, 2022 at 16:52
  • Can you see the table when you first open the question? Every time I post a question with a table, on my screen it is only text rather than a table.
    – Flyson
    Sep 24, 2022 at 16:54

1 Answer 1

0

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")
  }
}
2
  • How to make troubleshoot? I guess other people use a logger but every time I use a logger I don't know where to find the information it returns. And also, I want to search from bottom to top and return the second result. You misunderstand my question, but I think I don't need it right now. But it will be great if you can tell me how to do it and I may use it in the future.
    – Flyson
    Sep 24, 2022 at 16:49
  • Trouble-shooting. Suggest you do some basic research on this. console.log, Logger.log, ui.alert and others
    – Tedinoz
    Sep 27, 2022 at 1:38

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.