CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Quinten De Wilde
Quinten De Wilde

Reputation: 25

Find text and copy row to other sheet

Hi I have a list of data in Google Sheets that I want to use to extract certain names based upon another list.

Following code works to point me out on what row the search_string is:

function searchString(){
  var sheet = SpreadsheetApp.getActiveSheet()
  var search_string = "searchname1"
  var textFinder = sheet.createTextFinder(search_string)
  var search_row = textFinder.findNext().getRow()
  var ui = SpreadsheetApp.getUi();
  ui.alert("search row: " + search_row)
}

I want to find out how I can add multiple search_strings like below. Its quite a list. I tried array brackets but that gives me an error of NULL.

function searchString(){
  var sheet = SpreadsheetApp.getActiveSheet()
  var search_string = ("searchname1","searchname2")
  var textFinder = sheet.createTextFinder(search_string)
  var search_row = textFinder.findNext().getRow()
  var ui = SpreadsheetApp.getUi();
  ui.alert("search row: " + search_row)

}

Then instead of showing me an alert I want to copy the rows that are found to a new sheet. In the subsequential order of the array of search_strings.

Upvotes: 1

Views: 152

Answers (1)

Marios
Marios

Reputation: 27348

Explanation:

Your goal is to copy the rows where the strings appear (for the first time) in the source sheet to a target sheet.

  • You can include all the strings you want to search in the search_strings array.

  • Iterate over each string to find the first row it appears and add that row to the rows array.

Then you can get all the data from the source sheet and filter only the rows that are part of the rows list:

var data = source_sheet.getDataRange()
         .getValues().filter((_,i)=>rows.includes(i+1));

Finally copy the data to the target sheet.

Solution:

function searchString(){
  const ss = SpreadsheetApp.getActive();
  const source_sheet = ss.getSheetByName('Sheet1'); // change the name
  const target_sheet = ss.getSheetByName('Sheet2'); // change the name     
  const search_strings = ["searchname1","searchname2"]; // add your strings to search
  const rows = [];

  search_strings.forEach(search_string=>{                    
         let textFinder = source_sheet.createTextFinder(search_string);
         try{
            let search_row = textFinder.findNext().getRow();
            rows.push(search_row);     
         } catch(e){}
  });
  const data = source_sheet.getDataRange()
             .getValues().filter((_,i)=>rows.includes(i+1));

  if(data.length>0){
    target_sheet.getRange(1,1,data.length,data[0].length).setValues(data);
  }
}

Upvotes: 2

Related Questions