Google Script Select and copy some values from spr

Question

I have read this two links Google Script: Conditionally copy rows from one spreadsheet to another and https://stackoverflow.com/a/4809413/1526044 and after trying i am not able to do what i need.

I have one spreadsheet with some data on one sheet. Also i have another sheet with key information on columns to compare this way:

KEY Value1 Value2 Value3 
AAA   B      C      D
BBB   Y      Z      W

I need a function that check the my DATA spreadsheet row by row looking for the values on the columns values on the key sheet. And when any of the values is found it writes down on the first column of the DATA spreadsheet (on the same row) the KEY value.

Example: DATA spreadsheet

empty text text text B
empty text text text text
empty text   C  text text
empty text text text Y

So after the script, DATA will look like this:

AAA text text text B
empty text text text text
AAA text   C  text text
BBB text text text Y

Any other suggestion to make this will be appreciated. Thank you very much.

Answer 1

I modified a similar script I answered recently that should do what you need. I didn't test but you can give it a try. There are some comments to explain the idea... it probably needs some debugging.

EDIT : I played around with this and debugged it a bit more ;-) so it seems to work as needed now - here is the link to my test sheet

function xxx(){ 
/* we have an array 'keysheet'
    and an array datasheet , you get these with something like*/
        var keysheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getDataRange().getValues();
    // and
        var datasheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1].getDataRange().getValues();// if other columns, change index values in the arrays : 0=A, 1=B ...
    // the iteration could be like this :
              for(i=0;i<datasheet.length;++i){ // don't miss any data
                for(j=0;j<keysheet.length;++j){ // iterate through keysheet to find the keys
                  for(k=1;k<keysheet[0].length;++k){ // iterate in keysheet row to check every column except first one
                      var key = keysheet[j][k]
                      Logger.log(k+' '+key+"  "+datasheet[i].toString().match(key))
                 if(datasheet[i].toString().match(key) == key){// check whole rows to find if key is in data
                  var x = datasheet[i].shift();
                   datasheet[i].unshift(keysheet[j][0]) // if match found, replace first element in datasheet with fisrt element in keysheet
                     break  // if found break k loop maybe you should also break j loop ?
                       }
                 }
                   } //loop keysheet
             } // loop datasheet

            /* now you have a modified datasheet array that can directly overwrite the old datasheet 
            using setValues() */
            var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];// assuming the datasheet sheet is sheet nr2
            Logger.log(datasheet)
           sh.getRange(1,1,datasheet.length,datasheet[0].length).setValues(datasheet);
              }
        //