google script Как сравнить ячейки в двух столбцах и установить значение в другую ячейку

ProgramBox

google script Как сравнить ячейки в двух столбцах и установить значение в другую ячейку

Post author:admin
Запись опубликована:20 августа, 2022
Post category:Вопросы по программированию

#google-apps-script

Вопрос:

У меня есть уникальный идентификатор (‘ID1’) в столбце A в листе 1, и мне нужно сравнить его со столбцом B в листе 2, где указаны ID (‘ID2’).

Итак, мне нужно сравнить ячейки ID и проверить, совпадают ли некоторые из них, если они есть, я хочу изменить значение ячейки в той же строке, что и ‘ID1’ в sheet1, но в другом столбце, скажем, C.

 function IDValidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName("Sheet2");
  var Sheet1range = sheet1.getRange(2,1,sheet1.getLastRow()); //header is row 1
  var Sheet2range = sheet2.getRange(2, 2);    //header is row 1
  var Sheet1cellValue = Sheet1range.getValues();
  var Sheet2cellValue = Sheet2range.getValues();//Look here

  for(i = 0; i<Sheet1cellValue .length-1; i  ){
     if(Sheet1cellValue [i][0] == Sheet2cellValue)
     {
       ss.getSheetByName("Sheet1").getRange(i 2,3).setValue("Close");

}
}
}

1. Sheet2cellValue — это массив. Вы пробовали сравнивать с Sheet2cellValue[0][0]?

2. Это не сработало, даже если я расширил Sheet2range с помощью переменной Sheet2range = sheet2.getRange(2, 2,sheet2.getLastRow() . Он показывает только первое совпадение, а не все

3. var Sheet2range = sheet2.getRange(2, 2); получает только одну ячейку. При изменении его на var Sheet2range = sheet1.getRange(2,2,sheet2.getLastRow()); все строки, начинающиеся с 2,2, будут представлены в виде массива, поэтому вам придется искать в массиве. Простым методом будет второй цикл for внутри первого, зацикливающий каждый элемент в Sheet2cellValue и сравнивающий его с Sheet1cellValue[i][0] . ОБРАТИТЕ внимание, что при использовании .getLastRow() также будут получены все пустые строки, поэтому любые пустые строки будут совпадать.

4. Я знаю, что мой скрипт получает только ячейку. Я пробовал ваш способ, но он не сработал. Я думаю, это потому, что у листа 1 нет диапазона от листа 2, как вы написали. Я попробовал также var Sheet2range = sheet2.getRange(2, 2,sheet2.getLastRow(), но он получает только первое совпадение

Ответ №1:

Вот несколько вариантов.

Попробуйте это:

 function IDValidation() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName("Sheet2");
  var sh1rg=sh1.getRange(2,1,sh1.getLastRow()); //header is row 1
  var sh2rg=sh2.getRange(2,2);//header is row 1
  var sh1cvA=sh1rg.getValues();
  var sh2cV=sh2rg.getValue();//look here
  for(var i=0;i<sh1cvA.length-1; i  ){
    if(sh1cvA[i][0]==sh2cV){
      sh1.getRange(i 2,3).setValue("Close");
    }
  }
}

Лист1

Лист2

Похоже, это работает.

Я не знаю, возможно, вы ищете все совпадения между столбцом 1 листа и столбцом 2 листа. Вот что делает эта функция. Я использую случайные числа от 0 до 50, а не уникальные идентификаторы, поэтому совпадений много. Я перестал вводить close column3 и начал описывать, где находятся совпадения.

Вот код:

 function IDValidation1() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName("Sheet2");
  var sh1rg=sh1.getRange(2,1,sh1.getLastRow(),1); //header is row 1
  var sh2rg=sh2.getRange(2,2,sh2.getLastRow(),1);//header is row 1
  var sh1cvA=sh1rg.getValues();
  var sh2cvA=sh2rg.getValues();
  for(var i=0;i<sh1cvA.length-1; i  ){
    for(var j=0;j<sh2cvA.length;j  ) {
      if(sh1cvA[i][0]==sh2cvA[j][0]){
        sh1.getRange(i 2,3).setValue(Utilities.formatString('Sheet2 Row:%s == Sheet1 Row:%s = %s',j 2,i 2,sh1cvA[i][0]));
      }
    }
  }
}

Вот Лист1 после запуска:

И это Лист2: (Я выбрал слишком много строк и не смог разместить их все на своем экране)

1. Вы изменили только имя переменной. Это ничего не меняет в скрипте

2. Посмотрите на строку 8 как в вашей версии, так и в моей.

3. Вы также можете изменить это if(Sheet1cellValue [i][0] == Sheet2cellValue) на это if(Sheet1cellValue [i][0] == Sheet2cellValue{0][0])

4. Это тоже не сработало. Если в строке 8 значение Sheet2cellValue[0][0], я получаю сообщение: свойство «0» не может быть прочитано. Если это Значения Sheet2cellValue[0][0], он получает только совпадение с листа 2 B2, а не со всего столбца. Если я получу значения Sheet2cellValue[0][0] строка 6 —> var Sheet2range = sheet2.getRange(2, 2, sheet2.getLastRow()); он получит только первое совпадение, а не все.

5. Я не понимаю эту строку I have unique ID ('ID1') in column A in sheet1 and I need to compare it with column B in sheet2 where are ID ('ID2') to . Можете ли вы объяснить это лучше.

Ответ №2:

Я думаю, вы все очень хорошо поняли, потому что ваш код — это ответ на мою проблему 🙂 Я добавил только в строку ’10’ -1 с длиной, чтобы не считать последнюю строку в листе 2, которая пуста. И я, конечно, изменю setValue. Спасибо за все.

 function IDValidation1() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName("Sheet2");
  var sh1rg=sh1.getRange(2,1,sh1.getLastRow(),1); //header is row 1
  var sh2rg=sh2.getRange(2,2,sh2.getLastRow(),1);//header is row 1
  var sh1cvA=sh1rg.getValues();
  var sh2cvA=sh2rg.getValues();
  for(var i=0;i<sh1cvA.length-1; i  ){
    for(var j=0;j<sh2cvA.length-1;j  ) {
      if(sh1cvA[i][0]==sh2cvA[j][0]){
        sh1.getRange(i 2,3).setValue(Utilities.formatString('Sheet2 Row:%s == Sheet1 Row:%s = %s',j 2,i 2,sh1cvA[i][0]));
      }
    }
  }
}

Метки: google script Как сравнить ячейки в двух столбцах и установить значение в другую ячейку