1. Введение
Добро пожаловать в пятую часть плейлиста с практическими занятиями по основам Apps Script и Google Таблицам. В этом практическом занятии вы научитесь использовать сервис электронных таблиц в Apps Script для построения диаграммы набора данных. Вы также узнаете, как использовать сервис Slides для экспорта диаграммы в новую презентацию в Google Slides.
Чему вы научитесь
- Как построить линейный график с помощью Apps Script.
- Как экспортировать диаграммы в новую презентацию Slides.
- Как представить диалоговые окна пользователю в Таблицах.
Прежде чем начать
Это пятая практическая работа в плейлисте «Основы скриптов приложений с Google Таблицами». Прежде чем приступить к этой практической работе, обязательно выполните предыдущие:
- Макросы и пользовательские функции
- Электронные таблицы, листы и диапазоны
- Работа с данными
- Форматирование данных
Что вам понадобится
- Понимание основных тем Apps Script, рассмотренных в предыдущих практикумах этого плейлиста.
- Базовые знания редактора Apps Script
- Базовые знания Google Таблиц
- Умение читать нотацию листов А1
- Базовые знания JavaScript и его класса
String
2. Настройка
Прежде чем продолжить, вам понадобится электронная таблица с данными. Как и прежде, мы предоставили таблицу, которую вы можете скопировать для этих упражнений. Выполните следующие действия:
- Нажмите на эту ссылку, чтобы скопировать таблицу , а затем нажмите «Сделать копию ». Новая таблица будет помещена в папку Google Диска и получит имя «Копия дат и курсов обмена долларов США».
- Щёлкните по названию таблицы и измените его с «Копия дат и курсов обмена доллара США» на «Даты и курсы обмена доллара США». Ваша таблица должна выглядеть следующим образом, с некоторыми основными данными о различных курсах обмена доллара США на разные даты:
- Чтобы открыть редактор скриптов, нажмите Расширения > Скрипт приложений .
Чтобы сэкономить ваше время, мы добавили небольшой код для настройки пользовательского меню в эту таблицу. Возможно, вы видели это меню при открытии вашей копии таблицы:
С этой таблицей и проектом вы готовы приступить к работе над кодом. Перейдите к следующему разделу, чтобы начать изучать графики и триггеры, управляемые временем.
3. Создайте диаграмму в Таблицах с помощью Apps Script
Предположим, вы хотите создать диаграмму для визуализации набора данных. Вы можете использовать Apps Script для создания, редактирования и вставки диаграмм в Google Таблицы. Диаграмма, встроенная в электронную таблицу, называется встроенной .
Диаграммы используются для визуализации одного или нескольких рядов данных. Встроенные диаграммы обычно используют данные из электронной таблицы. Обычно при обновлении данных в таблице Таблицы автоматически обновляют и диаграмму.
Вы можете использовать Apps Script для создания настраиваемых встраиваемых диаграмм с нуля или для обновления существующих. В этом разделе рассматриваются основы создания встраиваемых диаграмм в Таблицах с помощью Apps Script и сервиса Spreadsheet .
Выполнение
В вашей копии таблицы данных набор данных «Даты и обменные курсы» содержит обменные курсы (за 1 доллар США) различных валют на разные даты. Вам предстоит реализовать функцию Apps Script, которая создаст диаграмму для визуализации части этих данных.
Выполните следующие шаги:
- В редакторе Apps Script добавьте следующую функцию в конец скрипта
Code.gsвашего проекта скрипта после функцииonOpen():
/**
* Creates and inserts an embedded
* line chart into the active sheet.
*/
function createEmbeddedLineChart() {
var sheet = SpreadsheetApp.getActiveSheet();
var chartDataRange = sheet.getRange(
'Dates and USD Exchange Rates dataset!A2:F102');
var hAxisOptions = {
slantedText: true,
slantedTextAngle: 60,
gridlines: {
count: 12
}
};
var lineChartBuilder = sheet.newChart().asLineChart();
var chart = lineChartBuilder
.addRange(chartDataRange)
.setPosition(5, 8, 0, 0)
.setTitle('USD Exchange rates')
.setNumHeaders(1)
.setLegendPosition(Charts.Position.RIGHT)
.setOption('hAxis', hAxisOptions)
.setOption("useFirstColumnAsDomain", true)
.build();
sheet.insertChart(chart);
}
- Сохраните свой проект сценария.
Обзор кода
Добавленный вами код реализует функцию, вызываемую пунктом меню «Набор данных «Даты и курсы валют»» в разделе «Диаграмма» , для создания простого линейного графика. Давайте рассмотрим код.
Первые строки задают следующие три переменные:
-
sheet: ссылка на текущий активный лист. -
chartDataRange: диапазон данных, который мы хотим визуализировать. В коде используется нотация A1 для указания диапазона, охватывающего ячейки A2–F102 на листе с именем «Dates and USD Exchange Rates dataset» . Указывая конкретное имя листа, мы гарантируем, что пункт меню будет работать, даже если активен другой лист, поскольку диапазон всегда охватывает позицию данных. Если начать со строки 2, то мы включим заголовки столбцов, и в диаграмму будут включены только 100 последних дат (строк). -
hAxisOptions: базовый объект JavaScript, содержащий некоторые настройки, которые код использует для настройки внешнего вида горизонтальной оси. В частности, они задают наклон текстовых меток горизонтальной оси под углом 60 градусов, а количество вертикальных линий сетки — 12.
Следующая строка создаёт объект конструктора линейной диаграммы . Встроенные диаграммы в Apps Script строятся с использованием шаблона проектирования «Конструктор» . Полное объяснение этого шаблона проектирования выходит за рамки данной практической работы, поэтому пока просто отметим, что сервис Spreadsheet предоставляет несколько классов EmbeddedChartBuilder . Чтобы создать диаграмму, ваш код сначала создаёт встроенный объект конструктора диаграмм, использует его методы для определения настроек диаграммы, а затем вызывает метод build() для создания конечного объекта EmbeddedChart . Ваш код никогда не изменяет объект EmbeddedChart напрямую, поскольку вся конфигурация диаграммы управляется через классы конструктора.
Служба электронных таблиц предоставляет родительский класс EmbeddedChartBuilder и несколько дочерних классов-конструкторов (например, EmbeddedLineChartBuilder ), которые наследуют его. Дочерние классы позволяют Apps Script предоставлять методы настройки диаграмм конструкторов, применимые только к определённым типам диаграмм. Например, класс EmbeddedPieChartBuilder предоставляет метод set3D() , применимый только к круговым диаграммам.
В вашем коде эта строка создает переменную объекта построителя lineChartBuilder :
var lineChartBuilder = sheet.newChart().asLineChart();
Код вызывает метод Sheet.newChart() для создания объекта EmbeddedChartBuilder , а затем использует EmbeddedChartBuilder.asLineChart() для установки типа построителя EmbeddedLineChartBuilder .
Затем код строит диаграмму с помощью lineChartBuilder. Эта часть кода представляет собой последовательность вызовов методов для определения настроек диаграммы, за которыми следует вызов build() для её создания. Как вы видели в предыдущих практических занятиях, в коде используется цепочка методов для удобства восприятия. Вот что делают вызовы методов:
-
addRange(range): определяет диапазон данных, отображаемых на диаграмме. -
setPosition(anchorRowPos, anchorColPos, offsetX, offsetY): определяет положение диаграммы на листе. В данном случае код вставляет верхний левый угол диаграммы в ячейку H5. -
setTitle(title): устанавливает заголовок диаграммы. -
setNumHeaders(headers): определяет, сколько строк или столбцов в диапазоне данных следует рассматривать как заголовки. В данном случае код использует первую строку диапазона данных в качестве заголовков, то есть текст в этой строке используется в качестве меток для отдельных рядов данных на диаграмме. -
setLegendPosition(position): перемещает легенду диаграммы в правую часть. Этот метод использует перечисление Charts.Position в качестве параметра. -
setOption(option, value): задаёт сложные параметры диаграммы. В этом коде параметрhAxisприсваивается объектуhAxisOptions. С помощью этого метода можно задать несколько параметров. Параметры и возможные значения для каждого типа диаграммы описаны в галерее диаграмм API Charts . Например, параметры, которые можно задать для линейных диаграмм, описаны в разделе «Параметры конфигурации линейных диаграмм» . МетодsetOption(option, value)— это сложная тема, поэтому рекомендуется воздержаться от его использования, пока вы не освоитесь с созданием диаграмм в Apps Script. -
build(): создает и возвращает объектEmbeddedChart, используя указанные выше настройки.
Наконец, код вызывает Sheet.insertChart(chart) , чтобы поместить построенную диаграмму на активный лист.
Результаты
Вы можете увидеть функцию форматирования в действии, выполнив следующие действия:
- Если вы еще этого не сделали, сохраните свой проект скрипта в редакторе Apps Script.
- Щелкните пункт меню «Представленный набор данных» > «Дата и курсы обмена долларов США» .
Теперь ваш скрипт размещает новую диаграмму справа от ваших данных:
Поздравляем! Вы создали встроенную линейную диаграмму с помощью Apps Script. В следующем разделе мы расскажем, как экспортировать диаграмму в Google Презентации.
4. Экспортируйте свои диаграммы в слайды.
Одно из главных преимуществ Apps Script заключается в том, что он позволяет легко переносить данные из одного приложения Google Workspace в другое. Большинство этих приложений имеют специальный сервис Apps Script, аналогичный сервису электронных таблиц . Например, в Gmail есть сервис Gmail , в Google Docs — сервис документов , а в Google Slides — сервис слайдов . Благодаря всем этим встроенным сервисам вы можете извлекать данные из одного приложения, обрабатывать их и записывать результат в другое.
В этом разделе вы узнаете, как экспортировать все встроенные диаграммы из электронных таблиц Google в новую презентацию Google Slides. Вы также узнаете о двух способах отображения пользовательских сообщений в Таблицах.
Выполнение
Здесь вы реализуете функцию, вызываемую пунктом меню «Представить набор данных» > «Экспортировать диаграммы в слайды» . Выполните следующие действия:
- В редакторе скриптов приложений добавьте следующую функцию в конец скрипта
Code.gsвашего проекта скрипта после функцииcreateEmbeddedLineChart():
/**
* Create a Slides presentation and export
* all the embedded charts in this spreadsheet
* to it, one chart per slide.
*/
function exportChartsToSlides() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Fetch a list of all embedded charts in this
// spreadsheet.
var charts = [];
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
charts = charts.concat(sheets[i].getCharts());
}
// If there aren't any charts, display a toast
// message and return without doing anything
// else.
if (charts.length == 0) {
ss.toast('No charts to export!');
return;
}
// Create a Slides presentation, removing the default
// title slide.
var presentationTitle =
ss.getName() + " Presentation";
var slides = SlidesApp.create(presentationTitle);
slides.getSlides()[0].remove();
// Add charts to the presentation, one chart per slide.
var position = {left: 40, top: 30};
var size = {height: 340, width: 430};
for (var i = 0; i < charts.length; i++) {
var newSlide = slides.appendSlide();
newSlide.insertSheetsChart(
charts[i],
position.left,
position.top,
size.width,
size.height);
}
// Create and display a dialog telling the user where to
// find the new presentation.
var slidesUrl = slides.getUrl();
var html = "<p>Find it in your home Drive folder:</p>"
+ "<p><a href=\"" + slidesUrl + "\" target=\"_blank\">"
+ presentationTitle + "</a></p>";
SpreadsheetApp.getUi().showModalDialog(
HtmlService.createHtmlOutput(html)
.setHeight(120)
.setWidth(350),
"Created a presentation!"
);
}
- Сохраните свой проект сценария.
Обзор кода
Этот код может оказаться короче, чем вы ожидали. Давайте рассмотрим, что он делает, разбив его на пять частей:
1: Получите диаграммы
Первые несколько строк выполняют поиск всех встроенных диаграмм в активной электронной таблице и собирают их в массив charts . Эти строки используют методы Spreadsheet.getSheets() и Sheet.getCharts() для получения списков листов и диаграмм. Метод JavaScript Array.concat() используется для добавления списка диаграмм с каждого листа в charts .
2: Проверьте наличие диаграмм для экспорта.
Код проверяет наличие диаграмм для экспорта. Мы не хотим создавать пустую презентацию, поэтому, если диаграмм нет, код создаёт всплывающее сообщение с помощью Spreadsheet.toast(message) . Это небольшое диалоговое окно, которое появляется в правом нижнем углу Таблиц, отображается несколько секунд, а затем исчезает:
Если диаграмм для экспорта нет, код создаёт всплывающее сообщение и завершает работу, не выполняя никаких дальнейших действий. Если диаграммы для экспорта есть, код продолжает создание презентации в следующих нескольких строках.
3: Создайте презентацию
Переменная presentationTitle создаётся для хранения имени файла новой презентации. Она задаётся как имя электронной таблицы с добавлением слова « Presentation » в конце. Затем код вызывает метод SlidesApp.create(name) сервиса Slides для создания презентации.
Новые презентации создаются с одним пустым слайдом. Нам это не нужно, поэтому код удаляет его с помощью Presentation.getSlides() и Slide.remove() .
4: Экспортируйте диаграммы
В следующем разделе код определяет position и size объектов JavaScript, чтобы задать место размещения импортированных диаграмм на слайде, а также размер диаграммы (в пикселях).
Код циклически проходит по всем диаграммам в списке. Для каждой диаграммы создаётся новый слайд newSlide с помощью Presentation.appendSlide() , добавляя слайд в конец презентации. Метод Slide.insertSheetsChart(sourceChart, left, top, width, height) используется для импорта диаграммы на слайд с заданными position и size .
5: Поделитесь местом проведения презентации
Наконец, код должен сообщить пользователю, где находится новая презентация, желательно в виде ссылки, по которой можно её открыть. Для этого код использует HTML сервис Apps Script для создания настраиваемого модального диалогового окна . Модальные диалоговые окна (также известные как настраиваемые диалоговые окна в Apps Script) — это окна, которые появляются поверх интерфейса Таблиц. При отображении настраиваемые диалоговые окна блокируют взаимодействие пользователя с Таблицами.
Для создания настраиваемого диалогового окна коду необходим HTML-код, определяющий его содержимое. Он предоставляется в переменной html . Содержимое включает короткий абзац и гиперссылку. Гиперссылка — это переменная presentationTitle , связанная с URL-адресом презентации, предоставленным методом Presentation.getUrl() . Гиперссылка также использует атрибут target="_blank" , поэтому презентация открывается в новой вкладке браузера, а не в диалоговом окне.
HTML-код преобразуется в объект HtmlOutput методом HtmlService.createHtmlOutput(html) . Объект HtmlOutput позволяет коду задать размер настраиваемого диалогового окна с помощью HtmlOutput.setHeight(height) и HtmlOutput.setWidth(width) .
После создания htmlOutput код использует метод Ui.showModalDialog(htmlOutput, title) для отображения диалогового окна с заданным заголовком.
Результаты
Теперь, когда вы реализовали второй пункт меню, вы можете увидеть его в действии. Чтобы протестировать функцию exportChartsToSlides() :
- Если вы еще этого не сделали, сохраните свой проект скрипта в редакторе Apps Script.
- Откройте электронную таблицу и выберите пункт меню «Представить набор данных» > «Диаграмма: набор данных «Даты и курсы обмена долларов США»» , чтобы создать диаграмму для экспорта. Она будет привязана к ячейке H5 на активном листе.
- Выберите пункт меню «Представить набор данных» > «Экспортировать диаграммы в слайды» . Возможно, вам потребуется повторно авторизовать скрипт.
- Вы должны увидеть, как ваш скрипт обрабатывает запрос и отображает настраиваемое диалоговое окно.
- Чтобы открыть новую презентацию Slides, нажмите ссылку Презентация дат и курсов обмена долларов США :
При желании вы также можете добавить в свою электронную таблицу больше диаграмм и повторно выбрать пункт меню, чтобы создать презентацию с несколькими слайдами.
Теперь вы можете экспортировать диаграммы, созданные в Таблицах, в презентации Slides. Вы также можете написать код для создания собственного диалогового окна.
Вы выполнили последнее упражнение этой лабораторной работы. Перейдите к следующему разделу, чтобы закрепить полученные знания.
5. Заключение
Поздравляем! Вы завершили эту практическую работу и весь плейлист «Основы Apps Script в Google Таблицах». Вы можете использовать принципы, изложенные в этом плейлисте, чтобы расширить свои знания о Таблицах и изучить возможности Apps Script.
Оказался ли этот практический практикум полезным?
Что вы узнали
- Как построить встроенную линейную диаграмму с помощью Apps Script.
- Как отображать всплывающие сообщения и настраиваемые диалоговые окна для пользователя в Таблицах.
- Как экспортировать диаграмму в новую презентацию Slides.
Что дальше?
Вы успешно завершили этот плейлист. Однако вам ещё многое предстоит узнать об Apps Script.
Ознакомьтесь со следующими ресурсами:
- Документация разработчика скриптов приложений
- Руководства по скриптам приложений
- Справочная документация по скриптам приложений
- Вопросы по скриптам приложений на StackOverflow
- Примеры кода скриптов приложений на GitHub
Удачного написания сценариев!