Создание статистики и управление рассылки писем
в 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 и больше, можно с легкостью допустить ошибку при составлении тех же формул.

Единственное, чего не хватает в нашем скрипте, так это параметра “только значения”, который необходим для того, чтобы не нагружать документ формулами.

Таблица с аккаунтом и критериями — Аккаунт
Таблица сводная — Сводная

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

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

Пользуйтесь файлами, учитесь, делитесь ссылками с друзьями.
Ждем ваших отзывов.

С уважением, Антон.

Рассылка писем в Google Docs (Drive)