Регистрация: 23.07.2014
Сообщений: 74
|
Импорт данных из Google Analytics в Google Sheets
|
Я заинтересовался, как можно получить данные из различных API, например, Google Analytics, и перенести их в таблицы Google (Google Sheets). Не удивительно, что уже есть готовое решение в виде скрипта Google Apps Script. Если вы устали от постоянного экспорта данных из Google Analytics, импорта их в Excel и создания одного и того же отчета раз за разом, то эта статья для вас! В этом посте я объясню:
1.Как начать работу со скриптами Google Apps Scripts
2. Как получить данные из Core Reporting API и вставить их в таблицы Google
3. Как использовать значения ячеек, чтобы указать параметры для запроса к Google Analytics API
Прочитав эту статью, вы должны получить таблицу Google, в которой будут a) параметры (например, метрики, которые вы хотите получить из Google Analytics API) из одной таблицы, b) данные, полученные при помощи этих параметров из Google Analytics API и c) вы вставите результат в новую таблицу.
Подсказка: Если вам не хочется читать весь пост, просто откройте эту таблицу Google, чтобы увидеть конечный результат. Можете бесплатно сделать копию и использовать ее на свое усмотрение.
Начинаем работу с Google Apps Scripts
1. Создаем новую таблицу Google
2. Переходим в Tools (инструменты) > Script Editor (редактор скрипта)
3. Выбираем Blank Project (новый проект) в появившемся окне.
4. Вставляем следующий код в окно Script Editor и нажимаем Save (сохранить):
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Get external data’)
.addItem(‘Google Analytics’, ‘getGoogleAnalyticsData’)
.addToUi();
}
function getGoogleAnalyticsData() {
SpreadsheetApp.getUi().alert(‘You clicked Get external data -> Google Analytics menu item!’);
}
5. Если вернуться к таблице Google, с которой мы начали и нажать Refresh (обновить), то вы увидите новый пункт меню Get External Data (получить внешние данные). Вкладка Script Editor закроется, но пусть вас это не беспокоит.
Кликните на пункт меню Google Analytics и вы увидите небольшое всплывающее окно:
Теперь давайте посмотрим на то, что мы только что сделали, чтобы понять, как работает Apps Script:
Мы создали Apps Script, который связан с таблицей Google. Apps Script пока имеет только две функции (выбрать Tools > Script Editor, вернуться к редактору и посмотреть код).
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Get external data’)
.addItem(‘Google Analytics’, ‘getGoogleAnalyticsData’)
.addToUi();
}
Функция onOpen() {} запускается автоматически, когда открывается таблица Google. На будущее, самый простой способ запустить код при каждом открытии документа – вставить функцию onOpen().
В данном случае мы создали новое меню Get External Data (получить внешние данные). У нас также появился новый пункт меню Google Analytics, addItem (добавить пункт).
Самое важное в этом шаге – теперь мы определяем, что происходит, когда пользователь кликает на пункт меню Google Analytics и мы можем это сделать, указав имя функции getGoogleAnalyticsData, чтобы она выполнялась, как только пункт меню будет выбран.
addItem(‘Google Analytics’.‘getGoogleAnalyticsData’)
Просто, правда? Осталось только прописать функцию getGoogleAnalyticsData в оставшуюся часть кода:
function getGoogleAnalyticsData() {
SpreadsheetApp.getUi().alert(‘You clicked Get external data -> Google Analytics menu item!’);
}
Код между скобками {} пока заставляет появиться всплывающее окно, но далее я напишу скрипт, который поможет нам получить данные из Google Analytics.
Как получить данные из Google Analytics API
Google предлагает отличный пример, как делать запрос к Google Analytics API при помощи Apps Script. Так давайте воспользуемся этим примером! (а я вложу свой вклад, объяснив, что делает этот код).
Открываем Script Editor и заменяем функцию getGoogleAnalyticsData() {…} этим кодом:
function getGoogleAnalyticsData() {
var today = new Date();
var oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);
var startDate = Utilities.formatDate(oneWeekAgo, Session.getTimeZone(),'yyyy-MM-dd');
var endDate = Utilities.formatDate(today, Session.getTimeZone(),'yyyy-MM-dd');
var tableId = 'ga:83819930';
var metric = 'ga:visits';
var options = {
'dimensions': 'ga:source,ga:keyword',
'sort': '-ga:visits,ga:source',
'filters': 'ga:medium==organic',
'max-results': 25
};
var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,options);
if (report.rows) {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var headers = report.columnHeaders.map(function(columnHeader) {
return columnHeader.name;
});
sheet.appendRow(headers);
sheet.getRange(2, 1, report.rows.length, headers.length)
.setValues(report.rows);
} else {
Logger.log('No rows returned.');
}
}
Давайте посмотрим, что происходит в этом коде:
var today = new Date();
var oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);
var startDate = Utilities.formatDate(oneWeekAgo, Session.getTimeZone(),'yyyy-MM-dd');
var endDate = Utilities.formatDate(today, Session.getTimeZone(),'yyyy-MM-dd');
Вы можете не смотреть на большую часть кода. Нам важно, что он определяет переменную today (сегодня) и дает ей значение now (сейчас). То есть, здесь указан день и время, когда вы запустили скрипт. Код также определяет переменную oneWeekAgo (одна неделя назад) и задает ей значение одна неделя назад, начиная с того времени, когда вы запустили скрипт.
Как видите, чтобы получить значение одной недели назад, нужно сделать простые вычисления: (дни)* (часов в день) * (минуты в часе) * (секунды в минуте) * 1000. Последнее умножение на 1 000 – это значение переменной в миллисекундах. Таким образом, чтобы получить данные 365 дней назад, нужно:
var oneYearAgo = new Date(today.getTime() - 365 * 24 * 60 * 60 * 1000);
Оставшаяся часть кода просто переводит сегодняшние даты и значение «одна неделя назад» в формат, который будет принять Google Analytics API. Итак, продолжим:
var tableId = 'ga:83819930';
var metric = 'ga:visits';
var options = {
'dimensions': 'ga:source,ga:keyword',
'sort': '-ga:visits,ga:source',
'filters': 'ga:medium==organic',
'max-results': 25
};
Эта часть кода определяет параметры запроса, который мы собираемся задать к Google Analytics API. Если вы их все знаете, то вам повезло. Остальным же рекомендую использовать Google Analytics Query Explorer (который особенно полезен для нахождения tableID).
var tableId = 'ga:83819930';
Это ID Google Analytics View (это не аккаунт, не свойства, а именно просмотр), из которого мы будем получать данные. В вышеуказанном коде вам нужно прописать tableID для одного из просмотров Google Analytics (Views), к которому у вас есть доступ. Его можно найти либо через Query Explorer, как показано на картинке сверху, либо так: Google Analytics > Admin > View Settings (не забудьте добавить «ga:» перед фактическим ID):
var metric = 'ga:visits';
var options = {
'dimensions': 'ga:source,ga:keyword',
'sort': '-ga:visits,ga:source',
'filters': 'ga:medium==organic',
'max-results': 25
};
Этот код определяет метрики, измерения, фильтр и опции сортировки, которые мы будем запрашивать у Google Analytics API. Параметр «max-results» (максимальный результат) можно настроить, как 10 000, что является абсолютным лимитом на каждый запрос к Google Analytics API. Таким образом, если вы планируете восстановить еще больше данных, то вам нужно будет разбить запрос на части (с этим без проблем справиться Apps Script).
Наконец, мы делаем запрос к Google Analytics API:
var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,options);
Ответ от API теперь хранится в отчете переменной; первое, что мы делаем – проверяем, содержит ли переменная в себе какие-либо данные, и если нет, то мы пишем в лог сообщение «No rows returned» (строки не были возвращены):
if (report.rows) {
...
} else {
Logger.log('No rows returned.');
}
Лог – это чрезвычайно полезный инструмент, так как с его помощью можно выявлять проблемы, которые могли произойти во время исполнения кода. Если лог пишет сообщение «No rows returned», вероятней всего, это связано с тем, что вы неверно сформулировали параметры API запроса.
Войти в лог можно через Script Editor: View > Log:
Давайте посмотрим, что происходит, если API не возвращает какие-то данные:
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
Код выше представляет собой всю прелесть Google Apps Script: при помощи двух простых строк вы получили активную таблицу spreadsheet.getActiveSheet() в активном файле таблицы Google SpreadsheetApp.getActive(). Теперь вы можете прописывать данные прямо в эту таблицу!
var headers = report.columnHeaders.map(function(columnHeader) {
return columnHeader.name;
});
sheet.appendRow(headers);
Первая строка - это JavaScript код, но для наших целей он нам не потребуется, так как я получаю хедеры из API ответа, а именно ga:source, ga:keyword, ga:visits.
Вторая строка добавляет значения хедера в активную таблицу, так как ранее мы ее определили:
var sheet = spreadsheet.getActiveSheet();
в активном файле таблицы:
var spreadsheet = SpreadsheetApp.getActive();
Наконец, в эту же таблицу мы добавляем данные, которые мы получили от Google Analytics API:
sheet.getRange(2, 1, report.rows.length, headers.length)
.setValues(report.rows);
Что делает это код:
getRange(row, column, numRows, numColumns)
gets a range, or selects several cells in the active sheet
В этом примере: row = 2 (column = 1 (колонка), numRows = количеству рядов в ответе API (report.rows.length), numColumns = количество хедеров, возвращенных API (headers.length).
.setValues()
sets the value of the range that we got by calling getRange
В этом примере мы задали значения в этом диапазоне значениям ответа Google Analytics API, которые хранятся в report.rows.
Apps Script расставит значения в соответствующие ячейки, несмотря на тот факт, что report.rows не является единым значением.
Наконец, давайте запустим код! Menu > Get External Data > Google Analytics! Далее вы прочитаете очень важную информацию, с которой вы сможете запустить код.
Это первая авторизация, которую нам нужно пройти, чтобы запустить код. Нажмите Continue (продолжить) и одобрите разрешение на запрос (не волнуйтесь, ведь это ваше приложение, и я надеюсь, что не произойдет ничего страшного, если вы разрешите своему же приложению получить доступ к данным).
Вы получили предупреждение «ReferenceError: Analytics is not defined.DetailsDismiss»? Это хорошо, это означает, что мы на правильном пути.
Переходим Tools > Script Editor, а в самом скрипте переходим Resources > Advanced Google Services:
Включаем Google Analytics API (Пока не нажимайте ОК!):
Теперь кликаем на ссылку Google Developers Console. Которая откроет нам консоль Developers. Сейчас ищем Analytics API в списке и включаем его:
Наконец, мы можем нажать ОК в попап окне Script Editor. Возвращаемся обратно к таблице и снова запускаем код.
Появится еще один запрос на доступ, на это раз доступ к данным Google Analytics (еще раз повторю, что можете не переживать и разрешить доступ, так как это ваше приложение).
Если вы не забыли изменить tableID на Google Analytics View ID, то в таблице вы увидите такой результат:
Замечательно, правда? А теперь давайте сделаем это еще более полезным.
Используем значения ячеек, чтобы задать параметры для запросов Google Analytics API
Наверное, мы все сталкивались с такими ситуациями, когда нам нужно было отсылать регулярные отчет боссу или клиентам. Мы брали одни и те же данные из Google Analytics, вставляли их в Excel и отсылали отчет. Давайте попробуем облегчить эту задачу при помощи Google Apps Script.
Сначала я добавлю еще один пункт меню Get External Data:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Get external data')
.addItem('Google Analytics', 'getGoogleAnalyticsData')
.addItem('Create report', 'createReport')
.addToUi();
}
Я также создам отдельную таблицу с параметрами для запроса (как видите, я использовал функцию VLOOKUP, чтобы найти tableID из списка внизу; если вы соберете все эти ID один раз, то это упростит работу в будущем), а также дам имя таблице, в которой будет храниться отчет:
Вот код, который я написал для функции createReport (создать отчет):
function createReport() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('params');
var start = new Date(sheet.getRange(2, 3, 1, 1).getValues());
var end = new Date(sheet.getRange(3, 3, 1, 1).getValues());
var startDate = Utilities.formatDate(start, Session.getTimeZone(),'yyyy-MM-dd');
var endDate = Utilities.formatDate(end, Session.getTimeZone(),'yyyy-MM-dd');
var tableId = String(sheet.getRange(5, 3, 1, 1).getValues());
var metric = String(sheet.getRange(6, 3, 1, 1).getValues());
var options = {
'dimensions': String(sheet.getRange(7, 3, 1, 1).getValues()),
'sort': String(sheet.getRange(8, 3, 1, 1).getValues()),
'filters': String(sheet.getRange(9, 3, 1, 1).getValues()),
'max-results': String(sheet.getRange(10, 3, 1, 1).getValues())
};
Logger.log(tableId);
var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,options);
if (report.rows) {
var reportsheet = spreadsheet.getSheetByName(String(sheet.getRange(11, 3, 1, 1).getValues()));
if(reportsheet) {
spreadsheet.deleteSheet(reportsheet);
}
var reportsheet = spreadsheet.insertSheet(String(sheet.getRange(11, 3, 1, 1).getValues()));
var headers = report.columnHeaders.map(function(columnHeader) {
return columnHeader.name;
});
reportsheet.appendRow(headers);
reportsheet.getRange(2, 1, report.rows.length, headers.length)
.setValues(report.rows);
Logger.log('Report spreadsheet created: %s',
spreadsheet.getUrl());
} else {
Logger.log('No rows returned.');
}
}
Как видите, я добавил в код относительно немного. Несколько простых строк:
var sheet = spreadsheet.getSheetByName('params');
Ранее мы выбрали активную таблицу, но в этом примере я выбираю таблицу по названию («params»). Позже я задам значение для API запроса из этой таблицы:
sheet.getRange(5, 3, 1, 1).getValues()
Мы обсудили getRange, но getValues () – это то же самое. Я позже конвертирую это в String (), чтобы Google Analytics смог это принять:
String(sheet.getRange(5, 3, 1, 1).getValues())
Наконец, я создаю новую таблицу на основании названия, указанного в «params»:
spreadsheet.insertSheet(String(sheet.getRange(11, 3, 1, 1).getValues()));
Вот и все! Открываем готовую таблицу Google, делаем копию и используем по назначению. Надеюсь эта статья помогла вам.
|