比较两列，如果匹配，则粘贴匹配值 [英] Compare two columns and if match, paste matching value

问题描述

这似乎应该超级简单，但我确实无法通过Google Script找到解决方案.我想将导入"中的SKU(A)中的所有单元格与价格表"中的SKU(A)进行比较，如果有匹配项，则将匹配的SKU粘贴到工作表3(A)中.

This seems like it should be super simple but I really cannot find a solution via Google Script. I want to compare all the cells in SKU (A) in Import to SKU (A) in Pricelist, and if there is a match then paste the matching SKU's into Sheet 3 (A).

通常我只是通过索引匹配来做到这一点，但我需要使用Google Script来做到这一点.

Normally I would just do this through index match but I need to do it with Google Script.

我尝试了以下方法，该方法可用于检查价目表A1:A100中整个范围的单个单元格(A81)，但是在根据范围检查范围时，我无法理解它.

I have tried the following and this works for checking a single cell (A81) with the whole range in Pricelist A1:A100 but I cannot make sense of it when checking a range against a range.

function checkProduct() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lookup = ss.getRange('Import!A81').getValue();
  var range = ss.getRange('Pricelist!A1:A200').getValues();
  var lookupRange = [];

  for (var i = 0; i < range.length; i++) {
    lookupRange.push(range[i][0]);
  }
  var index = lookupRange.indexOf(lookup);
  if (index == -1) {
  }
  else {
    ss.getRange('Sheet3!A1').setValue('its there'); // need to paste in the matching SKU
  }
}

导入表

SKU | Price
s123 | 99
s124 | 98
s125 | 97

价格表

SKU | Price
s123 | 99
a111 | 98
a453 | 97

推荐答案

尝试一下:

function checkProduct() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getSheetByName("Import")
  var lr=s.getLastRow()
  var lookup = s.getRange(2,1,lr-1,2).getValues();
  var s1=ss.getSheetByName("Pricelist") 
  var lr1=s1.getLastRow()
  var range = s1.getRange(2,1,lr1-1,2).getValues();
  var s3=ss.getSheetByName("Sheet3")
  var lookupRange = [];
  for (var i = 0; i < lookup.length; i++) {
     for (var j = 0; j < range.length; j++) {
     var  test=lookup[i][0]
         if(lookup[i][0]==range[j][0]){
           lookupRange.push([range[j][0],range[j][1],lookup[i][0],lookup[i][1],]);
     }}}
   s3.getRange(2,1,lookupRange.length,4).setValues(lookupRange); 
}

我要在比赛中推送所有4个值.根据需要进行调整.

I am pushing all 4 values on a match. Adjust as needed.

这篇关于比较两列，如果匹配，则粘贴匹配值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！