Google apps Script to find cell containing text in Google sheet-CodePudding

I'm trying to write a script to find text in a cell and go to that cell. Having done that I want to move to the right in the sheet. The text that it should find is in cell G1. The contents of G1 will change.

This:

function FindProduct () {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Stock'), true);
var textToFind = "20329";
var finder = SpreadsheetApp.getActiveSpreadsheet().createTextFinder(textToFind);
finder.findNext().activate()
};

Finds the text "20329" no problem but I cannot get it to look at G1 to get the text to find.

This is what I tried

function FindProduct () {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Stock'), true);
var textToFind = ('g1');
var finder = SpreadsheetApp.getActiveSpreadsheet().createTextFinder(textToFind);
finder.findNext().activate()
};

G1 contains 20329. This does not work but instead goes to cell G55 in another sheet called "Customers".

Can anyone help please?

Thanks

CodePudding user response：

You can try the following script:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Testing')
      .addItem('Find Customer', 'FindCustomer')
      .addToUi();

};

function FindCustomer () {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stock");
  var data = ss.getDataRange().getValues();
  var textToFind = ss.getRange('G1').getValue();

  for(var x=0;x<data.length; x  )
  {
    if(data[x][0]==textToFind)
    {
      break;
    }
  }

  var r = ss.getRange("A" `${x 1}`);
  var i=0;

  while(r.offset(0, 17 i).getValue()!="")
  {
    i  ;
  }
  for(var y=0; y<3; y  )
  {
    r.offset(0, 17 i y).setValue(textToFind);
  }

}

References:

offset()