20 возможностей Google Sheets, которые сэкономят время SEO-оптимизатору: функции, плагины, макросы

Сегодня Google Spreadsheets является достойной заменой столь необходимой для каждого оптимизатора программы – Microsoft Excel. Конечно, многий функционал урезан и там, где в Excel было достаточно двух кликов, в Google Таблицах нужно устанавливать плагины и писать скрипты. Однако основные функции и возможности, которые могут понадобиться, есть. Ранее мой коллега Евгений Аралов уже рассматривал основные приёмы и функции в Excel, которые понадобятся любому SEO-специалисту. В этой статье мы рассмотрим возможности Google Spreadsheets, которые облегчат и ускорят вам работу: плагины, формулы, встроенные функции и несколько макросов.

1. Плагины Google Sheets:

• Spreadsheet Google Analytics
• Search Analytics for Sheets
• Remove Duplicates
• Crop Sheet

2. Формулы в Google Spreadsheets

• для подсчёта количества символов в метаданных — LEN
• для объединения ячеек при генерации шаблонных метаданных — CONCATENATE
• для удаления лишних пробелов в ячейке — TRIM и SUBSTITUTE
• для смены регистра — LOWER и PROPER
• для уникализации значений в диапазоне — UNIQUE
• для разделения фразы или URL по столбцам — SPLIT
• для объединения данных из двух таблиц — VLOOKUP
• для быстрого протягивания формулы вниз — ARRAYFORMULA
• извлечение нужного фрагмента текста из ячейки — REGEXEXTRACT
• для поиска ячеек, содержащие определенный текст — IF и REGEXMATCH
• для импорта данных с сайта в таблицу — IMPORTXML

3. Макросы для Google таблиц

• раскрашиваем таблицу
• ещё один способ найти и удалить дубликаты

4. Условное форматирование в гугл таблицах

• сортировка и фильтр по цвету
• как сделать цветной чекбокс

Заключение

1. Google Sheets плагины (Add-Ons) для работы SEO-специалистов

1.1. Плагин Spreadsheet Google Analytics

Так как GA является одним из основных инструментов, метрик SEO-оптимизатора, этот плагин будет наиболее полезным дополнением в таблицах.

С помощью этого инструмента вы можете:

1. Получать данные из нескольких представлений.
2. Совершать пользовательские вычисления.
3. Визуализировать данные и внедрять эти диаграммы, графики на сторонние веб-сайты.
4. Запланировать отчёты для автоматического запуска и обновления.
5. Контролировать, кто может видеть ваши отчёты, используя настройки конфиденциальности Google Таблиц.

Как работать с дополнением:

1) Подключите плагин к своим таблицам.

Google Spreadsheets Add-On Google Analytics в магазине Chrome

2) Перейдите в «Дополнения» → «Google Analytics» → «Create New Report».
3) В поле «Name» заполните название отчёта, который хотите создать.
4) В полях «Select a view» выберите аккаунт, ресурс и представление для будущего отчёта.
5) В «Choose configuration options» настройте нужные параметры, метрики, сегменты.
6) После нажатия кнопки «Create a report» появится отдельный лист «Report Configuration» с настройками будущего отчёта.
7) Снова перейдите в «Дополнения» → «Google Analytics», но выберите уже «Run Reports».
8) После окончания формирования отчёта появится лист с названием, которое было указано на третьем этапе.

Чтобы настроить автоматическое обновление отчёта, нужно:

• перейти в «Дополнения» → «Google Analytics» → «Schedule Reports»;
• поставить галочку в чекбокс «Enable reports to run automatically»;
• выбрать частоту обновления отчёта, в какой промежуток суток он будет экспортировать данные из аналитики.

Разберём плагин на примере построения простенького отчёта о количестве посетителей из Google за неделю, сгруппированного по дням и страницам входа.

Шаг 1. Настраиваем отчёт, который хотим создать.

Шаг 2. В сформировавшихся настройках меняем «30daysAgo» на «7daysAgo».

Шаг 3. Запускаем и получаем готовый отчёт.

Более подробно о дополнении можно прочесть в справке.

1.2. Search Analytics for Sheets

Ещё один довольно важный плагин для SEO-специалиста. С помощью этого дополнения можно выгрузить данные из отчёта «Эффективность» в Google Search Console посредством нескольких кликов. При этом, если в самой панели отображается первая тысяча строк, с помощью этого плагина вы сможете выгрузить до 5 тыс. запросов и URL.

Как выгрузить данные:

1) Установите плагин.

Ссылка на плагин в магазине дополнений Google Chrome

2) Перейдите в «Дополнения» → «Search Analytics for Sheets» → «Open Sidebar».

3) Во всплывающем сайдбаре на вкладке Requests заполните следующие поля:

• Verified Site – нужный нам сайт.
• Date Range – диапазон дат, за которые нужно выгрузить данные.
• Search Type – выбор типа поиска: веб, изображение, видео.
• Group By – выбор критерия, по которому будут группироваться данные (по запросам, страницам, дате, устройствам и т. д.).
• Filter By (по дефолту без фильтрации) – можно отфильтровать выгрузку по запросу, странице, стране, устройству и виду в поиске.
• Aggregation Type – выбор типа агрегации (объединения). Обычно оставляем значение «Default (auto)». Если на вашем сайте отображается более одного URL-адреса для одного запроса, при агрегировании по свойству Search Console будет считать данные только один раз (один показ, один клик, самая высокая позиция). При агрегировании по страницам данные для каждого URL-адреса подсчитываются индивидуально (поэтому, если ваш сайт отображается на первой странице с 7 URL-адресами для одного запроса, этот единственный запрос будет считать 7 показов).
• Rows returned – количество строк: либо 1 000, либо 5 000. При выборе «Everything» (все строки) всё равно не выгрузит более 5 тыс. строк.
• Result Sheet – выбор листа, на который будут экспортироваться данные (можно создать новый или выгрузить на существующий).

4) Выгрузите отчёт, нажав кнопку «Request Data».

Для примера выгрузим данные о кликах, показах, CTR и средней позиции по дате и устройству.

Шаг 1. Настраиваем проект.

Шаг 2. На выходе получаем табличку с данными, сгруппированными по дате и устройству.

1.3. Remove Duplicates

Удобный плагин для поиска уникальных строк и дубликатов. Есть один минус – плагин платный (34$ в год или 60$ навсегда), но есть бесплатный период в 30 дней к дополнению, чтобы «пощупать», оценить его необходимость.

Как использовать дополнение:

1) Установить плагин (ссылка на дополнение в магазине Chrome).
2) Перейти в «Дополнения» → «Remove Duplicates» → «Find duplicate or unique row».
3) Выбрать диапазон: если таблица была выделена ранее, диапазон выбирает автоматически. На этом этапе можно сохранить бэкап таблицы.
4) Выбрать тип данных для поиска:

• Дубликаты (находит повторения, исключая первое совпадение).
• Дубликаты +1 (находит все дубликаты).
• Только уникальные значения.
• Уникальные значения +1 (подсвечивает уникальные значения и первое совпадение дубликатов).

5) Выбрать столбцы, по которым будет осуществляться поиск заданного критерия.
6) Выбрать тип действия, которое будет применено к найденным значениям (дубликатам или уникальным): подсветить, скопировать или перенести значения, очистить или удалить строки.
7) По окончании работы плагина будет представлен краткий отчёт.

Разберём работу плагина на примере поиска неявных дублей с одинаковой базовой, фразовой и точной частотой:

1.4. Crop Sheet

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

Ссылка на Add-On в магазине Google Chrome

Crop to Data – обрезать таблицу до столбцов и строк с информацией.
Crop to Selection – обрезать таблицу до выделенной области.

2. Функции в Google Sheets для SEO-оптимизаторов

Мы часто ищем, как ускорить тот или иной процесс нашей работы. Часто возникает вопрос: «А можно это сделать/посчитать в таблицах?». Ответ всегда: да, можно. Просто нужно знать, как это сделать.

Далее опишем несколько функций, которые используем в своей работе. Мы не претендуем на истину, и, возможно, некоторые процессы можно сделать ещё проще и быстрее. Тогда, как говорится, share your experience 🙂

Начнём с простеньких функций, которые, возможно, кто-то ещё не использует в своей работе.

2.1. =LEN – вычисляет длину строки

Формула =LEN подойдёт, например, для быстрого подсчёта количества символов в Title, Description, любом другом тексте.

=LEN(A1)  #в ячейке А1 находится текст, в котором нужно произвести подсчёт 
=LEN("текст, в котором нужно рассчитать количество символов")

2.2. =CONCATENATE – объединить ячейки/текст

Есть более простая функция =CONCAT, которая объединяет значения только двух ячеек в гугл таблицах. Но так как у =CONCATENATE (=СЦЕПИТЬ) возможностей больше, и она чаще используется, рассмотрим именно её.

Несколько вариантов, как можно использовать эту функцию:

• генерация шаблонных метаданных/текстов;
• подготовка директив для Disavow;
• подготовка директив по редиректу для .htaccess и т. д.

=CONCATENATE(A1:C1)                   #объединить текст из трёх ячеек подряд
=CONCATENATE(A1:B1;D1)                #объединить текст из трёх ячеек через одну
=CONCATENATE(A1:B1;"текст")           #объединить текст из двух ячеек и статичного текста
=CONCATENATE(C21&" "&D21)             #объединить текст из двух ячеек с добавлением пробела
=CONCATENATE(A1;" ";C1)               #объединить текст из двух ячеек с добавлением пробела

2.3. =TRIM и =SUBSTITUTE – удаление пробелов

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

• При генерации метаданных появились лишние (двойные) пробелы – в таком случае обращаемся к формуле =TRIM (=СЖПРОБЕЛЫ).
• Часто при переносе данных может возникнуть проблема, что число в ячейке записано в текстовом формате, ещё и с пробелом для разделения на разряды. В таком случае вы не сможете применить к ней никакие арифметические функции. Чтобы избавиться от пробела и далее преобразовать текст в число, нужна функция =SUBSTITUTE (=ПОДСТАВИТЬ).

=TRIM(A1)
=TRIM("текст,  из которого   нужно удалить лишние пробелы ")
=SUBSTITUTE(A1;" ";"")

2.4. =LOWER и =PROPER – смена регистров

Тоже две функции, которые рассмотрим вместе, так у них одна задача – сменить регистр текста в ячейке.

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

Воспользуйтесь функцией =PROPER (=ПРОПНАЧ), чтобы преобразовать первую букву слова из строчной в прописную, а =LOWER (=СТРОЧН) – из прописной в строчную.

=LOWER(A1)
=LOWER("текст, в котором нужно изменить регистр букв")
=PROPER(A1)
=PROPER("текст, в котором нужно изменить регистр букв")

2.5. =UNIQUE – уникальные значения диапазона

Так как в Google Sheets почему-то до сих пор нет волшебной кнопки «Удалить дубликаты», все изощряются, как могут. Когда есть один список с данными и нужно выделить уникальные значения – можно смело использовать функцию =UNIQUE.

Пример, когда пригодится:
Нам нужны все URL сайта. Парсим сайт, выгружаем данные из Яндекс.Вебмастера, берём адреса из Sitemap, объединяем это всё в один список. Естественно, многие URL будут повторяться. Для этого в соседнем столбце прописываем формулу =UNIQUE, указав нужный диапазон.

=UNIQUE(A1:A1000)
=UNIQUE(A:A)

2.6. =SPLIT – разделение ячейки по столбцам

В Google Таблицах есть два способа разделить текст в ячейке по столбцам: через функцию =SPLIT или через кнопку Данные → Разделить текст на столбцы.

Я приверженец первого способа, так у него есть неоспоримый плюс – возможность указать несколько разделителей одновременно, разбив текст на нужные части.

=SPLIT(C3;"—") # разделить текст в ячейке C3 по длинному тире
=SPLIT(C3;" ") # разделить текст в ячейке C3 по пробелу
=SPLIT(C3;":"&"/") # разделить текст в ячейке C3 по двоеточию и слэшу

2.7. =VLOOKUP – поиск по первому столбцу диапазона и возврат значения из найденной ячейки

Функция =VLOOKUP (=ВПР) – незаменимая, экономящая много времени формула для объединения данных из двух таблиц.
Возможных вариантов ситуаций, когда может понадобиться формула – множество. Приведём пример использования функции =VLOOKUP в сборе семантики.

Тот, кто использует сервис Just-Magic для кластеризации семантики, знает, что при выгрузке мы получаем список ключевых фраз и номер кластера. Чтобы очеловечить название кластера, пригодится =VLOOKUP.

Шаг 1. Чтобы заменить номер группы на название, в первую очередь, нам нужна таблица с номером группы (полученным в JM), запросами и базовой частотой. Сортируем её по номеру группы и частоте, чтобы первый запрос в кластере был самым частотным.

Шаг 2. Копируем столбцы с номером группы и запросов и выносим на другой лист.

Шаг 3. Удаляем дубликаты по столбцу с номером кластера. То есть, после этой манипуляции у вас должен остаться такой массив данных, где для каждого номера группы будет по одному наиболее частотному запросу.

Шаг 4. Возвращаемся на первый лист и в выбранной ячейке прописываем =VLOOKUP(запрос; диапазон; номер_столбца; [отсортировано]).

Для более наглядного примера, формула может выглядеть следующим образом:

=VLOOKUP(A1; 'Лист2'!$A$1:$B$22; 2; 0)

Шаг 5. Протягиваем функцию вниз (или используем =ARRAYFORMULA, подробнее опишем ниже) и, вуаля, для каждого запроса у вас есть читаемое название кластера.

Таким же образом можно добавить в свою таблицу с семантикой данные о ранжируемой странице и позиции по запросу из SeoLib.

2.8. =ARRAYFORMULA – протягиваем формулу вниз

Чтобы формулу из одной ячейки распространить по всему столбцу, можно её протянуть или копипастнуть. А можно использовать функцию =ARRAYFORMULA, которая за несколько секунд распространит формулу по столбцу или нужному диапазону.

На примере функции =VLOOKUP, =ARRAYFORMULA будет иметь следующий вид:

=ARRAYFORMULA(VLOOKUP(A2:A23;'Лист2'!$A$2:$B$11;2;0))

Для людей, которые не любят читать справку: при форматировании многих функций, зажав Ctrl+Shift+Enter, вы автоматически добавите =ARRAYFORMULA).

2.9. =REGEXEXTRACT – извлекаем нужный фрагмент текста из ячейки

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

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

=REGEXEXTRACT(A1;"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)")

Из ячейки можно достать любой текст, только нужно знать, как 🙂

2.10. =IF – отображает результат логической проверки условия

Вариаций формул с =IF (=ЕСЛИ) и возможностей их применений множество. Например, чтобы не пользоваться условным форматированием «Текст содержит», разберём использование функции =IF совместно с =REGEXMATCH для выделения геонезависимых запросов с топонимом «спб».

=IF(REGEXMATCH(B12;"спб");"1";"0") # значение 1 в примере – true, 2 – false

Таким образом, в соседнем столбце у вас будет маркер, определяющий запросы с «спб».

Функцию =IF можно использовать, когда нужно сравнить два столбца по порядку. Например, сравним значения в соседних ячейках столбцов B и A:

=IF(B1=A1;"совпало";"не совпало")

Если хотите сравнить значения, которые находятся в разном месте в столбцах, рекомендуем использовать функцию =VLOOKUP, о которой рассказывали выше.

2.11. =IMPORTXML – импортировать данные с сайта в таблицу

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

Как это можно сделать:
1) руками копировать <title> каждой страницы и вставлять в таблицу – 20 минут вашего времени;
2) спарсить Title каким-нибудь краулером – 10 минут вашего времени;
3) использовать =IMPORTXML – 10 секунд и всё готово.

Несколько запросов XPath, которые могут пригодиться:

XPath	Для чего
"//title"	выгрузить Title
"//h1"	выгрузить H1
"//meta[@name='description']/@content"	выгрузить Description
"//meta[@name='keywords']/@content"	выгрузить Keywords
"//link[@rel='canonical']/@href"	выгрузить Canonical
"//link[@rel='alternate']/@href"	выгрузить Alternate
"//@href"	выгрузить все ссылки на странице
"//a[contains(@href, 'mailTo:') or contains(@href, 'mailto:')]/@href"	выгрузить email со страницы
"//a[contains(@href, 'ok.ru/') or contains(@href, 'vk.com/feed') or contains(@href, 'twitter.com/') or contains(@href, 'facebook.com/')]/@href"	выгрузить социальные сети

 

На самом деле, есть множество функций и вариантов их использования в работе SEO-специалиста. Главное, вовремя задать себе вопрос: можно ли то, на что я трачу уйму времени, сделать быстрее? Поискать немного в интернете (сразу скажу, что искать информацию лучше на английском языке, объективно её больше), поинтересоваться у коллег, как делают они. И, вероятней всего, вы найдёте решение.

3. Макросы для Google Таблиц, которые могут пригодиться в работе.

Если у Microsoft Excel есть VBA, то у Google Таблиц – редактор скриптов. Код пишется на JS, информации в сети очень много, и даже не программисту не составит труда написать маленький лёгкий скрипт для своей таблицы.

Я использую скрипты только для раскрашивания таблиц и как ещё один способ подсветки строк с дубликатами.

3.1. Раскрашиваем таблицу

Если у вас есть таблица со статусами задач, этот скрипт вам пригодится. Да, можно использовать условное форматирование вместо него, но будет закрашена только одна ячейка. С помощью скрипта – закрашивается вся строка.

Шаблон таблицы из примера можно скачать по ссылке.

Как он работает:

Скрипт:

function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var statusColumnOffset = getStatusColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    rowRange = range.offset(i, 0, 1);
    status = rowRange.offset(0, statusColumnOffset).getValue();
    if (status == 'Completed') {
      rowRange.setBackgroundColor("#99CC99");
    } else if (status == 'In Progress') {
      rowRange.setBackgroundColor("#FFDD88");    
    } else if (status == 'Not Started') {
      rowRange.setBackgroundColor("#CC6666");          
    }
  }
}

function getStatusColumnOffset() {
  lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);

  for (var i = 0; i < range.getLastColumn(); i++) {
    if (range.offset(0, i, 1, 1).getValue() == "Status") {
      return i;
    } 
  }
}

3.2. Поиск дубликатов (да-да, ещё один способ)

Если вы не хотите покупать плагин Remove Duplicates, можете использовать урезанный вариант в виде скрипта. Он хорош тем, что подсвечивает всю строку. Если его дописать, можно настроить поиск и выделение дублей по нескольким параметрам.

Скрипт:

function findDuplicates() {
  var CHECK_COLUMNS = [2];

  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var numRows = sourceSheet.getLastRow();
  var numCols = sourceSheet.getLastColumn();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.insertSheet("FindDupes");

  for (var i = 0; i < CHECK_COLUMNS.length; i++) {
    var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);
    var nextCol = newSheet.getLastColumn() + 1;
    sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows));
  }

  var dupes = false;
  var data = newSheet.getDataRange().getValues();
  for (i = 1; i < data.length - 1; i++) {
    for (j = i+1; j < data.length; j++) {
      if  (data[i].join() == data[j].join()) {
        dupes = true;
        sourceSheet.getRange(i+1,1,1,numCols).setBackground("red");
        sourceSheet.getRange(j+1,1,1,numCols).setBackground("red");
      }
    }
  }

  ss.deleteSheet(newSheet);

  if (dupes) {
    Browser.msgBox("Possible duplicate(s) found and colored red.");
  } else {
    Browser.msgBox("No duplicates found.");
  }
};

 

Условное форматирование в гугл таблицах

В Google Sheets есть встроенные возможности для автоматического форматирования цвета ячеек и текста (или целых строк) в зависимости от заданных условий. Для этого:

1. Выделите диапазон ячеек, формат которых хотите изменить.
2. Откройте меню Формат → Условное форматирование
3. Выберите правила форматирования и цвет, который будет применяться к ним.

4.1. Cортировка и фильтр по цвету

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

4.2. Как сделать цветной чекбокс

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

1. Чекбокс делается с помощью меню Вставка → Флажок
2. Далее выбираем диапазон с флажками и устанавливаем правила форматирования «Равно» — TRUE (для галочки) или FALSE (для пустого флажка).
3. Выбираем цвет чекбокса.

Заключение

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

Заказать инструкции

Еще по теме:

• Отслеживание кликов в Google Analytics c помощью Google Tag Manager
В данной статье мы рассмотрим, как настроить отслеживание кликов по любому элементу на сайте в Google Analytics с помощью Google Tag Manager (далее GTM). Если...
• Как долго ждать результата от Disavow Tool?
Подскажите, как быстро срабатывает Disavow Tool? Как быстро ждать результата от таких действий, может есть какие-то кейсы? Ответ Время, необходимое для того, чтобы Google учел...
• Как узнать позицию изображения по определенному ключевому слову?
Как узнать позицию изображения в картинках по определенному ключевому слову? Или же просто позицию. Может есть какие-то сервисы? Ответ Для определения позиции изображения в выдаче...
• Какими сервисами лучше воспользоваться для сбора семантики на английском языке?
Какими сервисами лучше воспользоваться для сбора семантики на английском языке (кроме Semrush)? Ответ Для сбора семантики на английском языке существует ряд мощных инструментов и сервисов:...
• 10 примеров применения операторов поиска Яндекс и Google
В работе SEO-специалиста есть необходимость отслеживать различные показатели своего сайта и сайтов конкурентов, например: количество страниц в индексе, наличие санкций, релевантность текстов, учет ссылок и...