匹配不同工作表上的两列文本字符串，并使用Google脚本回发 [英] Match text strings of two columns on different sheet and post back using google script

问题描述

我试图在同一工作簿中两个单独的工作表上匹配两列

我正在尝试匹配两张表中的列A

SheetA = FindReplace SheetB = Test

如果存在匹配，则将该值返回到SheetA中匹配单元的右侧在SheetB中的同一行，但在列B中

匹配列中的范围可能大小不同

该函数运行，但没有找到匹配

另外，我认为我回发了找到的每一行，但是我想只在找到所有匹配后回发一次

感谢您的帮助

如果这里有帮助，它：

如果我误解了您的问题，请告诉我。我想修改。

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);
  };
 };
}

解决方案

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 :

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

这篇关于匹配不同工作表上的两列文本字符串，并使用Google脚本回发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！