Помните ли вы овцу Долли? При работе с документами google, особенно со spreadsheet (MS Excel) таблицами для решения многих нетипичных задач, таких как: специальное форматирование по множеству условий или удаление дубликатов, необходимо было научится пользоваться JavaScript, так как не все можно исполнить с помощью стандартных функций, а по формулам есть ограничения, которые раскроем в следующем выпуске. Теперь же представляем ликбез по поиску дублей ссылок и текста в Google Spreadsheet.
Поэтому начнем писать скрипты для Google Spreadsheet. Если вы знаете несколько языков программирования хотя бы поверхностно, вам, конечно, будет легче. На JavaScript я начал писать без проблем, но работал в основном с VBA, Visual basic, C/C++, C#. Как говорится, всего понемногу и везде по чуть-чуть. Ежели знания языков программирования хромают, то добро пожаловать на наш ликбез.
Введение
Для того чтоб вам был понятен пример, мы будем изучать скрипт на примере удаления дубликатов в таблицах документа google spreadsheet. Писать мы будем с нуля, поэтому приступаем.
Поставим задачу
Поставим задачу — найти в текстовом списке дубликаты и удалить их, создав резервную копию списка.
Создадим новый документ и назовем его “Поиски дублей”, страницу назовем так же. Искать дубликаты будем по 1-й колонке. Для того, чтобы было удобнее, пропишем название столбцов и закрепим их. Для того, чтобы закрепить строку или столбец, нужно потянуть за соответствующую жирную линию, при этом она переместится на 1 строку вниз или на 1 столбец вправо (Рисунок 1).
Рисунок 1. Закрепление столбцов и строк.
Так как обычно нам нужно использовать несколько столбцов, а в силу привычки мы обычно удаляем все лишние, мы добавим их заново (столбцы B:D). Если у вас новый лист, то соответственно там столбцы есть и добавлять их не нужно, а удалить все, что после, будет только приветствоваться. Так как чистота залог порядка.
Создадим еще 2 листа “Рабочая страница” на которой мы будем проводить манипуляции с данными. “Копия” — лист для копии начальных данных, в случае неправильной работы скрипта или потери данных (Рисунок 2).
Рисунок 2. Создание 2-х листов.
Приступаем к написанию кода. Нажимаем в верхнем ниспадающем меню Инструменты -> Редактор скриптов (Рисунок 3).
Рисунок 3. Открытие редактора скриптов в Google Spreadsheet.
Перед нами в новом окне откроется редактор скриптов с вариантами создания скриптов
(Рисунок 4)
Рисунок 4. Варианты создания скриптов в Редакторе скриптов.
Если вы выберете любой из пунктов слева (создание соответствующего скрипта для Диска, Почты и т.д.), то увидите вступительный скрипт с подробными объяснениями каким образом работать с тем или иным скриптом, Но это только показательные скрипты. Но мы выберем “Пустой проект” и увидим только начало всех скриптов
function myFunction(){}.
Стандартное название проекта — это “Проект без названия”, я же поменял название кликнув на эти слова (верхняя строка на рисунке) и назвал его “Поиски дубликатов” (Рисунок 5). Это для того, чтобы потом при наличии 10 и более скриптов, можно было их различать и без особых усилий находить нужный нам скрипт.
Рисунок 5. “Пустой проект” с первыми стандартными строками кода и измененным названием
В фигурных скобках и будем писать наш скрипт.
Углубляться в изучение JavaScript мы особо не будем, но чтобы понял даже новичок, по ходу написания кода, я буду подробно комментировать происходящее.
Пишем алгоритм
Перед тем, как приступить к написанию любого автоматизирующего скрипта (задачи), нам нужно нарисовать для себя алгоритм действий. И пусть многим это может показаться бредом и тратой лишнего времени и, возможно, вы посчитаете данные действия лишними, но это очень важный этап работы. При работе с большими проектами это очень важно, так как запутаться можно и в 3-х соснах — алгоритм решает данную проблему. Это касается не только JavaScript, это касается абсолютно любого языка, да и вообще любых действий. Для понимания приведем пример на основе получения чашки чая (Рисунок 6).
Рисунок 6. Алгоритм действий получения чашки чая (пример).
Надеюсь, вы заварили чашечку 🙂 и с хорошим настроением читаете дальше.
Нарисуем алгоритм для нашего скрипта по поиску дубликатов (Рисунок 7), он будет более подробный чем пример с чашкой чая.
Рисунок 7. Алгоритм для задачи по поиску дубликатов
Внесу небольшое пояснение, это уже окончательный вид алгоритма, поэтому в нем присутствуют 2 блока которые, возможно не сразу будут понятны для обычного пользователя.
Блок 2 — касается удаления контента и комментариев со страницы, на странице, которая, только что создана и совсем ничего не содержит. Этот блок нужен при повторном использовании скрипта, поэтому мы сразу закладываем данную функцию в скрипт. Об этом более подробно будет написано ниже.
Блок 5 — создание массива и конвертация, об этом будет рассказано подробно ниже. Сейчас же скажу, что созданный массив будет основным элементом обработки данных.
Кодин
Объявим переменные для наших листов, чтобы программа знала, как к ним обращаться.
Для объявления переменной в текущей области видимости используется ключевое слово var. Причем, особенностью языка javascript, как впрочем, и многих других, таких как php, является то, что не нужно специально объявлять тип переменной (как например, в C#: число объявляется integer; строка — string и т.д.).
Для того, чтобы описывать строки кода внутри самого кода, мы будем использовать комментарий в виде текста, который начинается с двух прямых слешей “//”. В разных языках программирования теги комментариев выглядят по разному и представляют из себя последовательность некоторых символов, вот несколько из них: (см. Таблицу 1.)
Таблица 1. Сравнительная таблица тегов комментариев в некоторых языках
При написании программ в редакторах языков, либо при применении правильного синтаксиса, например, в программе notepad++, текст программы будет подсвечиваться соответствующими цветами, которые прописаны для этого языка по умолчанию. Цвет текста комментариев, например, будет зеленый для большинства языков по умолчанию, но на самом деле, в программах для кодинга обычно присутствует возможность настраивать уветовую гамму, хотя и не везде. В google JS цвет комментария будет коричневый.
Рисунок 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
Рисунок 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)
Рисунок 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)
ждите наши следующие выпуски.
ссылка на оригинал статьи http://habrahabr.ru/post/198898/
Добавить комментарий