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

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

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

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

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

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

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

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

    Similar posts

    Comments 0

    Only users with full accounts can post comments. Log in, please.