google apps script - Find cells based off search, change their value, then add to a list on new sheet?

Question

Ok, title might be confusing. I mean, I'm confused just trying to make this work.

I essentially have 3 sheets.

1. The Search Sheet - Has a search box & macro button.
2. The Data Sheet - self explanatory.
3. The Target Sheet - Sorted into columns.

So, what I'm needing to happen is: when a user enters a value in the Search Sheet & hits the "go" button, it searches the Data sheet for any cells in column B that match. On a match, it modifies the matched cell so it won't match on another search, then copies the adjacent cell 'A' value into the first column of the Target Sheet, contuingin down the sheet & adding each match to the same column. On a new search, the results will show in the 2nd column, etc...

Search1 = "L1" | Search 2 = "L3"

Data Sheet: DataSheet

Result Sheet: Result Sheet

Here's my shitty attempt at scripting it. I probably defined the wrong things & didn't define right things. I'm trying to wrap my head around it, but I think I've gotten into the deep end too fast.

    function moveTest() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //Spreadsheet
  var source = ss.getSheetByName("Close");  //Input Sheet
  var sourceData = ss.getSheetByName("Raw_Data"); //Data Sheet (List of all data)
  var target = ss.getSheetByName("Pallet_Data");  //Target Sheet
  var dataRange = data.getRange("B:B").getValues; //Range To Search on Data Sheet
  var lastcolumn = target.getLastColumn(); //Last Column of Target
  var searchval = [[source.getRange("A2").getValue()]]; //Search String
  var targetRange = target.getRange("A"+(lastcolumn+1)+":C"+(lastcolumn+1)); //Define New Range n+1
  //var rIndex = getRowIndex(); //row index of Data
  //var cIndex = getColumnindex(); //column index of Data

  while (dataRange has_data) { // For each row that has data in column B...
    if (Bn = searchval) { // If B in row 'n' matches the search value
      var rIndex = sourceData.getRowIndex();  // Define that row
      var match = sourceData.getActiveCell // Defines B of matched row
      var firstcell = rIndex.getRange(??) // Define cell A of that row
      match.setValue('Moved'); // Change value of B in matched row so it won't match on new search
      firstcell.copyTo(lastcolumn) //Copy Cell A of defined row into last column of target sheet 
      //Add next match to bottom of SAME Column then start NEW Column when function is run again. 

    }
  }

Thanks

question from:https://stackoverflow.com/questions/65940309/find-cells-based-off-search-change-their-value-then-add-to-a-list-on-new-sheet

Answer 1

Your script could look like this:

function cellMatch() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet1 = ss.getSheetByName("Data");
  var sheet2 = ss.getSheetByName("Result");
  var sheet3 = ss.getSheetByName("Search");

  var lr = sheet1.getLastRow();
  var data = sheet1.getRange(2,1,lr-1,2).getValues();
  var lc = sheet2.getLastColumn()+1;
  var key = sheet3.getRange("B1").getValue();
  var matched = [["Search "+lc]];

  for (var i=0; i<lr-1; i++) {
    if (data[i][1] == key) {
      var temp = [];
      temp.push(data[i][0]);
      matched.push(temp);
    }
  }
  
  var result = sheet2.getRange(1,lc,matched.length);
  result.setValues(matched);

}

This should take the search key from Search sheet, compare it with the Data sheet, and place the results into Result sheet.

Sample Data:

This is the result after executing the script twice, for L1 and L3.