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

Ирина Чернова avatar | 40
FavoriteLoading В закладки
Автоматизируем Google Документы на Google Apps Script

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

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

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

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

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

Предположим, нам нужно составить отчет о тратах на коммунальные услуги в течение трех лет. У нас есть стопка квитанций и данные из них мы хотим занести в электронные таблицы на 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]

Снимок экрана 2015-11-30 в 18.04.07
Построение графиков

А теперь создадим в документе «Коммунальные счета 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]

Снимок экрана 2015-11-30 в 18.44.45

В строке «.setChartType(Charts.ChartType.BAR)» мы указали тип графика. В Google Docs есть и другие типы графиков:

  • AREA;
  • COLUMN;
  • LINE;
  • PIE;
  • SCATTER;
  • TABLE.

Как выглядит каждый тип графика можно посмотреть, заменив слова BAR на одно из вышеперечисленных в последнем примере.

 

Прокомментировать

🙈 Комментарии 40

  1. Бату Караев avatar
    Бату Караев4 декабря 2015
    0

    Подскажите кто может написать небольшой математический скрипт за деньги для гугл док

    Ирина Чернова avatar
    Ирина Чернова4 декабря 2015
    0

    @Бату Караев, пишите свою задачу отвечу в комментах)

  2. Tsvetkovr avatar
    Tsvetkovr4 декабря 2015
    0

    Ссылка на “Google Apps Script для Docs в примерах” не работает.

  3. Бату Караев avatar
    Бату Караев4 декабря 2015
    0

    Хех!После коммента тут разместил задачу на бирже и уже имею нужный скрипт!
    Фрилансер написал мне код который прибавляет нужный результат вычислений ко всем ячейкам столбца.
    Не ожидал что так быстро решу свою задачу,обошлось мне в 150грн.

  4. SuperSega avatar
    SuperSega4 декабря 2015
    0

    добавил в закладки

    Ирина Чернова avatar
    Ирина Чернова5 декабря 2015
    0

    @SuperSega, спасибо;-)

    trakturisto avatar
    trakturisto8 декабря 2015
    0

    @Ирина Чернова, Вам спасибо. Тема интересная.

    Ирина Чернова avatar
    Ирина Чернова8 декабря 2015
    0

    @trakturisto, :-)

  5. Max K. avatar
    Max K.5 декабря 2015
    0

    Очень интересные темы со скриптами, только примеры какие-то…

    Ирина Чернова avatar
    Ирина Чернова6 декабря 2015
    2

    @Max K., примеры придуманы такие, чтобы в одной компактной статье показать максимум практических приемов использования Google Apps Script) А какие бы Вы примеры предложили?:-)

    Max K. avatar
    Max K.8 декабря 2015
    0

    @Ирина Чернова, Я, честно говоря, хотел ответить именно с примером, но в голову ничего не пришло :)

    Ирина Чернова avatar
    Ирина Чернова8 декабря 2015
    0

    @Max K., да, придумывание примеров самое трудное в работе над статьями про скрипты)

  6. Sergey avatar
    Sergey6 декабря 2015
    0

    Побольше бы комментариев в коде

    Sergey avatar
    Sergey6 декабря 2015
    0

    Еще понять как необходимые функции находить.

    Ирина Чернова avatar
    Ирина Чернова6 декабря 2015
    0

    @Sergey, а какие функции вам нужны?)

  7. Sergey avatar
    Sergey6 декабря 2015
    0

    На данный момент пока не знаю=) Например, таймер поставить между 3мя скриптами. А запуск и остановку скрипта по кнопке из таблицы можно осуществить или это другими способами делается? Не думаю, что корректно его всегда запускать по кнопке плей из редактора.

    Ирина Чернова avatar
    Ирина Чернова6 декабря 2015
    0

    @Sergey, да таймер есть: Utilities.sleep(1000); можно сделать пользовательское меню в Google Docs, так удобнее, ну и можно сделать кнопку на листе)))))

    Ирина Чернова avatar
    Ирина Чернова6 декабря 2015
    0

    @Sergey, вам проще всего создать меню в документе. Это легко.
    1. Объявляем функцию для добавлению меню
    function createMenu() {
    var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
    var entries = [
    {
    name : “Первый скрипт”,
    functionName : “myFirstScript”
    },
    {
    name : “Второй скрипт”,
    functionName : “mySecondScript”
    }
    ];
    currentSheet.addMenu(“Мои скрипты”, entries);
    }

    Ирина Чернова avatar
    Ирина Чернова6 декабря 2015
    0

    @Sergey,
    2. Объявляем функции которые будем вызывать из меню
    function myFirstScript()
    {
    currentSheet.getRange(“A1”).setValue(“Первый скрипт сработал”);

    }
    function mySecondScript()
    {
    currentSheet.getRange(“A2”).setValue(“Второ1 скрипт сработал”);

    }

    Ирина Чернова avatar
    Ирина Чернова6 декабря 2015
    0

    @Sergey,
    3. Объявляем триггер, который будет добавлять меню в панель при каждом открытии документа:

    function onOpen() {
    createMenu();
    }

    Ирина Чернова avatar
    Ирина Чернова6 декабря 2015
    0

    @Sergey, а зачем вам останавливать скрипт?

    Sergey avatar
    Sergey6 декабря 2015
    0

    @Ирина Чернова, ой это мне до следующей недели разбираться) останавливать это если скрипт разбил на 3 дня и после первого дня решил поменять текст.

    Ирина Чернова avatar
    Ирина Чернова7 декабря 2015
    0

    @Sergey, вам, можно поступить так: написать функцию Y которая проверяет какой сегодня день недели и в зависимости от этого вызывает другую функцию (x1, x2 и x3). На открытие документа поставить триггер, который функцию Y запускает. И этот документ при приходе на работу открывать.

    Sergey avatar
    Sergey7 декабря 2015
    0

    @Ирина Чернова, к дате я не хочу привязываться. Может что-то напишу, потом попрошу проверить, если вы не против?)

    Ирина Чернова avatar
    Ирина Чернова7 декабря 2015
    0

    @Sergey, да. пожалуйста. пишите код в комменты потом)))

    Ирина Чернова avatar
    Ирина Чернова7 декабря 2015
    0

    @Sergey, а еще лучше написать bash/shell скрипт, который этот документ открывает и в crone настроить его выполнение.
    Надо только не забыть у файла настроить, что бы его мог открывать любой у кого есть ссылка.

    Sergey avatar
    Sergey7 декабря 2015
    0

    @Ирина Чернова, моих знаний не достаточно для этого понятия)

    Ирина Чернова avatar
    Ирина Чернова7 декабря 2015
    0

    @Sergey, хотя если текст у вас на листе записан, вы можете спокойно менять текст в ячейках, ведь скрипт читает оттуда данный непосредственно в момент выполнения строки, в которой чтение этих данных прописано)

  8. Sergey avatar
    Sergey13 декабря 2015
    0

    Ирина, добрый вечер.

    С функциями меню из 3 пунктов, я так и не понял что делать. Подумал, что проще выбирать каждую функцию из редактора скриптов и запускать отдельно. Смысл такой, у меня супруга на работе занимается информационной рассылкой, я решил ей немного упростить жизнь (а себе усложнить) т. к. она всё делает через копию. Плюс у googlе есть ограничение на рассылку – 500 в день, а человек около 1800, поэтому я решил разделить на 3 части. Что то я сделал неправильно, подскажите?
    function groupDelivery1() {
    // Собираем необходимую информацию из файла
    var info = SpreadsheetApp.getActiveSpreadsheet();
    var subject = info.getSheetByName(“Лист1”).getRange(“B1”).getValue();
    var text = info.getSheetByName(“Лист1”).getRange(“B2”).getValue();
    var id = info.getSheetByName(“Лист1”).getRange(“B3”).getValue();
    // Указываем прикрепляемый файл
    var file = DriveApp.getFileById(id);
    // Открываем базу с адресами
    var tableDoc = SpreadsheetApp.openById(“18scdeAR08HW_7vl38YBaZCIrRo7bNEG9tWARJuSOimU”);
    var Sheet1 = tableDoc.getSheetByName(“Лист1”);
    var dataRange = Sheet1.getRange(1, 1, rowsAmount, 2);
    // Число 300 надо заменить на число адресатов в таблице с адресами
    var rowsAmount = 300;
    var data = dataRange.getValues();
    for (i in data) {
    var row = data[i];
    var emailAddress = row[0];
    // Поддкорректируйте обращение и текст письма под свои нужды
    var messageText = row[1] + text;
    MailApp.sendEmail(emailAddress, subject, messageText, {attachments: [file]});
    }
    }
    function groupDelivery2() {
    // Собираем необходимую информацию из файла
    var info = SpreadsheetApp.getActiveSpreadsheet();
    var subject = info.getSheetByName(“Лист1”).getRange(“B1”).getValue();
    var text = info.getSheetByName(“Лист1”).getRange(“B2”).getValue();
    var id = info.getSheetByName(“Лист1”).getRange(“B3”).getValue();
    // Указываем прикрепляемый файл
    var file = DriveApp.getFileById(id);
    // Открываем базу с адресами
    var tableDoc = SpreadsheetApp.openById(“18scdeAR08HW_7vl38YBaZCIrRo7bNEG9tWARJuSOimU”);
    var Sheet1 = tableDoc.getSheetByName(“Лист1”);
    var dataRange = Sheet1.getRange(1, 1, rowsAmount, 2);
    // Число 300 надо заменить на число адресатов в таблице с адресами
    var rowsAmount = 300;
    var data = dataRange.getValues();
    for (i in data) {
    var row = data[i];
    var emailAddress = row[0];
    // Поддкорректируйте обращение и текст письма под свои нужды
    var messageText = row[1] + text;
    MailApp.sendEmail(emailAddress, subject, messageText, {attachments: [file]});
    }
    }
    function groupDelivery3() {
    // Собираем необходимую информацию из файла
    var info = SpreadsheetApp.getActiveSpreadsheet();
    var subject = info.getSheetByName(“Лист1”).getRange(“B1”).getValue();
    var text = info.getSheetByName(“Лист1”).getRange(“B2”).getValue();
    var id = info.getSheetByName(“Лист1”).getRange(“B3”).getValue();
    // Указываем прикрепляемый файл
    var file = DriveApp.getFileById(id);
    // Открываем базу с адресами
    var tableDoc = SpreadsheetApp.openById(“18scdeAR08HW_7vl38YBaZCIrRo7bNEG9tWARJuSOimU”);
    var Sheet1 = tableDoc.getSheetByName(“Лист1”);
    var dataRange = Sheet1.getRange(1, 1, rowsAmount, 2);
    // Число 300 надо заменить на число адресатов в таблице с адресами
    var rowsAmount = 300;
    var data = dataRange.getValues();
    for (i in data) {
    var row = data[i];
    var emailAddress = row[0];
    // Поддкорректируйте обращение и текст письма под свои нужды
    var messageText = row[1] + text;
    MailApp.sendEmail(emailAddress, subject, messageText, {attachments: [file]});
    }
    }

    Ирина Чернова avatar
    Ирина Чернова14 декабря 2015
    0

    @Sergey, Откройте документ в котором должно быть меню и откройте в нем редактор скриптов и введите в него текст

    function createMenu() {
    var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
    var entries = [
    {
    name : “Первый скрипт”,
    functionName : “groupDelivery1”
    },
    {
    name : “Второй скрипт”,
    functionName : “groupDelivery2”
    }
    ,
    {
    name : “Третий скрипт”,
    functionName : “groupDelivery3”
    }
    ];
    currentSheet.addMenu(“Мои скрипты”, entries);
    }
    function onOpen() {
    createMenu();
    }

    Потом добавьте код ваших функций

    Закройте и откройте документ

  9. Sergey avatar
    Sergey13 декабря 2015
    0

    Только эти параметры Sheet1 = tableDoc.getSheetByName(“Лист1″); я поменяю на Sheet2,3 и Лист2,3 соответственно

    Ирина Чернова avatar
    Ирина Чернова13 декабря 2015
    0

    @Sergey, я сильно не вчитывалась. Пока вижу только одну ошибку:
    var dataRange = Sheet1.getRange(1, 1, rowsAmount, 2);
    // Число 300 надо заменить на число адресатов в таблице с адресами
    var rowsAmount = 300;

    Сначала надо объявить переменную, а потом ее использовать:-)

  10. Sergey avatar
    Sergey13 декабря 2015
    0

    Мда, косяк, поправил. А такой вопрос. Если я запустил скрипт, а потом закрыл браузер он продолжит выполняться?

    Ирина Чернова avatar
    Ирина Чернова14 декабря 2015
    0

    @Sergey, да продолжит выполняться) У Вас получилось свой скрипт запустить или надо дальше смотреть?)

    Sergey avatar
    Sergey14 декабря 2015
    0

    @Ирина Чернова, да, запустился.

    Ирина Чернова avatar
    Ирина Чернова15 декабря 2015
    0

    @Sergey, ура:-)

  11. Belperhat avatar
    Belperhat28 июня 2016
    0

    @Ирина Чернова, ваша статья подтолкнула меня автоматизировать свою работу в excel, через google spreadsheet.
    Я сначала делал по вашим примерам и так как я работаю с большими файлами, очень быстро мои скрипты стали очень медленно работать.
    Я начал рыть почему. А всё по тому что скрипт работает с каждой ячейкой, однако нужно что бы скрипт сразу кинул таблицу (Array) во все ячейки.
    К примеру:

    months = [“Январь”, “Февраль”, “Март”, “Апрель”, “Май”, “Июнь”, “Июль”, “Август”, “Сентябрь”, “Октябрь”, “Ноябрь”, “Декабрь”];
    SpreadsheetApp.getActiveSheet().getRange(1,1,1,12).setValues([months]);

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

    Ирина Чернова avatar
    0

    @Belperhat, спасибо большое, непродумала этот момент( сейчас как раз делаю новую статью про GAS и ваше замечание очень кстати)

  12. kandrew avatar
    kandrew26 января 2018
    0

    Здравствуйте.

    Есть такая практическая задача:
    Есть набор таблиц с одинаковой структурой о записи учеников на курсы в учебный центр.
    Надо сделать сводную таблицу по ФИО.
    Подробнее: например Вася Пупкин ходит на курсы по физике, русскому языку и электронике. Сейчас эти данные – в 3-х разных таблицах. Нужно: свести эти данные в одну таблицу, где было бы указано на какие курсы ходит Вася и все прочие данные из таблиц (кроме названия курсов и финансовых вопросов они совпадают).

    Заранее спасибо за подсказку

  13. Grigory avatar
    Grigory28 июня 2018
    0

    Ирина, здравствуйте!
    А не подскажете, как сделать, чтобы информация из определенных ячеек автоматически (к примеру 1 раз в день) отправлялась на определенный email?

Вы должны авторизоваться или зарегистрироваться для комментирования.

Нашли орфографическую ошибку в новости?

Выделите ее мышью и нажмите Ctrl+Enter.

Как установить аватар в комментариях?

Ответ вот здесь