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]
В строке «.setChartType(Charts.ChartType.BAR)» мы указали тип графика. В Google Docs есть и другие типы графиков:
- AREA;
- COLUMN;
- LINE;
- PIE;
- SCATTER;
- TABLE.
Как выглядит каждый тип графика можно посмотреть, заменив слова BAR на одно из вышеперечисленных в последнем примере.
- 4 лайфхака Gmail и Google Apps Script
- Google Apps Script для Docs в примерах
- Планируем жизнь с Google Calendar и Google Apps Script
- Простые автоматизации для Google Drive
(5 голосов, общий рейтинг: 4.60 из 5)
40 комментариев
Форум →Подскажите кто может написать небольшой математический скрипт за деньги для гугл док
@Бату Караев, пишите свою задачу отвечу в комментах)
Ссылка на “Google Apps Script для Docs в примерах” не работает.
Хех!После коммента тут разместил задачу на бирже и уже имею нужный скрипт!
Фрилансер написал мне код который прибавляет нужный результат вычислений ко всем ячейкам столбца.
Не ожидал что так быстро решу свою задачу,обошлось мне в 150грн.
добавил в закладки
@SuperSega, спасибо;-)
@Ирина Чернова, Вам спасибо. Тема интересная.
@trakturisto, :-)
Очень интересные темы со скриптами, только примеры какие-то…
@Max K., примеры придуманы такие, чтобы в одной компактной статье показать максимум практических приемов использования Google Apps Script) А какие бы Вы примеры предложили?:-)
@Ирина Чернова, Я, честно говоря, хотел ответить именно с примером, но в голову ничего не пришло :)
@Max K., да, придумывание примеров самое трудное в работе над статьями про скрипты)
Побольше бы комментариев в коде
Еще понять как необходимые функции находить.
@Sergey, а какие функции вам нужны?)
На данный момент пока не знаю=) Например, таймер поставить между 3мя скриптами. А запуск и остановку скрипта по кнопке из таблицы можно осуществить или это другими способами делается? Не думаю, что корректно его всегда запускать по кнопке плей из редактора.
@Sergey, да таймер есть: Utilities.sleep(1000); можно сделать пользовательское меню в Google Docs, так удобнее, ну и можно сделать кнопку на листе)))))
@Sergey, вам проще всего создать меню в документе. Это легко.
1. Объявляем функцию для добавлению меню
function createMenu() {
var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [
{
name : “Первый скрипт”,
functionName : “myFirstScript”
},
{
name : “Второй скрипт”,
functionName : “mySecondScript”
}
];
currentSheet.addMenu(“Мои скрипты”, entries);
}
@Sergey,
2. Объявляем функции которые будем вызывать из меню
function myFirstScript()
{
currentSheet.getRange(“A1”).setValue(“Первый скрипт сработал”);
}
function mySecondScript()
{
currentSheet.getRange(“A2”).setValue(“Второ1 скрипт сработал”);
}
@Sergey,
3. Объявляем триггер, который будет добавлять меню в панель при каждом открытии документа:
function onOpen() {
createMenu();
}
@Sergey, а зачем вам останавливать скрипт?
@Ирина Чернова, ой это мне до следующей недели разбираться) останавливать это если скрипт разбил на 3 дня и после первого дня решил поменять текст.
@Sergey, вам, можно поступить так: написать функцию Y которая проверяет какой сегодня день недели и в зависимости от этого вызывает другую функцию (x1, x2 и x3). На открытие документа поставить триггер, который функцию Y запускает. И этот документ при приходе на работу открывать.
@Ирина Чернова, к дате я не хочу привязываться. Может что-то напишу, потом попрошу проверить, если вы не против?)
@Sergey, да. пожалуйста. пишите код в комменты потом)))
@Sergey, а еще лучше написать bash/shell скрипт, который этот документ открывает и в crone настроить его выполнение.
Надо только не забыть у файла настроить, что бы его мог открывать любой у кого есть ссылка.
@Ирина Чернова, моих знаний не достаточно для этого понятия)
@Sergey, хотя если текст у вас на листе записан, вы можете спокойно менять текст в ячейках, ведь скрипт читает оттуда данный непосредственно в момент выполнения строки, в которой чтение этих данных прописано)
Ирина, добрый вечер.
С функциями меню из 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]});
}
}
@Sergey, Откройте документ в котором должно быть меню и откройте в нем редактор скриптов и введите в него текст
function createMenu() {
var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [
{
name : “Первый скрипт”,
functionName : “groupDelivery1”
},
{
name : “Второй скрипт”,
functionName : “groupDelivery2”
}
,
{
name : “Третий скрипт”,
functionName : “groupDelivery3”
}
];
currentSheet.addMenu(“Мои скрипты”, entries);
}
function onOpen() {
createMenu();
}
Потом добавьте код ваших функций
Закройте и откройте документ
Только эти параметры Sheet1 = tableDoc.getSheetByName(“Лист1″); я поменяю на Sheet2,3 и Лист2,3 соответственно
@Sergey, я сильно не вчитывалась. Пока вижу только одну ошибку:
var dataRange = Sheet1.getRange(1, 1, rowsAmount, 2);
// Число 300 надо заменить на число адресатов в таблице с адресами
var rowsAmount = 300;
Сначала надо объявить переменную, а потом ее использовать:-)
Мда, косяк, поправил. А такой вопрос. Если я запустил скрипт, а потом закрыл браузер он продолжит выполняться?
@Sergey, да продолжит выполняться) У Вас получилось свой скрипт запустить или надо дальше смотреть?)
@Ирина Чернова, да, запустился.
@Sergey, ура:-)
@Ирина Чернова, ваша статья подтолкнула меня автоматизировать свою работу в excel, через google spreadsheet.
Я сначала делал по вашим примерам и так как я работаю с большими файлами, очень быстро мои скрипты стали очень медленно работать.
Я начал рыть почему. А всё по тому что скрипт работает с каждой ячейкой, однако нужно что бы скрипт сразу кинул таблицу (Array) во все ячейки.
К примеру:
months = [“Январь”, “Февраль”, “Март”, “Апрель”, “Май”, “Июнь”, “Июль”, “Август”, “Сентябрь”, “Октябрь”, “Ноябрь”, “Декабрь”];
SpreadsheetApp.getActiveSheet().getRange(1,1,1,12).setValues([months]);
Таким образом скрипт работает намного быстрей и в моём случае вылетает очень редко.
@Belperhat, спасибо большое, непродумала этот момент( сейчас как раз делаю новую статью про GAS и ваше замечание очень кстати)
Здравствуйте.
Есть такая практическая задача:
Есть набор таблиц с одинаковой структурой о записи учеников на курсы в учебный центр.
Надо сделать сводную таблицу по ФИО.
Подробнее: например Вася Пупкин ходит на курсы по физике, русскому языку и электронике. Сейчас эти данные – в 3-х разных таблицах. Нужно: свести эти данные в одну таблицу, где было бы указано на какие курсы ходит Вася и все прочие данные из таблиц (кроме названия курсов и финансовых вопросов они совпадают).
Заранее спасибо за подсказку
Ирина, здравствуйте!
А не подскажете, как сделать, чтобы информация из определенных ячеек автоматически (к примеру 1 раз в день) отправлялась на определенный email?
Нашли орфографическую ошибку в новости?
Выделите ее мышью и нажмите Ctrl+Enter.Как быть, если iPhone после обновления до iOS 11 начал сам принимать звонки
Почему не работает режим Картинка в картинке для YouTube на iPhone
Как поделиться геопозицией iPhone в приложении iMessage
Как на iPhone обновить номер телефона, если не проходит оплата в App Store
Стоит ли обновлять iPhone 5s с iOS 9 до iOS 12
Как удобнее всего читать Twitter на Mac
Как включить звуковой сигнал при включении и отключении iPhone
Как настроить параметры вида в Finder отдельно для разных папок?