Сложная автоматическая Email рассылка Google таблиц

Создаем сложную автоматическую Email рассылку Google таблиц, с помощью Apps Script

Код для создания скрипта в Apps Script:

// Подставляем имя

function myFunction() {

var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Получаем активную книгу и активный лист

if(list.getName() === "Список заказчиков"){ // Проверяем имя активного листа, и если имя совпадает, то

list.getRange('C2:C900').activate(); //Выделяем диапазон

list.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true}); //Очищаем выделенный диапазон

list.getRange("C2").setFormula("=IFERROR(VLOOKUP(A2;'Email адреса'!A:C;3;0);\"\")"); // Обращаемся к конкретному диапазону и подставляем туда формулу

var ks = list.getLastRow(); // Определяем последнюю строку, т.е. количество строк для заполнение формулой

var diap = list.getRange(2, 3, ks-1); // Определяем диапазон для заполнения формулой (строка, столбец, количество строк, количество столбцов)

list.getRange("C2").copyTo(diap); // Копируем формулу из определенной ячейки в выбранный диапазон

};

};

// Подставляем почту

function myFunction1() {

var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Получаем активную книгу и активный лист

if(list.getName() === "Список заказчиков"){ // Проверяем имя активного листа, и если имя совпадает, то

list.getRange('D2:D900').activate(); //Выделяем диапазон

list.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true}); //Очищаем выделенный диапазон

list.getRange("D2").setFormula("=IFERROR(VLOOKUP(A2;'Email адреса'!A:C;2;0);\"\")"); // Обращаемся к конкретному диапазону и подставляем туда формулу

var ks = list.getLastRow(); // Определяем последнюю строку, т.е. количество строк для заполнение формулой

var diap = list.getRange(2, 4, ks-1); // Определяем диапазон для заполнения формулой (строка, столбец, количество строк, количество столбцов)

list.getRange("D2").copyTo(diap); // Копируем формулу из определенной ячейки в выбранный диапазон

};

};

// Делаем рассылку - отправляем определенный лист таблицы вложением в формате ПДФ

function myFunction2() {

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Обращаемся к активной таблице

let sheet = spreadsheet.getSheetByName('Список заказчиков') // Указываем лист, с которого берем данные клиента

let file = DriveApp.getFileById('1p9htVkm6d6PbNss8PwE6pe-JwDkovxpkLLDjRfIXHIw'); // Указываем код таблицы, которую будем конвертировать в ПДФ и отправлять

let ks = sheet.getLastRow(); // Определяем последнюю строку таблицы, т.е. количество строк

let dataRage = sheet.getRange(2,1,ks,4).getValues(); // Указываем диапазон, с которого берем информацию (с какой строки, с какого столбца, количество строк, количество столбцов)

// Скрываем ненужные нам листы

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Email адреса'), true);

spreadsheet.getActiveSheet().hideSheet();

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Список заказчиков'), true);

spreadsheet.getActiveSheet().hideSheet();

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('данные'), true);

spreadsheet.getActiveSheet().hideSheet();

// Задаем цикл, условие и делаем рассылку

for(let i =0;i < dataRage.length; i++){

// Подготавливаем сводную таблицу:

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Сводная'), true); // Активируем лист со сводной таблицей, и устанавливаем следующие настройки сводной таблицы:

var sourceData = spreadsheet.getRange('\'данные\'!A:L'); // Указываем диапазон данных для построения сводной таблицы

var pivotTable = spreadsheet.getRange('A2').createPivotTable(sourceData); // Указываем ячейку на которой располагается левый верхний угол сводной таблицы

var pivotValue = pivotTable.addPivotValue(2, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA); // Указываем значение для сводной таблицы

pivotValue.setDisplayName('Количество'); // Меняем название колонки со значением сводной таблицы

var pivotGroup = pivotTable.addRowGroup(5); // Выводим колонку в строки сводной таблицы

pivotGroup = pivotTable.addRowGroup(2); // Выводим колонку в строки сводной таблицы

pivotGroup.showTotals(false); // Отключаем строку итогов столбца

pivotGroup = pivotTable.addRowGroup(7); // Выводим колонку в строки сводной таблицы

pivotGroup = pivotTable.addRowGroup(11); // Выводим колонку в строки сводной таблицы

pivotGroup.showTotals(false); // Отключаем строку итогов столбца

var criteria = SpreadsheetApp.newFilterCriteria() // Задаем фильтр для сводной таблицы

.setVisibleValues(['В работе'])

.build();

pivotTable.addFilter(6, criteria);

criteria = SpreadsheetApp.newFilterCriteria() // Задаем фильтр для сводной таблицы

.setVisibleValues([dataRage[i][0]])

.build();

pivotTable.addFilter(5, criteria);

criteria = SpreadsheetApp.newFilterCriteria() // Дублируем предыдущий участок кода. Не знаю почему, но без этого в письме отправляется сводная таблица без последнего фильтра. Лучше решения не нашел

.setVisibleValues([dataRage[i][0]])

.build();

pivotTable.addFilter(5, criteria);

// Подготавливаем и отправляем письмо:

let adress = dataRage[i][2] + "<" + dataRage[i][3] + ">"

let name = dataRage[i][2]

let theme = "Текущее положение дел по заявкам на новые договора"

let body = name + ", дня доброго! Во вложении еженедельный отчет по работе отдела снабжения. Письмо сформировано автоматически. Хорошего дня!<br />" + "<br />" + "С уважением, Снабжение"

if(dataRage[i][0] != ""){ // Ставим условие: если в 1 колонке ячейка не пустая, то отправляем письмо:

MailApp.sendEmail({

to: adress,

subject: theme,

name: "Авторассылка от снабжения",

htmlBody: body,

attachments:[file.getAs('application/pdf')]

});

};

};

// Возвращаем скрытые листы на место

spreadsheet.getSheetByName('Email адреса').showSheet()

.activate();

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Email адреса'), true);

spreadsheet.getSheetByName('Список заказчиков').showSheet()

.activate();

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Список заказчиков'), true);

spreadsheet.getSheetByName('данные').showSheet()

.activate();

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('данные'), true);

};