CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
xyz
xyz

Reputation: 2300

Match text strings of two columns on different sheet and post back using google script

I am trying to match two columns on two separate worksheets in the same workbook

I'm am trying to match column A in both sheets

SheetA = FindReplace SheetB = Test

If a match exists then post back the value to the right of the match cell in SheetA to the same row in SheetB but in Column B

The ranges in the matching columns may be different in size

The function runs but NO matches are found

Also, I think I am posting back each row that is found but I would like to post back only once after ALL matches are found

Thank you for any help with this

If is helpful here a GS with some data and the function in it:

https://docs.google.com/spreadsheets/d/1cVyCmnFYy_4Ghmx1r-dvCGhxrnu-tCPaKZSX_BRHfnM/edit?usp=sharing

function MatchColumns(){
  // gets spreadsheet A and the range of data
  var sheetA =SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FindReplace");
  var dataA = sheetA.getRange('A:B').getValues();

  // gets spreadsheet B and the range of data
  var sheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
  var dataB = sheetB.getRange('A:B').getValues();

  // loops through column A of spreadsheet A & B and compare
  for(var i = 0; i > sheetA.getLastRow(); i++){
  // checks to see if ith row value in 1st Column is the same
  if(dataA[i][0] == dataB[i][0]){
  //if match then get cell value in ajacent cell column B to right in sheetA
  var value = sheetA.getRange(i+1, 2).getValue();
  //Post Back the value in cell to column B in sheet B
  sheetB.getRange(i+1, 2).setValue(value);
  };
 };
}

Upvotes: 0

Views: 1200

Answers (2)

iMarketingGuy
iMarketingGuy

Reputation: 696

The function did run after changing the > for <

Upvotes: 1

Tanaike
Tanaike

Reputation: 201388

How about this modification? Please think of this as one of several solutions.

  • It processed the data as array.
  • If the value of column A of sheetB is not existing to the column A of sheetA, if (b != res.length - 1) res.push([""]); is used.
    • The length of created array is necessary to be the same to the length of dataB. I used this.

Modified script :

function MatchColumns(){
  // gets spreadsheet A and the range of data
  var sheetA =SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FindReplace");
  var dataA = sheetA.getRange(2, 1, sheetA.getLastRow(), 2).getValues();

  // gets spreadsheet B and the range of data
  var sheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
  var dataB = sheetB.getRange(2, 1, sheetB.getLastRow(), 1).getValues();

  // Added
  var res = [];
  for (var b in dataB) {
    for (var a in dataA) {
      if (dataA[a][0] == dataB[b][0]) res.push([dataA[a][1]]);
    }
    if (b != res.length - 1) res.push([""]);
  }
  sheetB.getRange(2, 2, res.length, res[0].length).setValues(res);
}

Result :

enter image description here

If I misunderstand your question, please tell me. I would like to modify.

Upvotes: 1

Related Questions