Как выделить повторяющиеся строки в Google Таблицах?
coding google-apps-script

Как выделить повторяющиеся строки в Google Таблицах?

1 марта 2023 г.

Вы часто используете листы и иногда испытываете трудности с определением повторяющихся строк. Затем после этого блога вы получите скрипт, который будет выделять повторяющиеся строки в вашей таблице.

Намасте! Это Нибеш Хадка. ==Я фрилансер, разрабатываю скрипты для автоматизации приложений Google Workspace, таких как Gmail, Google Таблицы, Google Документы и т. д.==

Для тех, кто не занимается кодированием

Если вы не программист и не хотите утруждать себя объяснениями, выполните этот шаг:

Open Apps Script Editor

  1. Откройте редактор скриптов. В электронной таблице перейдите на вкладку расширений и откройте скрипт приложения, как показано на изображении выше.
  2. Удалите все коды в Code.gs. Затем скопируйте и вставьте код из раздела Полный код в этом блоге.
  3. Сохраните и перезагрузите таблицу. Теперь снова откройте скрипт приложений, следуя инструкциям на шаге 1 (==Иногда скрипт не работает без сохранения и перезагрузки==).

Полный код

Вот полный сценарий этого блога.

/**
 *The colorDuplicateRows() colors the duplicate rows.
 *Each unique rows and its duplicates are highlighted with different color
 */
function colorDuplicateRows() {
  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();

  // find lastCol
  let lastCol = Math.max(...data.map((arr) => arr.length));

  // convert nested arrays to string
  data = data.map((arr) => arr.join(""));
  // find unique ones among arrays
  let uniqueRows = [...new Set(data)].filter(String);
  // create unique color for each unique arr
  let uniqueColor = {};
  uniqueRows.forEach((val) => (uniqueColor[val] = getRandomUniqueColor()));

  // console.log(uniqueColor);
  // find duplicate row for each item
  let duplicateRows = data
    .map((x, i) => (isDuplicateRow(x, data) ? i + 1 : ""))
    .filter(String);
  // console.log(duplicateRows);

  // now reset color before highlighting duplicate rows
  colorReset();

  duplicateRows.forEach((rowNum) => {
    for (let i = 0; i < uniqueRows.length; i++) {
      //console.log(range.getValues())
      // compare each item with uniqe items and assing color accordingly
      if (
        sheet.getRange(rowNum, 1, 1, lastCol).getValues().flat().join("") ===
        uniqueRows[i]
      ) {
        sheet
          .getRange(rowNum, 1, 1, lastCol)
          .setBackground(uniqueColor[uniqueRows[i]]);
      }
    }
  });
}

/**
 * Function takes two items: row and arr.
 * The parameter "row" is a string to be compared to items in array "arr".
 * Inspired from https://stackoverflow.com/a/68424642/6163929
 * @param {String} row
 * @param {Array<String>} arr
 * @returns {Boolean}
 */
function isDuplicateRow(row, arr) {
  return row === "" ? false : arr.indexOf(row) != arr.lastIndexOf(row);
}

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Highlight Duplicate Rows"); 

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  menu.addItem("Reset Colors", "colorReset");
  menu.addToUi();
}

/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * ColorReset is used to reset bg color of spreadsheet to its original color.
 */
function colorReset() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground("");
}

/**
 * Function creates a unique random color as hashcode.
 * @returns {String}
 */
function getRandomUniqueColor() {
  // thanks to function https://dev.to/rajnishkatharotiya/generate-unique-color-code-in-javascript-3h06
  let n = (Math.random() * 0xfffff * 1000000).toString(16);
  return "#" + n.slice(0, 6);
}

/**
 * Code By Nibesh Khadka.
 * I am freelance and Google Workspace Automation Expert.
 * You can find me in:
 * https://linkedin.com/in/nibesh-khadka
 * https://nibeshkhadka.com
 * me@nibeshkhadka.com
 */

Является ли строка дубликатом?

Чтобы определить, является ли строка дубликатом, мы используем функцию isDuplicateRow() в нашем скрипте.

function isDuplicateRow(x, arr) {
  return x === "" ? false : arr.indexOf(x) != arr.lastIndexOf(x);
}

Эта функция принимает два параметра: строку и массив. Строка — это строка, преобразованная в строку, а массив — это список строк, каждая из которых преобразована в строку.

Сбросить цвет и случайный уникальный цвет

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

  1. Во-первых, сбросить предыдущие выделения (если они существуют) с помощью функции colorReset().
  2. Затем ==раскрасьте их цветами, уникальными для каждого набора повторяющихся строк==, с помощью функции getRandomUniqueColor().

function colorReset() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground("");
}


function getRandomUniqueColor() {
  // thanks to function https://dev.to/rajnishkatharotiya/generate-unique-color-code-in-javascript-3h06
  let n = (Math.random() * 0xfffff * 1000000).toString(16);
  return "#" + n.slice(0, 6);
}

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

:::

Раскрашиваем повторяющиеся строки

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

function colorDuplicateRows() {
  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();

  // find lastCol
  let lastCol = Math.max(...data.map((arr) => arr.length));

  // convert nested arrays to string
  data = data.map((arr) => arr.join(""));
  // find unique ones among arrays
  let uniqueRows = [...new Set(data)].filter(String);
  // create unique color for each unique arr
  let uniqueColor = {};
  uniqueRows.forEach((val) => (uniqueColor[val] = getRandomUniqueColor()));

  // console.log(uniqueColor);
  // find duplicate row for each item
  let duplicateRows = data
    .map((x, i) => (isDuplicateRow(x, data) ? i + 1 : ""))
    .filter(String);
  // console.log(duplicateRows);

  // now reset color before highlighting duplicate rows
  colorReset();

  duplicateRows.forEach((rowNum) => {
    for (let i = 0; i < uniqueRows.length; i++) {
      //console.log(range.getValues())
      // compare each item with uniqe items and assing color accordingly
      if (
        sheet.getRange(rowNum, 1, 1, lastCol).getValues().flat().join("") ===
        uniqueRows[i]
      ) {
        sheet
          .getRange(rowNum, 1, 1, lastCol)
          .setBackground(uniqueColor[uniqueRows[i]]);
      }
    }
  });
}

Да.. Хорошо, но где меню?

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

Итак, давайте создадим наше пользовательское меню с помощью функции createCustomMenu().

function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Highlight Duplicate Rows");

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  menu.addItem("Reset Colors", "colorReset");
  menu.addToUi();
}

Затем отобразите пользовательское меню в электронной таблице с помощью OnOpen(). триггер.

function onOpen(e) {
  createCustomMenu();
}

Сохраните код и перезагрузите таблицу, после чего вы сможете увидеть меню с заголовком «==Выделить повторяющиеся строки==»

Custom Menu

Спасибо за уделенное время

==Я создаю надстройки Google, а также могу написать для вас сценарии Google Apps. Если вам нужны мои услуги, дайте мне знать .==

Follow Me

п

Оригинал

Recent Post

Categories

PREVIOUS ARTICLE
NEXT ARTICLE