Создание статистики и управление рассылки писем
в Google Docs (с разных аккаунтов) на основе FormEmailer
Мы, Большой Брат Ltd., решили создать статистику результатов рассылки посредством скрипта — шаблона FormEmailer. Расскажем, как это делалось.
Для любопытных сразу покажу, к чему мы стремимся.
Выглядеть это будет примерно так:
Рисунок 1: Итоговый вид разработки.
Теперь приступим к самому интересному — реализации.
Для работы с рассылкой будем использовать скрипт — шаблон FormEmailer. Он многофункционален, гибок в настройке и при небольших усилиях становится действительно мощным инструментом.
Создаем таблицу “Рассылка” в Google Docs.
Нажимаем “Вставка” -> “Скрипт”, находим FormEmailer от hgabreu, нажимаем Install. Авторизируемся, давая право использовать свой календарь.
После установки в меню появится пункт Form Emailer.
При нажатии на него вы увидите в меню Install. Нажимаем на него и получаем форму с выбором языка и Листа с данными (сюда вы будете собирать базу для рассылки).
Оговорюсь, в БАЗУ ДЛЯ РАССЫЛКИ мы включаем исключительно своих подписчиков.
Также важно учитывать и специфики ограничений Google таблиц. Поэтому настоятельно рекомендуем ознакомиться с данным разделом Читать тут
ВАЖНО: Для того чтобы все создалось без ошибок и проблем, лист должен содержать хотя бы названия колонок (иметь шапку).
Если база для рассылки у Вас больше пары тысяч, нужно использовать дополнительные аккаунты и проделывать в них те же манипуляции.
Если у Вас возникли проблемы с установкой, зайдите на сайт программы. Сайт
В итоге, мы получаем инструмент для массовой рассылки с лимитом на использование аккаунта Gmail — 500 писем в день. Желательно, не использовать максимальный лимит из-за риска получить бан.
Так выглядят наши документы:
Рисунок 2: Виды листов
1. — лист “Data”; 2. — лист “FormEmailer”; 3. — лист “05/2012”; 4. — лист “Report”.
При создании, из Install вы получите 2 вкладки:
1) Data — наша база. Этот документ Вы будете использовать в качестве базы рассылки.
2) FormEmailer — шаблон и основные настройки рассылки.
Создаем еще один лист — “Report”, а лист архива мы создадим с помощью программы. В него будут собираться все отчеты об отправке.
Итак, приступим к приведению скрипта FormEmailer-a под себя. Версия не имеет значения.
Открываем редактор скриптов, откроется код FormEmailera.
С помощью Ctrl+F найдем функцию processManually и впишем перед ней наш код.
Для того, чтобы все работало, необходимо чтобы код был таким же, как и у меня.
Данный код добавит в наш документ лист архива с названием текущего месяца и года в виде “05/2012” — месяц/год.
var thisMonth = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "MM/yyyy");
var outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(thisMonth);
if ( !outputSheet )
{
SpreadsheetApp.getActiveSpreadsheet().insertSheet(thisMonth);
var outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(thisMonth);
}Добавляем счетчик отправленных писем.
var count_send_email = 0;Создаем в колонках ячейки с датой и копированием в архив для отсчета и комментируем текст, который отвечает за статистические данные.
var thisDate_send_email = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "MM/dd/yyyy");
var time_send_email = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "MM/dd/yyyy hh:mm:ss");
c.fs.getRange(line,1).setValue("Email sent, " + time_send_email);
c.fs.getRange(line,2).setValue(thisDate_send_email);
var dataHeight = c.fs.getDataRange().getHeight();
var dataWidth = c.fs.getDataRange().getWidth();
// Check if there free rows in the output sheet
if(outputSheet.getDataRange().getHeight()==outputSheet.getMaxRows()) outputSheet.insertRowAfter(outputSheet.getMaxRows());
Logger.log("height:"+outputSheet.getDataRange().getHeight());
Logger.log("max: "+outputSheet.getMaxRows())
count_send_email++;
c.fs.getRange(line, 1, 1, dataWidth).moveTo(outputSheet.getRange(outputSheet.getDataRange().getHeight()+1, 1));
c.fs.deleteRow(line);
} catch(e)
{
status.push(repl_(T.statusError, s.qtt == 1 ? '' : i+1, e));
c.err.push(repl_(T.mailError, s.qtt == 1 ? '' : i+1, e, line));
}
} else
{
status.push(T.statusQuota);
if( c.err.length == 0 || !startsWith_(c.err[c.err.length-1], T.statusQuota) )
c.err.push(t.statusQuota+'. '+new Date());
break;
}
}
outputSheet.getRange(outputSheet.getLastRow(),5).setValue(count_send_email);
/* c.fs.getRange(line,1).setValue(status.join('; '));
if( c.fl && line != 2 )
{
if( s.closure === 'values' )
all.setValues([values]);
else if( s.closure === 'clear' )
all.clearContent();
//else formulas > just leave them there
}*/Вдаваться в подробности написания скрипта мы не будем.
Если возникнут вопросы, пишите — ответим.
На этом доработка FormEmailer-a почти закончена.
Приступаем к статистике.
Создадим файл, где и будет собираться статистика.
Далее, нам необходимо сделать так, чтобы суммарная таблица “Report” копировалась в сводный документ Статистика
Для этого, создаем новую функцию updateData в скрипте FormEmailera.
В конце кода, примерно на строке 1164, после закрывающей скобки предыдущей функции добавляем следующее:
var SOURCE_SPREADSHEET_ID = "ключ листа, с которого будем копировать";
var SOURCE_SHEET_NAME = "название копируемого листа";
var DESTINATION_SPREADSHEET_ID = "ключ листа, куда будем копировать";
var DESTINATION_SHEET_NAME = "название листа, куда будем копировать";
function updateData() {
try {
var sourceSheet = SpreadsheetApp.openById(SOURCE_SPREADSHEET_ID).getSheetByName(SOURCE_SHEET_NAME);
if(sourceSheet!=null) {
var sourceData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SOURCE_SHEET_NAME).getDataRange().getValues();
var destinSheet = SpreadsheetApp.openById(DESTINATION_SPREADSHEET_ID).getSheetByName(DESTINATION_SHEET_NAME);
if(destinSheet!=null) destinSheet.getRange(1, 1, sourceData.length, sourceData[0].length).setValues(sourceData);
else throw new Error("Destination sheet not found.");
}
else throw new Error("Source sheet not found.");
}
catch(e) {
Logger.log(e.message);
Browser.msgBox(Logger.getLog());
}
}Для того чтобы наш лист сводной статистики приобрел окончательный вид, пропишем формулы для сбора количества писем категории отправлено / ожидает отправки.
ОТПРАВЛЕНО:
=(ARRAYFORMULA(SUMIF('05/2012'!$B:$B,$A2,'05/2012'!$E:$E)))
Расшифруем формулу:
ARRAYFORMULA — Формулы массива «в одной ячейке» позволяют записывать формулы с помощью ввода массива, а не выходных данных.
SUMIF — СуммаЕСЛИ(диапазон; критерии; суммарный_диапазон)
диапазон — лист 05/2012 колонка B, знак $ закрепляет данную колонку при размножении формулы посредством “тянем вниз” (то есть, в каждой ячейке будет диапазон $B:$B; $B:$B; $B:$B, если бы $ не было, то было бы так: B:B; С: С; D:D. (в данном случае, это дата).
$A2 — это критерий, по которому следует вести суммирование, в нашем случае это 5/1/2012 и тд.
ВАЖНО: для того, чтобы у Вас все подсчеты были верными, вид искомых критериев должен быть одинаковым. То есть, если Вы отбираете по 5/1/2012, то это не то же самое, что 1/5/2012, ММ/ДД/ГГГГ не равно ДД/ММ/ГГГГ.
суммарный_диапазон — ‘05/2012'!$E:$E — столбец, где должны производиться расчеты (в нашем случае, это количество писем).
ОЖИДАЕТ:
=COUNTIF('Data'!C:C, "*@*")
расшифруем формулу — СЧИТАТЬ ЕСЛИ (В столбце С листа “Data” есть текст с содержимым @)
подробнее о формулах
Список функций Таблиц Google
Есть одна проблема — если мы запишем нашу формулу во все столбцы, то получим во всех столбцах одну и ту же цифру, а нам нужна реальная цифра, привязанная к дате.
Для правильной обработки ожидающих отправки писем, необходимо привязать цифру к дате и при работе с формулой оставить лишь полученное значение. Приведенный ниже код решает данную задачу.
var thisDay = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "dd");
var myArray_Date = new Array();
myArray_Date = [1,2,3,4,7,8,9,10,11,14,15,16,17,18,21,22,23,24,25,28,29,30,31];
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report");
for (var date_count = 0; date_count < 30; date_count++)
{
if (myArray_Date[date_count]==thisDay)
{
var y = myArray_Date[date_count] + 1;
dataSheet.getRange(y, 3).setFormula("=COUNTIF('Date'!C:C, \"*@*\")");
}
}
var datatocopy = dataSheet.getRange(y, 3).getValues();
dataSheet.getRange(y, 3).setValues(datatocopy);
Теперь приступим к формированию статистики.
У нас есть документ для формирования статистики, но для того, чтобы сформировать ее полностью, необходим не один такой документ, а несколько.
Но пока мы будем исходить только из одного документа, имеющегося у нас.
Для формирования используем формулы.
Создаем страницу для суммарного подсчета.
Рисунок 3. Статистика, которую мы и хотели увидеть.
Для того, чтобы ускорить работу, напишем скрипт для сбора данных с наших двух листов.
function myFunction()
{
var workSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("REPORT");
var rew;
var myReport_Name = new Array();
var first_work_row = 3, last_work_row = 33, count_column = 2;
workSheet.getRange("B" + first_work_row + ":F" + last_work_row).clearContent();
for (var i=first_work_row; i<=last_work_row; i++)
{
rew = i - 1;
workSheet.getRange(i, 2).setFormula("=(ARRAYFORMULA(SUMIF('Аккаунт для рассылки'!$A:$A&'Аккаунт для рассылки'!$A$1; $A" + i + "&$B$1; 'Аккаунт для рассылки'!B:B)))");
workSheet.getRange(i, 3).setFormula("=(ARRAYFORMULA(SUMIF('Аккаунт для рассылки'!$A:$A&'Аккаунт для рассылки'!$A$1; $A" + i + "&$B$1; 'Аккаунт для рассылки'!C:C)))");
workSheet.getRange(i, 4).setFormula("=Report_Тематика!F" + rew);
workSheet.getRange(i, 5).setFormula("=Report_Тематика!E" + rew);
workSheet.getRange(i, 6).setFormula("=Report_Тематика!D" + rew);
}
}Для того, чтобы в меню была кнопка, допишем маленький код:
function onOpen()
{
SpreadsheetApp.getActiveSpreadsheet().addMenu("Копаем?", [{name: "Копаем!", functionName: "myFunction"}]);
}Теперь не надо заходить в скрипт каждый раз, когда надо совершить подсчет. Просто нажмем на кнопку “Копать” и ждем результата.
Для автоматизации процесса запуска скрипта также можно использовать тригерры, которые настраиваются в редакторе скриптов (Ресурсы — Тригерры текущего скрипта).
Должна появиться вот такая таблица, нажимаем на ссылку “Добавить” и выставляем следующие параметры.
Теперь скрипт будет выполняться автоматически без вашего участия каждые 2 часа. Настраивать можно по-разному.
Можно прописать формулы вручную, но когда у тебя не один лист, а 20 и больше, можно с легкостью допустить ошибку при составлении тех же формул.
Единственное, чего не хватает в нашем скрипте, так это параметра “только значения”, который необходим для того, чтобы не нагружать документ формулами.
Таблица с аккаунтом и критериями — Аккаунт
Таблица сводная — Сводная
Единственный минус данной статистики, это необходимость проставлять даты вручную. Если кто-то предложит автоматизацию процесса проставления даты, без учета выходных, будем благодарны вам за помощь.
Вот и все, что мы смогли рассказать вам об организации статистики с онлайн доступом.
Пользуйтесь файлами, учитесь, делитесь ссылками с друзьями.
Ждем ваших отзывов.
С уважением, Антон.
