Решил задачу с помощью скрипта предложенного 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);
}
}
}
}
