Основы скрипта приложений с Google Sheets # 5: Диаграмма и представление данных в слайдах

1. Введение

Добро пожаловать в пятую часть плейлиста «Основы скриптов приложений с кодовой лабораторией Google Sheets». В этой лабораторной работе вы узнаете, как использовать службу электронных таблиц в Apps Script для составления диаграммы набора данных. Вы также узнаете, как использовать сервис Slides для экспорта диаграммы в новую презентацию в Google Slides.

Что вы узнаете

  • Как построить линейную диаграмму с помощью Apps Script.
  • Как экспортировать диаграммы в новую презентацию Slides.
  • Как представить диалоги пользователю в Sheets.

Прежде чем вы начнете

Это пятая лаборатория кода в плейлисте Fundamentals of Apps Script with Google Sheets. Прежде чем приступить к этой лабораторной работе, обязательно выполните предыдущие лабораторные работы:

  1. Макросы и пользовательские функции
  2. Электронные таблицы, листы и диапазоны
  3. Работа с данными
  4. Форматирование данных

Что вам понадобится

  • Понимание основных тем сценариев приложений, изученных в предыдущих лабораторных работах этого плейлиста.
  • Базовое знакомство с редактором скриптов приложений
  • Базовое знакомство с Google Sheets
  • Умение читать листы формата А1.
  • Базовое знакомство с JavaScript и его классом String

2. Настроить

Прежде чем продолжить, вам нужна электронная таблица с некоторыми данными. Как и прежде, мы предоставили лист данных, который вы можете скопировать для этих упражнений. Выполните следующие действия:

  1. Щелкните эту ссылку, чтобы скопировать лист данных, а затем щелкните Сделать копию . Новая таблица помещается в папку на вашем Google Диске и называется «Копия дат и курсов обмена долларов США».
  2. Щелкните заголовок электронной таблицы и измените его с «Копия дат и курсов обмена долларов США» на «Даты и курсы обмена долларов США». Ваш лист должен выглядеть так, с некоторой базовой информацией о различных обменных курсах доллара США в разные даты:

45a3e8814ecb07fc.png

  1. Чтобы открыть редактор сценариев, щелкните Расширения > Скрипт приложений .

Чтобы сэкономить ваше время, мы включили в эту таблицу небольшой код для настройки пользовательского меню. Возможно, вы видели, как появляется меню, когда открывается ваша копия электронной таблицы:

9b9caf6c1e9de34b.png

С этой электронной таблицей и проектом вы готовы начать лабораторию кода. Перейдите к следующему разделу, чтобы начать изучение графиков и триггеров, управляемых временем.

3. Создайте диаграмму в Sheets с помощью Apps Script

Предположим, вы хотите разработать конкретную диаграмму для визуализации набора данных. На самом деле вы можете использовать Apps Script для создания, редактирования и вставки диаграмм в Google Sheets. Когда диаграмма содержится в электронной таблице, она называется встроенной диаграммой .

Диаграммы используются для визуализации одного или нескольких рядов данных. Для встроенных диаграмм данные, которые они представляют, обычно поступают из электронной таблицы. Обычно при обновлении данных в электронной таблице Таблицы также автоматически обновляют диаграмму.

Вы можете использовать Apps Script для создания настраиваемых встроенных диаграмм с нуля или для обновления существующих диаграмм. В этом разделе представлены основы построения встроенных диаграмм в Таблицах с помощью Apps Script и службы Spreadsheet .

Реализация

В вашей копии электронной таблицы данных набор данных «Даты и обменные курсы» показывает обменные курсы (за 1 доллар США) различных валют в разные даты. Вы будете реализовывать функцию скрипта приложений, которая создает диаграмму для визуализации части этих данных.

Выполните следующие действия:

  1. В редакторе сценариев приложений добавьте следующую функцию в конец сценария 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);  
}
  1. Сохраните проект сценария.

Обзор кода

Код, который вы добавили, реализует функцию, вызываемую пунктом меню Chart «Даты и набор данных об обменных курсах доллара США» , для создания базовой линейной диаграммы. Давайте рассмотрим код.

Первые строки устанавливают следующие три переменные:

  • sheet : ссылка на текущий активный лист.
  • chartDataRange : диапазон данных, который мы хотим визуализировать. В коде используется нотация A1 для указания диапазона ячеек с A2 по F102 на листе с именем Dates and USD Exchange Rates dataset . Специально называя лист, мы гарантируем, что пункт меню работает, даже если активен другой лист, поскольку диапазон всегда охватывает позицию данных. Начало со строки 2 означает, что мы включаем заголовки столбцов, и мы будем отображать только 100 самых последних дат (строк).
  • hAxisOptions : базовый объект JavaScript, который включает некоторую информацию о настройках, которую код использует для настройки внешнего вида горизонтальной оси. В частности, они устанавливают текстовые метки горизонтальной оси под углом 60 градусов, а количество вертикальных линий сетки — 12.

Следующая строка создает объект построителя линейной диаграммы . Встроенные диаграммы в Apps Script создаются с использованием шаблона проектирования Builder . Полное объяснение этого шаблона проектирования выходит за рамки этой лаборатории кода, поэтому сейчас просто поймите, что служба 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 . Есть несколько параметров, которые вы можете установить с помощью этого метода. Параметры и возможные значения для каждого типа диаграммы задокументированы в галерее диаграмм Charts API . Например, параметры, которые можно установить для линейных диаграмм, задокументированы в разделе Параметры конфигурации линейных диаграмм . Метод setOption(option, value) является сложной темой, поэтому вы можете не использовать его, пока не освоитесь с созданием диаграмм в Apps Script.
  • build() : создает и возвращает объект EmbeddedChart с указанными выше настройками.

Наконец, код вызывает Sheet.insertChart(chart) , чтобы поместить построенную диаграмму на активный лист.

Полученные результаты

Вы можете увидеть свою функцию форматирования в действии, выполнив следующие действия:

  1. Если вы еще этого не сделали, сохраните проект сценария в редакторе сценариев приложений.
  2. Щелкните пункт меню Текущий набор данных > Диаграмма «Набор данных дат и курсов валют» .

Теперь ваш скрипт помещает новую диаграмму справа от ваших данных:

bbf856699b6d2b45.gif

Поздравляем, вы построили встроенную линейную диаграмму с помощью Apps Script. В следующем разделе вы узнаете, как экспортировать диаграмму в Google Slides.

4. Экспортируйте свои диаграммы в слайды

Одно из сильных сторон Apps Script заключается в том, что он позволяет легко перемещать данные из одного приложения Google Workspace в другое. Большинство этих приложений имеют специальную службу сценариев приложений, аналогичную службе электронных таблиц . Например, в Gmail есть сервис Gmail , в Google Docs — сервис Document , а в Google Slides — сервис Slides . Со всеми этими встроенными службами вы можете извлекать данные из одного приложения, обрабатывать их и записывать результат в другое.

В этом разделе вы узнаете, как экспортировать каждую встроенную диаграмму из электронной таблицы Google в новую презентацию Google Slides. Вы также увидите два способа отображения пользовательских сообщений в Таблицах.

Реализация

Здесь вы реализуете функцию, вызываемую пунктом меню Текущий набор данных > Экспорт диаграмм в слайды . Выполните следующие действия:

  1. В редакторе сценариев приложений добавьте следующую функцию в конец сценария 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. Сохраните проект сценария.

Обзор кода

Этот код может быть короче, чем вы ожидали. Давайте посмотрим, что он делает, разбив код на пять разделов:

1: Получить диаграммы

Первые несколько строк выполняют поиск в активной электронной таблице, чтобы найти все встроенные диаграммы и собрать их в массив charts . Эти строки используют метод Spreadsheet.getSheets() и метод Sheet.getCharts() для получения списков листов и диаграмм. Метод JavaScript Array.concat() используется для добавления списка диаграмм с каждого листа в charts .

2: Проверьте, есть ли графики для экспорта

Код проверяет, есть ли какие-либо диаграммы для экспорта. Мы хотим избежать создания пустой презентации, поэтому, если нет диаграмм, код вместо этого создает всплывающее сообщение, используя Spreadsheet.toast(message) . Это небольшое диалоговое окно просмотра, которое появляется в правом нижнем углу Таблиц, остается на несколько секунд, а затем исчезает:

db7e87dcb8010bef.gif

Если диаграмм для экспорта нет, код создает всплывающее сообщение и завершает работу, ничего не делая. Если есть диаграммы для экспорта, код продолжает создавать презентацию в следующих нескольких строках.

3: Создайте презентацию

Переменная presentationTitle создается для хранения имени файла новой презентации. Оно задается как имя электронной таблицы с добавлением « Presentation » в конце. Затем код вызывает метод сервиса Slides SlidesApp.create(name) для создания презентации.

Новые презентации создаются с одним пустым слайдом. Мы не хотим этого в нашей презентации, поэтому код удаляет его с помощью 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-адресом presentationTitle , предоставленным Presentation.getUrl() . Гиперссылка также использует атрибут target="_blank" , поэтому презентация открывается в новой вкладке браузера, а не в диалоговом окне.

HTML анализируется в объект HtmlOutput методом HtmlService.createHtmlOutput(html) . Объект HtmlOutput позволяет коду задавать размер настраиваемого диалогового окна с помощью HtmlOutput.setHeight(height) и HtmlOutput.setWidth(width) .

После htmlOutput код использует метод Ui.showModalDialog(htmlOutput, title) для отображения диалогового окна с заданным заголовком.

Полученные результаты

Теперь, когда вы реализовали второй пункт меню, вы можете увидеть его в действии. Чтобы протестировать exportChartsToSlides() :

  1. Если вы еще этого не сделали, сохраните проект сценария в редакторе сценариев приложений.
  2. Откройте электронную таблицу и щелкните пункт меню « Представленный набор данных» > «Диаграмма» «Набор данных о датах и ​​курсах валют» , чтобы создать диаграмму для экспорта. Он будет привязан к ячейке H5 на активном листе.
  3. Щелкните пункт меню Текущий набор данных > Экспорт диаграмм в слайды . Вас могут попросить повторно авторизовать сценарий.
  4. Вы должны увидеть, как ваш скрипт обрабатывает запрос и отображает пользовательский диалог.
  5. Чтобы открыть новую презентацию «Слайды», щелкните ссылку « Презентация дат и курсов валют »:

51326ceaeb3e49b2.gif

При желании вы также можете добавить в электронную таблицу дополнительные диаграммы и повторно выбрать пункт меню, чтобы создать презентацию с несколькими слайдами.

Теперь вы можете экспортировать диаграммы, созданные в Sheets, в презентацию Slides. Вы также можете написать код для создания пользовательского диалога.

Вы выполнили последнее упражнение этой лаборатории кода. Перейдите к следующему разделу, чтобы повторить то, что вы узнали.

5. Вывод

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

Вы нашли эту кодовую лабораторию полезной?

Да Нет

Что вы узнали

  • Как построить встроенную линейную диаграмму с помощью Apps Script.
  • Как отображать всплывающие сообщения и настраиваемые диалоговые окна для пользователя в Таблицах.
  • Как экспортировать диаграмму в новую презентацию Slides.

Что дальше

Вы успешно завершили этот плейлист. Однако еще многое предстоит узнать о Apps Script.

Ознакомьтесь с этими ресурсами:

Удачного написания сценариев!

Считаете ли вы этот плейлист codelab полезным?

Да Нет

Хотели бы вы, чтобы в будущем появилось больше лабораторий кода Apps Script?

Да Нет