Возвращает массив (отредактированных) совпадающих элементов двух других массивов и (неотредактированных) несовпадающих элементов

Моя цель

Я пытаюсь связать строки на двух разных листах в одной электронной таблице в соответствии со значением в ячейке. Основная идея состоит в том, что если значение в столбце B в Sheet1 имеет совпадающее значение в столбце B в Sheet2, гиперссылка должна быть добавлена ​​в ячейку с согласованным значением в Sheet1, связываясь со всей строкой согласованного значения в Sheet2.

Что я сделал

Как вы можете видеть из приведенного ниже кода, он ищет совпадения, если он найден, он редактирует совпадающие значения, чтобы добавить гиперссылки. Я не только хочу вставить «связанные» согласованные значения в новый массив, мне также нужны несоответствующие значения без ссылки. Идея состоит в том, что ссылка будет добавлена, если совпадение будет найдено, в противном случае значение все равно будет добавлено, только без ссылки.

function linkToContacts(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var Sheet1 = ss.getSheetByName("Sheet1");
    var Sheet2 = ss.getSheetByName("Sheet2");
    var Sheet2ID = Sheet2.getSheetId();

    var arrSheet1 = Sheet1.getRange(4,2,Sheet1.getLastRow()-3).getValues(); // -3 because I have 3 row as headers before the data, which start in row 4 col B
    var arrSheet2 = Sheet2.getRange(4,2,Sheet2.getLastRow()-3).getValues(); // -3 because I have 3 row as headers before the data, which start in row 4 col B

    var arrOutput = [];

    for(var i = 0; i<arrSheet1.length;i++){
      for(var j = 0; j<arrSheet2.length;j++) {
        if(arrSheet1[i][0] === arrSheet2[j][0]){
          arrOutput.push(['=HYPERLINK("#gid=' + Sheet2ID + '&range=' + Sheet2.getRange(j+4,2,1,Sheet2.getLastColumn()-1).getA1Notation() + '";"' + arrSheet1[i][0] + '")']);
        } else {
          arrOutput.push([arrSheet1[i][0]]);
        }
      }
    }

    Sheet1.getRange(4,2,Sheet1.getLastRow()).clearContent();
    Sheet1.getRange(4,2,arrOutput.length).setValues(arrOutput);
  }

Эта проблема

Два массива содержат только уникальные значения соответственно. Проблема в том, что из-за двойного цикла каждый элемент проверяется длиной arrSheet2. Так, например, представим себе такой сценарий:

var arrSheet1 = [apple,avocado,banana];
var arrSheet2 = [apple,banana,mango,];

arrOutput приведет к:

arrOutput = [
          apple(link),apple,apple,
          avocado,avocado,avocado,
          banana(link),banana,banana
];

В быстром, вероятно, неэлегантном способе решения проблемы я попытался удалить дубликаты frm arrOutput, но, очевидно, значение со ссылкой и значения без него отличаются, поэтому лучшее, что он может получить с этим решением, таково:

arrOutput = [
          apple(link),apple,
          avocado,
          banana(link),banana
];

Вопрос

Есть ли более умный / эффективный способ добраться до

arrOutput = [apple(link),avocado,banana(link)];

или, если то, что я делаю, действительно имеет смысл, что мне делать, чтобы получить результат выше?


google-sheets javascript google-apps-script
person res_cogitans    schedule 26.07.2019    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Проблема:

  • Двойной цикл и многократное нажатие элементов массива

Решение:

  • Используйте break и условные if операторы для управления логикой

Поток:

  • Если гиперссылка нажата, разорвать второй цикл листа
  • Нажимайте элемент Sheet1 только в том случае, если в Sheet2 нет гиперссылок (т. Е. Подождите, пока не будет повторен последний элемент Sheet2)

Фрагмент:

for(var i = 0; i<arrSheet1.length;i++){
  for(var j = 0, k = arrSheet2.length-1; j<=k; j++) {// modified
    if(arrSheet1[i][0] === arrSheet2[j][0]){
      arrOutput.push(['=HYPERLINK("#gid=' + Sheet2ID + '&range=' + Sheet2.getRange(j+4,2,1,Sheet2.getLastColumn()-1).getA1Notation() + '";"' + arrSheet1[i][0] + '")']);
      break; //break j loop to continue next i loop
    } else if(j === k){//push sheet1 only on the last element
      arrOutput.push([arrSheet1[i][0]]);
    }
  }
}

Примечание:

  • Возможно, лучше использовать объекты {}. Преобразуйте массив sheet2 в объект {val1:hash,val2:hash,...}. Затем вы можете легко проверить, присутствуют ли элементы Sheet1 в Sheet2, используя in
person TheMaster    schedule 27.07.2019

arrow_upward
1
arrow_downward

Для создания новый массив, содержащий только совпадающие элементы. Если элементы не найдены, будет возвращен пустой массив.

var arrSheet1 = ['apple','avocado','banana'];
var arrSheet2 = ['apple','banana','mango'];

var intersect = arrSheet1.filter(function (element) {
  return arrSheet2.includes(element);
});

// ['apple','banana']

Или без полифилла Array.prototype.includes:

var intersect = arrSheet1.filter(function (element) {
  return arrSheet2.indexOf(element) !== -1);
})
person Cue    schedule 26.07.2019
comment
Стрелочные функции пока не поддерживаются в скрипте приложений - person TheMaster; 27.07.2019
comment
Спасибо @TheMaster, обновлено, чтобы соответствовать вашему комментарию. - person Cue; 27.07.2019
comment
Спасибо за ответ @Cue, но, к сожалению, это не сработает, поскольку результат, который я ищу, не является массивом только совпадающих элементов. Как я уже упоминал, логика должна быть следующей: если элемент соответствует, то к нему добавляется ссылка (= ГИПЕРССЫЛКА (элементы)), и она помещается в arrOutput, если совпадения нет, то элемент ТАКЖЕ помещается в массив но без преобразования самих элементов с добавлением ссылки. Следовательно, результирующий массив, к которому я стремлюсь, должен быть ['= ГИПЕРССЫЛКА (яблоко)', авокадо, '= ГИПЕРССЫЛКА (банан)']. Надеюсь, теперь стало понятнее! Спасибо - person res_cogitans; 27.07.2019