Как прописать условное форматирование для автоматического выделения ячеек в Google таблице в которых есть повторяющиеся элементы?

Question

[Ринат]

Мне скидывают информацию одним предложением: "1234 5678 9101 2345 Сафонова Ольга Ивановна вернуть разницу 200 руб.(обмен) 213202", такую информацию я заполняю в гугл таблицу для отчётности, 1 предложение - 1 ячейка; "1234 5678 9101 2345" - номер карты; ФИО; "213202" - номер заказа; не всегда предложение однотипное, бывает что ФИО в начале предложения, номер заказа в конце или наоборот;
Затем я соответственно оплачиваю, фиксирую чеки, но вот проблема, бывает иногда мне скидывают информацию повторно по тем заказам которые я уже оплатил. Неприятная ситуация, особенно когда переводов бывает 20, за месяц 300, всех кому переводил точно не запомнишь, а надеяться что другие перестанут косячить не хочется : )
Вот как это выглядит:

Мне хочется, что бы при заполнении новых данных в ячейку, проверялось был ли похожий заказ; возможно ли как-нибудь осуществить через условное форматирование, что бы подкрашивалось? Пробовал что-нибудь придумать с формулой SPLIT и ПОИСК, но не осилил.
Возможно стоит применить гугл скрипты.

Answer 1

[Ринат]

Решил задачу с помощью скрипта предложенного Chat GPT. Есть данные в диапазоне d2:d, скрипт в начале выдёргивает 6-тизначные номера заказов из столбца D и переносит их в соответствующие строки столбца K, затем проверяет ячейки столбца K на дубликаты и подкрашивает красным цветом строки A:K последних найденных дубликатов. В ходе решения появлялись проблемы, что если данные в столбце D изменялись, старая раскраска не очищалась, переписал в скрипт очищение цвета перед новым раскрашиванием. Скрипт cрабатывает автоматически при ручном редактировании ячейки столбца D и проходится по всем ячейкам d2:d; k2:k.

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var column = range.getColumn();
  
  // Убедимся, что изменение произошло в столбце D
  if (column == 4) {
    var lastRow = sheet.getLastRow();
    var dataRange = sheet.getRange(2, 4, lastRow - 1, 1); // Исключаем заголовок
    var dataValues = dataRange.getValues();
    
    var orderNumbers = [];

    for (var i = 0; i < dataValues.length; i++) {
      var sentence = dataValues[i][0];
      var orderNumber = extractOrderNumber(sentence);
      orderNumbers.push([orderNumber]);
    }

    var orderColumn = sheet.getRange(2, 11, lastRow - 1, 1);
    
    // Убираем только красный фон перед записью новых значений
    var backgrounds = orderColumn.getBackgrounds();
    for (var i = 0; i < backgrounds.length; i++) {
      backgrounds[i][0] = ""; // Очищаем фон
    }
    orderColumn.setBackgrounds(backgrounds);
    
    // Записываем номера заказов
    orderColumn.setValues(orderNumbers);
    
    // Убираем красный фон для столбцов A:K
    var rangeAtoK = sheet.getRange(2, 1, lastRow - 1, 11);
    var rangeBackgrounds = rangeAtoK.getBackgrounds();
    for (var i = 0; i < rangeBackgrounds.length; i++) {
      for (var j = 0; j < rangeBackgrounds[i].length; j++) {
        rangeBackgrounds[i][j] = ""; // Очищаем фон
      }
    }
    rangeAtoK.setBackgrounds(rangeBackgrounds);
    
    // Проверяем на дубликаты и подсвечиваем строки
    checkForDuplicates(sheet);
  }
}

function extractOrderNumber(sentence) {
  var match = sentence.match(/\b\d{6}\b/);
  return match ? match[0] : "";
}

function checkForDuplicates(sheet) {
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var duplicates = {};

  for (var i = 1; i < data.length; i++) {
    var orderNumber = data[i][10];
    
    if (orderNumber && orderNumber !== "") { // Проверяем, что значение не пустое
      var key = orderNumber;

      if (duplicates[key]) {
        sheet.getRange(i + 1, 1, 1, 11).setBackground("red"); // Подсветка строки красным
      } else {
        duplicates[key] = true;
      }
    }
  }
}

Минус кода в том что он пробегается и обновляет номера заказов по всем ячейкам в диапазонах d2:d; k2:k соответственно; Под себя подкорректировал, что бы перенос данных в столбец K происходил только по той строке в которой было изменение ячейки столбца D; А полное прохождение по всему диапазону d2:d; k2:k реализовал посредством отдельного вызова опции в меню; Это сделано что бы не нагружать табличку. Вот обновлённый код, если кому пригодится:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Run Script', 'runScript')
      .addToUi();
}

function runScript() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange(2, 4, lastRow - 1, 1);
  var dataValues = dataRange.getValues();
  
  var orderNumbers = [];

  for (var i = 0; i < dataValues.length; i++) {
    var sentence = dataValues[i][0];
    var orderNumber = extractOrderNumber(sentence);
    orderNumbers.push([orderNumber]);
  }

  var orderColumn = sheet.getRange(2, 11, lastRow - 1, 1);
  
  var backgrounds = orderColumn.getBackgrounds();
  for (var i = 0; i < backgrounds.length; i++) {
    backgrounds[i][0] = "";
  }
  orderColumn.setBackgrounds(backgrounds);
  
  orderColumn.setValues(orderNumbers);
  
  var rangeAtoK = sheet.getRange(2, 1, lastRow - 1, 11);
  var rangeBackgrounds = rangeAtoK.getBackgrounds();
  for (var i = 0; i < rangeBackgrounds.length; i++) {
    for (var j = 0; j < rangeBackgrounds[i].length; j++) {
      rangeBackgrounds[i][j] = "";
    }
  }
  rangeAtoK.setBackgrounds(rangeBackgrounds);
  
  checkForDuplicates(sheet);
}

function extractOrderNumber(sentence) {
  var match = sentence.match(/\b\d{6}\b/);
  return match ? match[0] : "";
}

function checkForDuplicates(sheet) {
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var duplicates = {};

  for (var i = 1; i < data.length; i++) {
    var orderNumber = data[i][10];
    
    if (orderNumber && orderNumber !== "") {
      var key = orderNumber;

      if (duplicates[key]) {
        sheet.getRange(i + 1, 1, 1, 11).setBackground("red");
      } else {
        duplicates[key] = true;
      }
    }
  }
}

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var sheetId = "Идентификатор вашей таблицы"; // Идентификатор вашей таблицы
  if (sheet.getParent().getId() !== sheetId) return; // Применяем скрипт только для указанной таблицы

  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();
  
  if (column == 4) {
    var dataRange = sheet.getRange(row, 4, 1, 1);
    var dataValue = dataRange.getValue();
    
    if (!dataValue || dataValue === "") {
      sheet.getRange(row, 11).setValue("");
      sheet.getRange(row, 1, 1, 11).setBackground("");
      checkForDuplicates(sheet);
    } else {
      var orderNumber = extractOrderNumber(dataValue);
      
      if (orderNumber && orderNumber !== "") {
        var orderColumn = sheet.getRange(row, 11);
        orderColumn.setValue(orderNumber);
        
        sheet.getRange(row, 1, 1, 11).setBackground("");
        checkForDuplicates(sheet);
      }
    }
  }
}

Comments

[Dimonchik]

шик!

Answer 2

[CBET_TbMbI]

Ответ на вопрос: без макроса никак. И даже с макросом идеально работать не будет. Макрос не отличит имена от слов "вернуть, номер, телефона" и т.п.

Совет: разбейте тупой столбец "комментарий" на десяток нормальных: имя, отчество, фамилия, номер, сумма, способ, причина и что там ещё вам надо. С ними дальше и работайте. Комметарии тоже оставьте, но только для чего-то на самом деле уникального, не подходящего под другие столбцы.

В идеале информацию раскладывать по прлочкам там, где она рождается. Но если это трудно, то и на своём этапа это облегчит работу.

Comments

[Aetae]

Ну на самом деле макрос сможет отличать ровно то, чему вы его научите.
Те же имена и определённые цифры вытянуть не проблема с минимальными погрешностями. А потом можно и перелинковать их перекрёстными ссылками и на отдельную страничку по столбцам разложить. (но я этого делать, конечно же, не буду)

Однако да, гораздо лучше с самого начала раскладывать всё по столбцам нормально.