Блог

Я чувствую, что этот сценарий работает почти так, как задумывалось, но я не уверен, где разместить определенные компоненты процедуры в цикле for для достижения желаемого результата. Туннельное зрение сейчас очень эффективно, вполне возможно, что я переосмысливаю простую задачу. Пожалуйста, дайте мне знать, если я смогу более четко описать проблему. Любые предложения или указания на существующий ресурс полезны.

Настройка: Первый лист содержит динамический вертикальный список текстовых значений, начинающихся в ячейке I3 и идущих вниз. Второй лист содержит динамический вертикальный список текстовых значений, начинающихся в диапазоне A2 и идущих вниз, и содержит аналогичный набор текстовых значений в тех же строках столбца B.

Цель:

• Получите значение каждой используемой ячейки в столбце I листа 1 (диапазон один)
• Получите значение каждой используемой ячейки в столбце A листа 2 (диапазон два)
• Для каждой используемой ячейки в первом диапазоне проверьте значение каждой ячейки первого диапазона, чтобы узнать, существует ли значение во втором диапазоне
• Если совпадение существует, получите номер строки ячейки во втором диапазоне, содержащей значение (если совпадение существует, то будет только одно совпадение).
• Получите значение ячейки в той же строке в столбце B листа 2
• Установите указанное выше значение в качестве значения ячейки в строке, содержащей значение, найденное в обоих диапазонах в столбце M листа 1

Ниже приведено то, что мне удалось придумать. Я могу заставить его функционировать до последнего шага. Похоже, что соответствующая переменная номера строки не обновляется с каждым циклом for.

 // Get values of range one
var lastRowRangeOne = sheetone.getRange('I3').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var rangeOneValues = sheetone.getRange('I3:I'   lastRowClientColumn).getValues();

// Get values of range two
var sheettwo = spreadsheet.getSheetByName('Sheet2');
var sheettwoLastRow = sheettwo.getLastRow();
var sheettwoList = sheettwo.getRange('A2:A'   sheettwoLastRow).getValues();
var sheettwoListFlat = sheettwoList.map(function(row) {return row[0];});

    for (var i = 0; i< rangeOneValues.length ; i  ){ // for each row in range one
    
      if (sheettwoListFlat[i] == rangeOneValues[i]) { // if the range one value exists in range two

        var rowNumber = sheettwoListFlat.indexOf(rangeOneValues[i])   3; // get the row number of the matching value found in range two
        var sheetOneColumnMValue = sheettwo.getRange('B'   rowNumber).getValue(); // get the cell value of the same row in sheet two column B
        var sheetOneRowNumber = i   3; // get the row number of the range one value
        sheetone.getRange('M'   sheetOneRowNumber).setValue(sheetOneColumnMValue); // set the cell value of sheet one column M row x, where x is sheetOneRowNumber
      }

    }
 

Если вы напечатаете значение sheettwoListFlat , оно будет содержать только 5 элементов. Как только итератор цикла for достигнет 5 или более, он автоматически установит if (sheettwoListFlat[i] == rangeOneValues[i]) { значение false. Также оператор будет работать только в том случае, если элементы одного и того же индекса обоих массивов равны.

Также было бы более эффективно, если бы вы искали Лист1, используя значения листа 2, так как лист 2 имеет меньшее значение.

Здесь я использовал TextFinder, чтобы найти текст в пределах диапазона.

Использование значений листа 2 в качестве ключа поиска

Код:

 function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetone = spreadsheet.getSheetByName('Sheet1');

  var lastRowRangeOne = sheetone.getRange('I2').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var rangeOneValues = sheetone.getRange('I2:I'   lastRowRangeOne);
  var sheettwo = spreadsheet.getSheetByName('Sheet2');
  var sheettwoLastRow = sheettwo.getLastRow();
  var sheettwoList = sheettwo.getRange('A2:B'   sheettwoLastRow).getValues();

  for (var i = 0; i < sheettwoList.length; i  ){
    var find = rangeOneValues.createTextFinder(sheettwoList[i][0]).findNext()
    if(find){
      var sheetOneRowNumber = find.getRow();
      sheetone.getRange('M'   sheetOneRowNumber).setValue(sheettwoList[i][1]);
    }
  }
}
 

Использование значений листа 1 в качестве ключа поиска

Код:

 function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetone = spreadsheet.getSheetByName('Sheet1');

  var lastRowRangeOne = sheetone.getRange('I2').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var rangeOneValues = sheetone.getRange('I2:I'   lastRowRangeOne).getValues();
  var sheettwo = spreadsheet.getSheetByName('Sheet2');
  var sheettwoLastRow = sheettwo.getLastRow();
  var sheettwoList = sheettwo.getRange('A2:B'   sheettwoLastRow);

  for (var i = 0; i< rangeOneValues.length ; i  ){ // for each row in range one
    var find = sheettwoList.createTextFinder(rangeOneValues[i][0]).findNext();
    if(find){
      var rowNumber = find.getRow()
      var sheetOneColumnMValue = sheettwo.getRange('B'   rowNumber).getValue(); // get the cell value of the same row in sheet two column B
      var sheetOneRowNumber = i   2;
      sheetone.getRange('M'   sheetOneRowNumber).setValue(sheetOneColumnMValue);
    }
  }
}
 

Выход:

Примечание: Оба выводят один и тот же результат, но используют значения листа 2 в качестве ключа поиска быстрее, чем другой.

 function compareTwoCols() {
  const c1 = 'COL1';//column names
  const c2 = 'COL3';
  const ss1 = SpreadsheetApp.openById(gobj.globals.datagenid);//data set 1
  const sh1 = ss1.getSheetByName('Sheet1');
  const [hd1, ...vs1] = sh1.getDataRange().getValues();
  let col1 = {};
  hd1.forEach((h, i) => { col1[h] = i });
  const ds1 = vs1.map((r, i) => {
    return r[col1[c1]];
  });
  const ss2 = SpreadsheetApp.openById(gobj.globals.ssid);//data set 2
  const sh2 = ss2.getSheetByName('Sheet0');
  const [hd2, ...vs2] = sh2.getDataRange().getValues();
  let col2 = {};
  hd2.forEach((h, i) => { col2[h] = i });
  const ds2 = vs2.map((r, i) => {
    return r[col2[c2]]
  });
  let matches = { pA: [] };
  let idx = -1;
  ds1.forEach((e, i) => {
    let from = '';
    do {
      idx = ds2.indexOf(e, from);
      if (~idx) {
        if (!matches.hasOwnProperty(e)) {
          matches[e] = [];
          matches[e].push({ val1: e, row1: i   2, col1: col1[c1]   1, row2: idx   2, col2: col2[c2]  1 });
          matches.pA.push(e);
        } else {
          matches[e].push({ val1: e, row1: i   2, col1: col1[c1]   1, row2: idx   2, col2: col2[c2]   1});
        }
        from = idx   1;
      }
    } while (~idx);
  });
  Logger.log(JSON.stringify(matches));
}
 

Электронная таблица1 Лист1:

Электронная таблица2 Лист0:

Соответствует Объекту:

 {
   "2":[
      {
         "val1":2,
         "row1":3,//ds1 row 3
         "col1":1,
         "row2":2,//ds2 row 4
         "col2":3
      },
      {
         "val1":2, 
         "row1":3,//ds1 row 3
         "col1":1,
         "row2":8,//ds2 row 8
         "col2":3
      },
      {
         "val1":2,
         "row1":10,
         "col1":1,
         "row2":2,
         "col2":3
      },
      {
         "val1":2,
         "row1":10,
         "col1":1,
         "row2":8,
         "col2":3
      }
   ],
   "7":[
      {
         "val1":7,
         "row1":6,
         "col1":1,
         "row2":3,
         "col2":3
      },
      {
         "val1":7,
         "row1":6,
         "col1":1,
         "row2":11,
         "col2":3
      }
   ],
   "8":[
      {
         "val1":8,
         "row1":7,
         "col1":1,
         "row2":4,
         "col2":3
      },
      {
         "val1":8,
         "row1":11,
         "col1":1,
         "row2":4,
         "col2":3
      }
   ],
   "9":[
      {
         "val1":9,
         "row1":5,
         "col1":1,
         "row2":7,
         "col2":3
      },
      {
         "val1":9,
         "row1":5,
         "col1":1,
         "row2":10,
         "col2":3
      }
   ],
   "pA":[//just an array of all of the matches
      2,
      9,
      7,
      8
   ]
}