Коран по поискам дублей в Google Spreadsheet

в 6:26, , рубрики: Google, Google Docs, Google Web Toolkit, xls, Веб-разработка, метки: ,

Доброго времени суток, дорогие читатели.

Помните ли вы овцу Долли? При работе с документами google, особенно со spreadsheet (MS Excel) таблицами для решения многих нетипичных задач, таких как: специальное форматирование по множеству условий или удаление дубликатов, необходимо было научится пользоваться JavaScript, так как не все можно исполнить с помощью стандартных функций, а по формулам есть ограничения, которые раскроем в следующем выпуске. Теперь же представляем ликбез по поиску дублей ссылок и текста в Google Spreadsheet.

image

Поэтому начнем писать скрипты для Google Spreadsheet. Если вы знаете несколько языков программирования хотя бы поверхностно, вам, конечно, будет легче. На JavaScript я начал писать без проблем, но работал в основном с VBA, Visual basic, C/C++, C#. Как говорится, всего понемногу и везде по чуть-чуть. Ежели знания языков программирования хромают, то добро пожаловать на наш ликбез.

Введение

Для того чтоб вам был понятен пример, мы будем изучать скрипт на примере удаления дубликатов в таблицах документа google spreadsheet. Писать мы будем с нуля, поэтому приступаем.

Поставим задачу
Поставим задачу — найти в текстовом списке дубликаты и удалить их, создав резервную копию списка.
Создадим новый документ и назовем его “Поиски дублей”, страницу назовем так же. Искать дубликаты будем по 1-й колонке. Для того, чтобы было удобнее, пропишем название столбцов и закрепим их. Для того, чтобы закрепить строку или столбец, нужно потянуть за соответствующую жирную линию, при этом она переместится на 1 строку вниз или на 1 столбец вправо (Рисунок 1).
Коран по поискам дублей в Google Spreadsheet
Рисунок 1. Закрепление столбцов и строк.

Так как обычно нам нужно использовать несколько столбцов, а в силу привычки мы обычно удаляем все лишние, мы добавим их заново (столбцы B:D). Если у вас новый лист, то соответственно там столбцы есть и добавлять их не нужно, а удалить все, что после, будет только приветствоваться. Так как чистота залог порядка.

Создадим еще 2 листа “Рабочая страница” на которой мы будем проводить манипуляции с данными. “Копия” — лист для копии начальных данных, в случае неправильной работы скрипта или потери данных (Рисунок 2).

Коран по поискам дублей в Google Spreadsheet
Рисунок 2. Создание 2-х листов.

Приступаем к написанию кода. Нажимаем в верхнем ниспадающем меню Инструменты -> Редактор скриптов (Рисунок 3).
Коран по поискам дублей в Google Spreadsheet
Рисунок 3. Открытие редактора скриптов в Google Spreadsheet.

Перед нами в новом окне откроется редактор скриптов с вариантами создания скриптов
(Рисунок 4)
Коран по поискам дублей в Google Spreadsheet
Рисунок 4. Варианты создания скриптов в Редакторе скриптов.

Если вы выберете любой из пунктов слева (создание соответствующего скрипта для Диска, Почты и т.д.), то увидите вступительный скрипт с подробными объяснениями каким образом работать с тем или иным скриптом, Но это только показательные скрипты. Но мы выберем “Пустой проект” и увидим только начало всех скриптов

function myFunction(){}

.

Стандартное название проекта — это “Проект без названия”, я же поменял название кликнув на эти слова (верхняя строка на рисунке) и назвал его “Поиски дубликатов” (Рисунок 5). Это для того, чтобы потом при наличии 10 и более скриптов, можно было их различать и без особых усилий находить нужный нам скрипт.
Коран по поискам дублей в Google Spreadsheet
Рисунок 5. “Пустой проект” с первыми стандартными строками кода и измененным названием

В фигурных скобках и будем писать наш скрипт.

Углубляться в изучение JavaScript мы особо не будем, но чтобы понял даже новичок, по ходу написания кода, я буду подробно комментировать происходящее.
Пишем алгоритм
Перед тем, как приступить к написанию любого автоматизирующего скрипта (задачи), нам нужно нарисовать для себя алгоритм действий. И пусть многим это может показаться бредом и тратой лишнего времени и, возможно, вы посчитаете данные действия лишними, но это очень важный этап работы. При работе с большими проектами это очень важно, так как запутаться можно и в 3-х соснах — алгоритм решает данную проблему. Это касается не только JavaScript, это касается абсолютно любого языка, да и вообще любых действий. Для понимания приведем пример на основе получения чашки чая (Рисунок 6).

Коран по поискам дублей в Google Spreadsheet
Рисунок 6. Алгоритм действий получения чашки чая (пример).

Надеюсь, вы заварили чашечку :) и с хорошим настроением читаете дальше.

Нарисуем алгоритм для нашего скрипта по поиску дубликатов (Рисунок 7), он будет более подробный чем пример с чашкой чая.
Коран по поискам дублей в Google Spreadsheet
Рисунок 7. Алгоритм для задачи по поиску дубликатов

Внесу небольшое пояснение, это уже окончательный вид алгоритма, поэтому в нем присутствуют 2 блока которые, возможно не сразу будут понятны для обычного пользователя.
Блок 2 — касается удаления контента и комментариев со страницы, на странице, которая, только что создана и совсем ничего не содержит. Этот блок нужен при повторном использовании скрипта, поэтому мы сразу закладываем данную функцию в скрипт. Об этом более подробно будет написано ниже.
Блок 5 — создание массива и конвертация, об этом будет рассказано подробно ниже. Сейчас же скажу, что созданный массив будет основным элементом обработки данных.
Кодин
Объявим переменные для наших листов, чтобы программа знала, как к ним обращаться.

Для объявления переменной в текущей области видимости используется ключевое слово var. Причем, особенностью языка javascript, как впрочем, и многих других, таких как php, является то, что не нужно специально объявлять тип переменной (как например, в C#: число объявляется integer; строка — string и т.д.).
Для того, чтобы описывать строки кода внутри самого кода, мы будем использовать комментарий в виде текста, который начинается с двух прямых слешей “//”. В разных языках программирования теги комментариев выглядят по разному и представляют из себя последовательность некоторых символов, вот несколько из них: (см. Таблицу 1.)

Коран по поискам дублей в Google Spreadsheet
Таблица 1. Сравнительная таблица тегов комментариев в некоторых языках

При написании программ в редакторах языков, либо при применении правильного синтаксиса, например, в программе notepad++, текст программы будет подсвечиваться соответствующими цветами, которые прописаны для этого языка по умолчанию. Цвет текста комментариев, например, будет зеленый для большинства языков по умолчанию, но на самом деле, в программах для кодинга обычно присутствует возможность настраивать уветовую гамму, хотя и не везде. В google JS цвет комментария будет коричневый.
Коран по поискам дублей в Google Spreadsheet
Рисунок 8. Иллюстрация подсветки синтаксиса кода.

Объявим три переменные для наших листов:
переменная sheet_work_page для страницы с названием “Рабочая страница”, сюда мы будем помещать текст для поиска и после работы получать результат.
переменная sheet_find_dubles для страницы с названием “Поиск дубликатов”, на этой странице будет проходить обработка дубликатов.
переменная sheet_copy для страницы с названием “Копия”, создается копию данных перед началом обработки, так как никогда не знаешь, когда понадобится оригинал.


    var sheet_work_page = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Рабочая страница');
    var sheet_find_dubles = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Поиск дубликатов');
    var sheet_copy = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Копия');
</sourse>

А также переменные для работы. 
    var i,Page_Last_Row,k,archurls;
переменные i, k - счетчики циклов, так как имена не несут большую смысловую нагрузку, не будем им присваивать имена покрасивее.
переменная Page_Last_Row - последняя строка листа, для которого, эта переменная будет объявлена.
переменная archurls - массив для хранения и перебора значений.
   
Для каждой новой обработки нам необходимо удалять контент и комментарии со страниц “Рабочая страница” и “Копия”, так как наш скрипт будет оперировать с комментариями. Это операция предотвращает попадание в конечный результат данных, которые могли остатся от предыдущих использований скрипта. Например: Вы, сначала работали с 1000 строк, а потом перешли на 20 строк, не удалив предыдущий результат, Вы в итоге получите 1000 строк, а не 20. Эту операцию на страницах можно выполнить так: удалить содержимое, нажав клавишу delete, а комментарии через контекстное меню соответствующей командой. При простом удалении, например, при нажатии клавиши delete,  комментарии остаются. 
Также можно воспользоватся клавиатурой:
Встать на вторую строку, нажать клавиши Shift + Space(пробел) так мы выделим всю строку, далее Shift+Ctrl+Arrow_Down (стрелка вниз) (выделить все до конца листа) и через контекстное меню "Удалить строки". Получаем девственно чистый лист с шапкой.
<img src="http://habrastorage.org/storage3/c53/628/51b/c5362851ba5f57dbdaa809c5f0d75dc7.jpg"/>
Рисунок 9 Правильно удаляем примечания.

Для удаления Нам необходимо знать границы диапазона где производится операция чистки. Это нужно для того чтобы, например, оставить шапку в документе нетронутой, либо если нам необходимо затронуть лишь часть данных, а также это необходимость формулировки языков программирования - знать рабочий диапазон.
Для нашего диапазона за основу берем  диапазон ячеек “A2:D номер последней строки” 
Номер последней строки - количество строк листа “Поиски дубликатов”.
Диапазон начинается с ячейки A2 так как первая строка или ячейка A1 находится в закреплении для обозначения шапки листа. А работать с закрепленной областью скритп не умеет. Также вид A2:Dномер последней строки можно записать как (2,1,номер последней строки,4) от этого ничего не поменяется, подробнее об этом Вы можете прочитать здесь.
<a href=”http://habrahabr.ru/post/157933/”>на русском языке</a>
<a href=”http://webhostingw.com/google-spreadsheet-formulas/”>на английском языке</a>
Напишем код для всего, что было сказано выше об удалении примечаний и содержимого, это всего лишь 4 строки.

<source lang=”javascript”>
    sheet_work_page.getRange("A2:D" + sheet_find_dubles.getLastRow()).clearContent();
    sheet_work_page.getRange("A2:D" + sheet_find_dubles.getLastRow()).clearComment();
    sheet_copy.getRange("A2:D" + sheet_find_dubles.getLastRow()).clearContent();
    sheet_copy.getRange("A2:D" + sheet_find_dubles.getLastRow()).clearComment();

Опишем немного подробнее:
sheet_work_page — символьное имя, которое мы присвоили листу “Рабочая страница”.
getRange(первая строка, первый столбец, последняя строка, последний столбец) — указание диапазона для совершения дальнейших действий.
clearContent() — удаление содержимого в ячейках, указанных в диапазоне.
clearComment() — удаление примечаний в ячейках, указанных в диапазоне.

Создаем переменную datatocopy и выделяем диапазон ячеек с данными A2:D последняя строка в листе “Поиск дубликатов” и копируем на страницы “Рабочая страница” и “Копия”.
Особенность данной операции в том, что вставить скопированый диапазон можно только при выделении соответствующего диапазона.


var datatocopy = sheet_find_dubles.getRange("A2:D" + sheet_find_dubles.getLastRow()).getValues();
    sheet_work_page.getRange(2,1,sheet_find_dubles.getLastRow()-1,4).setValues(datatocopy);
    var datatocopy1 = sheet_find_dubles.getRange("A2:D" + sheet_find_dubles.getLastRow()).getValues();
    sheet_copy.getRange(2,1,sheet_find_dubles.getLastRow()-1,4).setValues(datatocopy1);

переменной Page_Last_Row присваиваем значение последней строки страницы “Поиск дубликатов”


Page_Last_Row = sheet_find_dubles.getLastRow();

В объявленный ранее массив archurls присваиваем все данные из первой колонки листа “Поиск дубликатов”.


archurls = sheet_find_dubles.getRange(2, 1, Page_Last_Row-1, 1).getValues();

и конвертируем 2-х мерный массив в одномерный. Это нужно для того, чтобы переконвертировать все записи в столбце в символы, так как поиск у нас происходит посимвольно.


 for (i=0; i<archurls.length; i++) //convert 2d array into 1d
    {
        archurls[i] = archurls[i][0];
    }

Чтобы лучше понять механизм действий и составить правильное представление, рассмотрим скриншот “Как выглядят данные в массиве archurls” рисунок 8
Коран по поискам дублей в Google Spreadsheet
Рисунок 8 Как выглядит массив со значениями изнутри.

Переменной i, которая в данном случаем будет использоваться для порядкового номера проверяемой строки листа “Рабочая страница”, присваиваем значение 2-й строки, так как 1-я у нас является названиями столбцов, i = 2;

И переходим к написанию главного цикла по поиску дубликатов. Объявляем цикл while (цикл, условие которого проверяется перед началом операций всего цикла) и в нем перебираем построчно значения из листа “Рабочая страница”, сравнивая со значениями листа “Поиск дубликатов”.


    while (i <= sheet_work_page.getLastRow())
    { 

        k = archurls.indexOf(sheet_work_page.getRange(i, 1).getValue());
        if (k >= 0)
        {
            if ((k+2)==i)
            {
                sheet_work_page.getRange(i, 4).setComment('Найдено совпадение со строкой ' + (k+1) + ' архива');
                i++;
                continue;
            }

            else
            {
                sheet_work_page.getRange("A" + i + ":C" + i).clearContent();
                i++;
                delete_count++;
                continue;
            }
        }

    }

В итоге, на рабочей странице получаем список без 100% дубликатов.
Учтите, что если ссылка А содержит кириллицу но визуально она похожа на ссылку B, написанную латиницей, то эти ссылки будут разными.

Сортируем, если есть такая необходимость.
Описание: Лист.sort(номер колонки, true/false) true — от А до Я, false — от Я до А


sheet_work_page.sort(3, true);

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


function onOpen() 
{
    SpreadsheetApp.getActiveSpreadsheet().addMenu("Поиск дубликатов?", [{name: "Удалить дубликаты!", functionName: "check_duplicates_one_sheet"}]);
}

Добавляем меню с названием “Копаем?” указываем название подменю “Копаем!” и присваиваем кнопке выполнение функции той, что мы написали выше:check_duplicates_one_sheet (рисунок 9)

Коран по поискам дублей в Google Spreadsheet
Рисунок 9

Можете зайти в наш документ описанный в этой статье.
Дополнение:
“А если необходимо просто удалить дубликаты из колонки А, быстренько и без глупостей?”, — спросите вы.
Что ж напишем более скоростной скрипт.


function removeDuplicates() {
//объявим наш листик.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ускоренная чистка дублей для одной колонки");
//получаем массив данных из нашего листа. 
  var data = sheet.getDataRange().getValues();
//объявим новый массив для результатов.
  var newdata = new Array();
//проверяем на дубли
  for(i in data){
    var row = data[i];
    var duplicate = false;
    for(j in newdata){
      if(row[0] == newdata[j][0]){
        duplicate = true;
      }
    }
//обновляем строку
    if(!duplicate){
      newdata.push(row);
    }
  }
//очищаем содержимое ячеек с исходного листа
  sheet.clearContents();
//вставляем в лист новый отчищенный массив
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
//сортируем по возрастанию
  sheet.sort(1, true);
}

В итоге, у нас быстродействующий скрипт по удалению дубликатов.
Проверенно на 400 000 записях.

До новых встреч! Ваш покорный слуга.

Также можете посетить наши предыдущие статьи:
Талмуд по формулам в Google SpreadSheet
Рассылка писем в Google Docs (Drive)
ждите наши следующие выпуски.

Автор: gasyoun

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js