Dev Автоматизируем Google Документы на Google Apps Script

3 примера кода, которые стоит сохранить на будущее.

Продолжаем рассказывать о приемах автоматизации работы с сервисами Google. Многие думают о Google Docs, как о приложении предназначенном для просмотра таблиц, присланных по почте. Но это не так. У этого табличного процессора есть больше сотни встроенных формул для расчетов, обширный набор инструментов для форматирования и обработки данных.

Одно из важных достоинств Google Docs, не считая коллективной работы над документом из всех уголков планеты — возможность использовать Google Apps Script для автоматизации и интеграции с другими сервисами Google.

Попробовать рецепты из статьи очень просто:

Заходим на docs.google.com и авторизируемся;
Создаем новую электронную таблицу;
Открываем Инструменты -> Редактор скриптов;
Вбиваем в окошко код из примеров и вносим свои правки;
Нажимаем на кнопку Запустить и даем скрипту все разрешения, которые он попросит.

Предположим, нам нужно составить отчет о тратах на коммунальные услуги в течение трех лет. У нас есть стопка квитанций и данные из них мы хотим занести в электронные таблицы на Google Sheet. Для каждого года нужно создать отдельный файл с 12 листами-месяцами. На каждом листе сделать шаблон для записи значений и добавить немного дизайна. А когда все данные будут внесены, построить график для их визуализации.

Такая структура документа не слишком удобна для хранения данных, но рассмотрение этой задачи поможет понять, каким образом Google Apps Script взаимодействует с электронными таблицами.

Генерация документов

Создадим документы «Коммунальные счета 2012», «Коммунальные счета 2013» и «Коммунальные счета 2014». В каждом документе сделаем листы с названиями месяцев, на каждом листе в первом столбце запишем виды коммунальных услуг.

[jscript]function createTables() {
years=["2012","2013","2014"];
months = ["Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь"];
services = ["Газ", "Свет", "Вода", "Квартплата", "Телефон", "Интернет", "Охрана"];
for (j=0;j<=years.length-1;j++)
{
tableDoc = SpreadsheetApp.create("Коммунальные счета " + years[j]);
for (i=0;i<=months.length-1;i++)
{
if (i==0)
{
currentSheet = tableDoc.getActiveSheet();
currentSheet.setName(months[i]);
}
else
{
currentSheet = tableDoc.insertSheet();
currentSheet.setName(months[i]);
}
for (z=0;z<=services.length-1;z++)
{
row=z+1;
currentSheet.getRange(‘A’ + row ).setValue(services[z]);
}
}
}
}
[/jscript]

Выполнение скрипта может занять пару минут, наберитесь немного терпения.

Редактирование файлов

Создавать файлы мы научились, а теперь разберемся как открывать их и редактировать. Нам нужно на всех листах файла «Коммунальные счета 2014» покрасить заполненные ячейки первого столбца в голубой цвет и сделать так, чтобы в первой незаполненной его ячейке было слово «Итого», а ячейке справа от него формула для вычисления этого значения.

Перед выполнением скрипта нам нужно открыть Google Drive и выяснить ID нужного нам документа.

[jscript]function editTables() {
months = ["Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь"];
services = ["Газ", "Свет", "Вода", "Квартплата", "Телефон", "Интернет", "Охрана"];
tableDoc = SpreadsheetApp.openById("1RzIv25ZnSEUI3cyu-h-x0JKnkScPEA5WwiEDqX-stEE");
for (i=0;i<=months.length-1;i++)
{
if (i==0)
{
currentSheet = tableDoc.getActiveSheet();
}
else
{
currentSheet = tableDoc.getSheetByName(months[i]);
}
for (z=0;z<=services.length;z++)
{
row =z+1;
currentSheet.getRange("A"+row).setBackground("#75aad8");
}
lastRow = services.length+2;
currentSheet.getRange("A" + lastRow).setValue("Итого:");
currentSheet.getRange("B" + lastRow).setValue("=sum(b1:b"+(services.length+1)+")");
}
}[/jscript]

Построение графиков

А теперь создадим в документе «Коммунальные счета 2012» еще один новый лист и построим на нем диаграмму для визуального сравнения итоговых счетов по месяцам.

[jscript]function insertChart() {
months = ["Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь"];
services = ["Газ", "Свет", "Вода", "Квартплата", "Телефон", "Интернет", "Охрана"];
tableDoc = SpreadsheetApp.openById("1RzIv25ZnSEUI3cyu-h-x0JKnkScPEA5WwiEDqX-stEE");
endSums = new Array();
for (i=0;i<=months.length-1;i++)
{
if (i==0)
{
currentSheet = tableDoc.getActiveSheet();
}
else
{
currentSheet = tableDoc.getSheetByName(months[i]);
}
lastRow = services.length+2;
endSums[i] = currentSheet.getRange("B" + lastRow).getValue();
}
sheetIndex = months.length + 1;
currentSheet = tableDoc.insertSheet(sheetIndex);
currentSheet.setName("Итоги года");
for (z=0;z<=months.length-1;z++)
{
row = z+1;
currentSheet.getRange("A" + row).setValue(months[z]);
currentSheet.getRange("B" + row).setValue(endSums[z]);
}
var chart = currentSheet.newChart()
.setChartType(Charts.ChartType.BAR)
.addRange(currentSheet.getRange("A1:B" + months.length))
.setPosition(1, 3, 0, 0)
.setOption("title", "Расход за год")
.build();
currentSheet.insertChart(chart);
}[/jscript]