Using textFinder with text from a specific cell

[Jim U]

I have been trying to find an example of this for a while with no luck.  

I have two sheets 'Inventory' and 'Sold'.  I enter my 'Sold' item in Cell A2 of the Sold sheet, then enter more info about it (who sold to and how much, etc.) at that point I want to automate taking the item from the 'Inventory' sheet by simply making it blank in the inventory sheet (it is just a single cell on the inventory sheet).  

I have been able to use textFinder when looking up specific text and then change it to " ", so wondering if there is a way to change this code to look up the specific text in cell 'A2' on the 'Sold' sheet since the text in A2 will change each time I sell a different item.

Here is the snip I would use if A2 was always 'Blanket Red':

function searchAndReplace(searchTerm, replacement) {

let textFinder = SpreadsheetApp.getActive().getSheetByName("Inventory").createTextFinder("Blanket Red").replaceAllWith("");

}

I have tried various was to get the value in A2 and it does not seem to work in the createtextFinder. 

I don't have a ton of data, so I thought this would be a simple way to find and replace the value but cannot figure out how to insert the text from 'A2' instead of search for a constant text string. Is there a way to do this?  

Thanks in advance.

- J

[CBMServices Web]

Hi Jim,

What is createTextFinder? I assume you meant to use textFinder.

To grab the value of A2 in Sold do this:

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sold Sheet = ss.getSheetByName("Sold");

var value = sold Sheet.getRange(1,2).getValue();

Then use the variable Value in the parentheses when you do your replacement.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/87c439f5-0113-48c8-b282-3631d58de490n%40googlegroups.com.

[cwl...@gmail.com]

Assuming that the text in cell A2 is like a dropdown list or even just typed-in text (possible erros due to typos...), you need to be very specific on the search text. Right now, your function isn't pointing to cell A2 anywhere. So you have to add that in.

function searchAndReplace(searchTerm, replacement) {
    let findText;
  

    if (searchTerm) {findText = searchTerm} //if the search term is passed, use it
       else { findText = SpreadsheetApp.getActive().getSheetByName("Sold").getRange("A2").getDisplayValue() }  //otherwise, get the value of cell A2 of the Sold sheet
    let textFinder = SpreadsheetApp.getActive().getSheetByName("Inventory").createTextFinder(findText).replaceAllWith("");
}

[Jim U]

Yes this worked.  The getDisplayValue was what I was missing when I had tried to getRange on A2 before.  Thanks for the help!