Превратите свои большие данные в ценную информацию с помощью Google Sheets и Slides

1. От анализа больших данных к презентации в формате слайдов.

Существует множество инструментов для анализа больших данных, но в конечном итоге аналитикам все равно приходится обосновывать результаты перед руководством. Большое количество цифр на бумаге или в базе данных вряд ли удобно для представления ключевым заинтересованным сторонам. Этот практический урок по Google Apps Script среднего уровня сложности использует две платформы для разработчиков Google ( Google Workspace и Google Cloud Console ), чтобы помочь вам автоматизировать этот заключительный этап.

Инструменты разработчика Google Cloud позволяют проводить углубленный анализ данных. Затем вы можете взять результаты, вставить их в электронную таблицу и создать презентацию с данными. Это более удобный способ предоставления данных руководству. В этом практическом занятии рассматривается API BigQuery в Cloud Console (в виде расширенного сервиса Apps Script) и встроенные сервисы Apps Script для Google Sheets и Google Slides .

Мотивация

Пример приложения в этом практическом занятии был создан по мотивам следующих примеров кода:

• Пример приложения Google Apps Script BigQuery Service , открытый на GitHub .
• Пример приложения, показанный в видеоролике для разработчиков «Создание слайдов из данных электронных таблиц» и опубликованный в этой статье блога .
• Пример приложения, представленный в практическом руководстве по использованию API Google Slides .

Хотя в примере приложения Slides API, представленном в этом руководстве, также используются BigQuery и Slides, оно отличается от примера приложения из этого руководства по нескольким параметрам:

• Их приложение на Node.js против нашего приложения на Apps Script.
• Они используют REST API, а мы — сервисы Apps Script.
• Они используют Google Drive, но не Google Sheets, тогда как это приложение использует Sheets, но не Drive.

В рамках этого практического занятия мы хотели объединить несколько технологий в одном приложении, продемонстрировав при этом возможности и API из всего Google Cloud таким образом, чтобы это напоминало реальный пример использования. Цель — вдохновить вас на проявление воображения и на использование Cloud Console и Google Workspace для решения сложных задач вашей организации и ваших клиентов.

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

• Как использовать Google Apps Script с несколькими сервисами Google.
• Как использовать Google BigQuery для анализа больших данных
• Как создать таблицу Google Sheets и вставить в неё данные.
• Как создать диаграмму в Google Sheets
• Как перенести данные и диаграммы из Google Таблиц в презентацию Google Слайдс

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

• Веб-браузер с доступом в интернет.
• Учетная запись Google (для учетных записей Google Workspace может потребоваться подтверждение администратора).
• Базовые навыки работы с Google Sheets.
• Умение читать обозначения листов формата А1.
• Базовые навыки JavaScript
• Знание разработки на Apps Script полезно, но не обязательно.

2. Опрос

Как вы будете использовать этот практический пример/учебное пособие?

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

Как бы вы оценили свой опыт использования инструментов разработчика и API Google Workspace?

Новичок Средний Профессионал

Как бы вы оценили свой опыт работы с Apps Script?

Новичок Средний Профессионал

Как бы вы оценили свой опыт использования инструментов разработчика и API Cloud Console?

Новичок Средний Профессионал

3. Обзор

Теперь, когда вы знаете, о чём этот практический урок, вот что вам нужно сделать:

1. Возьмите существующий пример использования Apps Script и BigQuery и заставьте его работать.
2. На примере вы узнаете, как отправить запрос в BigQuery и получить результаты.
3. Создайте таблицу Google Sheets и вставьте в нее результаты из BigQuery.
4. Измените код, чтобы немного изменить данные, возвращаемые и вставляемые в таблицу.
5. Используйте службу «Таблицы» в Apps Script для создания диаграммы на основе данных BigQuery.
6. Воспользуйтесь сервисом Slides для создания презентации в Google Slides.
7. Добавьте заголовок и подзаголовок к стандартному титульному слайду.
8. Создайте слайд с таблицей данных и экспортируйте ячейки данных из таблицы в нее.
9. Создайте еще один слайд и вставьте в него диаграмму из электронной таблицы.

Давайте начнём с некоторой справочной информации о Apps Script, BigQuery, Sheets и Slides.

Google Apps Script и BigQuery

Google Apps Script — это платформа разработки Google Workspace, работающая на более высоком уровне, чем REST API Google. Это бессерверная среда разработки и размещения приложений, доступная разработчикам любого уровня квалификации. По сути, Apps Script — это бессерверная среда выполнения JavaScript для автоматизации, расширения и интеграции Google Workspace.

Он использует серверный JavaScript, похожий на Node.js, но ориентирован на тесную интеграцию с Google Workspace и другими сервисами Google, а не на быструю асинхронную обработку событий при размещении приложений. Кроме того, он предлагает среду разработки, которая может отличаться от той, к которой вы привыкли. С Apps Script вы:

• Разрабатывайте скрипты с помощью браузерного редактора кода, а также локально, используя clasp — инструмент развертывания приложений через командную строку.
• Напишите код на специализированной версии JavaScript, настроенной для доступа к Google Workspace и другим сервисам Google или внешним сервисам (используя URL Fetch или сервисы JDBC в Apps Script).
• Можно обойтись без написания кода авторизации, так как Apps Script делает это за вас.
• Вашему приложению не нужно размещаться на собственном сервере — оно будет работать на серверах Google в облаке.

ПРИМЕЧАНИЕ : Данное практическое занятие не предназначено для обучения работе с Apps Script. В интернете есть множество ресурсов, которые помогут вам в этом. В официальной документации также есть обзор с быстрыми стартами , учебными пособиями и видеороликами . Кроме того, перед началом этого занятия необходимо пройти два вводных практических занятия: «Основы Apps Script с Google Sheets» и «Практическое занятие с Google Apps Script» .

Apps Script взаимодействует с другими технологиями Google двумя различными способами:

• В качестве встроенной услуги
• В качестве передовой услуги

Встроенный сервис содержит высокоуровневые методы для взаимодействия с пользовательскими данными, другими системами Google и внешними системами. Расширенный сервис , по сути, представляет собой тонкую оболочку над API Google Workspace или Google REST API. Расширенные сервисы обеспечивают полное покрытие REST API и часто могут делать больше, чем встроенные сервисы, но требуют большей сложности кода (при этом оставаясь проще в использовании, чем полный REST API). Кроме того, расширенные сервисы необходимо включить для проекта скрипта перед их использованием.

По возможности разработчикам следует использовать встроенные сервисы, поскольку они проще в использовании и предоставляют больше возможностей, чем расширенные сервисы. Однако некоторые API Google не имеют встроенных сервисов, поэтому расширенный сервис может быть единственным вариантом. Например, Google BigQuery не имеет встроенного сервиса, но существует сервис BigQuery . Сервис BigQuery — это сервис Cloud Console, который позволяет использовать API Google BigQuery для выполнения запросов к большим массивам данных (например, нескольким терабайтам), при этом он может предоставлять результаты за считанные секунды.

Доступ к таблицам и презентациям из Apps Script

В отличие от BigQuery, Sheets и Slides имеют встроенные сервисы. Они также обладают расширенными сервисами для доступа к функциям, доступным только через API. Перед тем как приступить к коду, ознакомьтесь с документацией по встроенным сервисам Sheets и Slides . Обратите внимание, что существует также документация по расширенным сервисам как Sheets , так и Slides .

4. Задание 1: Запустите BigQuery и запишите результаты в Google Sheets.

Введение

Большую часть этого практического задания мы выполним в первой части. Фактически, после её завершения вы пройдете примерно половину всего практического задания. Разделенное на несколько подразделов, оно включает в себя:

• Создайте проект одновременно в Google Apps Script и в Cloud Console.
• Включите доступ к расширенным функциям сервиса BigQuery.
• Откройте редактор скриптов и введите исходный код приложения.
• Пройдите процесс авторизации приложения (OAuth2).
• Запустите приложение, которое отправляет запрос в BigQuery.
• Просмотрите новую таблицу Google Sheets, созданную на основе результатов BigQuery.

Настраивать

1. Чтобы создать проект Apps Script, перейдите на script.google.com и нажмите «Новый проект» .
2. Чтобы переименовать проект Apps Script, щелкните «Безымянный проект» , введите название для проекта и нажмите «Переименовать» .

Далее вам потребуется создать проект в Cloud Console для выполнения запросов к данным в BigQuery.

Примечание : Если вы выполняете это практическое задание на киоске во время мероприятия, организованного Google, то для вас уже создан проект в Cloud Console. Откройте проект и перейдите к шагу 5 ниже.

3. Чтобы создать проект в Cloud Console, воспользуйтесь этой ссылкой для создания проекта , дайте проекту имя и нажмите «Создать» .

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

4. После завершения создания проекта на странице появится уведомление. Убедитесь, что ваш новый проект выбран в списке проектов в верхней части страницы.
5. Нажмите «Меню» и перейдите в раздел API и сервисы > Экран согласия OAuth ( прямая ссылка ).
6. Чтобы создать приложение для пользователей Google Workspace в вашей организации, нажмите «Внутреннее» > «Создать» .
7. В поле «Название приложения » введите «Big Data Codelab».
8. Введите адреса электронной почты для полей « Служба поддержки пользователей» и «Контактная информация для разработчиков» .
9. Нажмите «Сохранить и продолжить» > «Сохранить и продолжить» .
10. Нажмите «Подробнее» На панели навигации выберите «Настройки проекта» ( прямая ссылка ).
11. Скопируйте значение, указанное в поле «Номер проекта» . (Отдельное поле «Идентификатор проекта» будет использовано позже в практическом задании.)

Примечание : Подробную информацию о вашем проекте также можно найти на странице «Панель управления» в облачной консоли.

Далее вам нужно будет подключить свой проект Apps Script к проекту Cloud Console.

12. Перейдите в редактор App Script и нажмите «Настройки проекта» . .
13. В разделе «Проект Google Cloud Platform (GCP)» нажмите «Изменить проект» .
14. Введите номер проекта и нажмите «Установить проект» .
15. Далее нажмите «Редактор». чтобы начать добавление расширенной службы BigQuery.
16. Рядом с пунктом «Услуги» нажмите «Добавить услугу». .
17. В диалоговом окне «Добавить службу» выберите BigQuery API и нажмите «Добавить» .

Последний шаг — включение API BigQuery в облачной консоли.

18. Для этого перейдите в консоль Cloud Console и нажмите API и сервисы > Панель мониторинга . (Убедитесь, что вы по-прежнему находитесь в том же проекте, который создали на шаге 3.)
19. Нажмите «Включить API и сервисы» .
20. Найдите "большой запрос", выберите API BigQuery ( не API передачи данных BigQuery) и нажмите "Включить" , чтобы активировать его.

ПРИМЕЧАНИЕ : После включения API на этой странице может появиться сообщение «Для использования этого API необходимо создать учетные данные...». Пока не беспокойтесь об этом — Apps Script выполнит этот шаг за вас.

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

Загрузите приложение и запустите его.

1. В редакторе скриптов замените стандартный блок кода myFunction() следующим кодом:

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a Sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}

2. Нажмите «Сохранить». .
3. Рядом с Code.gs нажмите «Подробнее». > Переименовать . Изменить заголовок с Code.gs на bq-sheets-slides.js .
4. Далее рассмотрим код, который выполняет запрос к BigQuery и записывает результаты в таблицу Google Sheets. Вы можете увидеть его в верхней части функции runQuery() :

SELECT
    LOWER(word) AS word,
    SUM(word_count) AS count
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY word
ORDER BY count
DESC LIMIT 10

Этот запрос просматривает произведения Шекспира, входящие в общедоступный набор данных BigQuery , и выдает 10 наиболее часто встречающихся слов во всех его произведениях, отсортированных в порядке убывания популярности. Вы можете представить, насколько полезен BigQuery, если вообразите, сколько работы потребовалось бы для составления этого списка вручную.

Функция также объявляет переменную PROJECT_ID , для работы которой требуется действительный идентификатор проекта Cloud Console. Оператор if под этой переменной предназначен для предотвращения продолжения работы приложения без идентификатора проекта.

5. Перейдите в свой проект в Cloud Console и нажмите «Подробнее». На панели навигации выберите «Настройки проекта» .
6. Скопируйте значение, указанное в поле «Идентификатор проекта» .
7. Вернитесь в редактор App Script, найдите переменную PROJECT_ID в bq-sheets-slides.js и добавьте её значение.
8. Нажмите «Сохранить». > Выполнить .
9. Нажмите «Проверить права доступа» , чтобы продолжить.

Примечание: Если вы используете учетную запись gmail.com , при первом использовании скрипта может появиться диалоговое окно «Это приложение не проверено». Google использует это для предупреждения пользователей, которые могут использовать код от неизвестных или ненадежных авторов. Если вы видите это диалоговое окно, можете продолжить, поскольку вы являетесь автором скрипта. Следуйте инструкциям на экране, чтобы продолжить авторизацию скрипта:

1. В диалоговом окне непроверенного приложения нажмите «Дополнительно» .
2. Нажмите « Перейти к названию вашего проекта (небезопасно)» .
3. Нажмите «Разрешить» .

В ходе этого практического занятия вам может потребоваться несколько раз предоставить разрешения. Подробнее об этом процессе можно прочитать в разделе «Авторизация для сервисов Google» .

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

Чтобы отобразить или скрыть журнал выполнения, нажмите кнопку «Журнал выполнения» в верхней части редактора.

11. Как только в журнале выполнения появится сообщение «Выполнение завершено», перейдите в свой Google Диск ( drive.google.com ) и найдите таблицу Google с названием «Наиболее часто встречающиеся слова во всех произведениях Шекспира» (или с именем, которое вы присвоили переменной QUERY_NAME , если вы ее обновили):
12. Откройте электронную таблицу, чтобы просмотреть 10 наиболее часто употребляемых слов и их общее количество, отсортированное в порядке убывания:

Краткое изложение задания 1

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

Код, который вы использовали для bq-sheets-slides.js также можно найти в папке step1 репозитория GitHub этого учебного пособия по адресу github.com/googlecodelabs/bigquery-sheets-slides . Код был вдохновлен оригинальным примером на странице расширенных сервисов BigQuery , в котором выполнялся немного другой запрос для получения наиболее популярных слов из 10 и более символов, используемых Шекспиром. Вы также можете увидеть пример в его репозитории GitHub.

Если вас интересуют другие запросы, которые можно создавать с использованием произведений Шекспира или других общедоступных таблиц данных, посетите раздел «Как создавать запросы к примерам таблиц BigQuery» и этот репозиторий на GitHub .

Вы также можете выполнять запросы, используя страницу BigQuery в консоли Cloud, прежде чем запускать их в Apps Script. Чтобы найти её, нажмите «Меню». и перейдите в BigQuery UI > SQL workspace ( прямая ссылка ). Например, вот как наш запрос отображается в графическом интерфейсе BigQuery:

5. Задание 2: Создайте диаграмму в Google Таблицах.

Цель функции runQuery() — использовать BigQuery и отправить результаты обработки данных в таблицу Google Sheets. Далее нам нужно построить диаграмму, используя эти данные. Давайте создадим новую функцию с именем createColumnChart() , которая будет вызывать метод newChart() из Google Sheets.

1. В редакторе Apps Script добавьте функцию createColumnChart() в файл bq-sheets-slides.js после runQuery() . Код получает лист и запрашивает столбчатую диаграмму со всеми данными. Диапазон данных начинается с ячейки A2, поскольку первая строка содержит заголовки столбцов.

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

2. Функция createColumnChart() требует в качестве параметра объект электронной таблицы, поэтому нам нужно обновить runQuery() , чтобы она возвращала объект spreadsheet , который можно передать в createColumnChart() . В конце функции runQuery() верните объект spreadsheet после того, как выведете в консоль сообщение об успешном создании листа:

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}

3. Создайте функцию createBigQueryPresentation() , которая будет вызывать функции runQuery() и createColumnChart() . Логическое разделение функциональности BigQuery и создания диаграмм является лучшей практикой:

/**
 * Runs the query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}

4. Вы выполнили два важных шага выше: вернули объект электронной таблицы и создали функцию ввода. Чтобы сделать runQuery() более удобным, нам нужно перенести строку лога из runQuery() в createBigQueryPresentation() . Теперь ваш метод должен выглядеть так:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

После внесения вышеуказанных изменений (за исключением PROJECT_ID ) ваш bq-sheets-slides.js теперь должен выглядеть следующим образом. Этот код также находится в step2 репозитория GitHub .

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

В редакторе скриптов сохраните свой проект скрипта. Затем выберите createBigQueryPresentation из списка функций и нажмите «Выполнить» . После завершения в вашем Google Диске будет создана еще одна электронная таблица, но на этот раз рядом с данными в таблицу будет включена диаграмма:

6. Задание 3: Представьте результаты в виде презентации.

Заключительная часть практического задания включает в себя создание презентации в Google Slides, добавление заголовка и подзаголовка к титульному слайду, а затем создание слайдов для ячеек с данными и диаграммы.

1. В редакторе Apps Script добавьте функцию createSlidePresentation() в файл bq-sheets-slides.js после функции createColumnChart() . Вся работа над презентацией будет выполняться в этой функции. Давайте начнем с создания презентации, а затем добавим заголовок и подзаголовок к слайду с заголовком по умолчанию.

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
  * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

2. Следующий шаг в функции createSlidePresentation() — импорт данных ячеек из таблицы Google Sheets в нашу новую презентацию. Добавьте в функцию следующий фрагмент кода:

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

3. Последний шаг в createSlidePresentation() — добавление еще одного слайда, импорт диаграммы из электронной таблицы и возврат объекта Presentation . Добавьте в функцию следующий фрагмент кода:

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

4. Теперь, когда наша функция завершена, еще раз взгляните на ее сигнатуру. Функция createSlidePresentation() требует в качестве параметров как объект электронной таблицы, так и объект диаграммы. Мы уже изменили runQuery() , чтобы она возвращала объект Spreadsheet но нам нужно внести аналогичное изменение в функцию createColumnChart() , чтобы она возвращала объект диаграммы ( EmbeddedChart ). Вернитесь к функции createColumnChart() и добавьте следующий фрагмент кода в конец функции:

  // NEW: Return the chart object for later use.
  return chart;
}

5. Поскольку createColumnChart() теперь возвращает объект диаграммы, нам нужно сохранить диаграмму в переменную. Затем мы передаем переменные электронной таблицы и диаграммы в функцию createSlidePresentation() . Кроме того, поскольку мы выводим в консоль URL-адрес только что созданной электронной таблицы, давайте также выведем в консоль URL-адрес новой презентации. Обновите функцию createBigQueryPresentation() так, чтобы она выглядела следующим образом:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}

6. Сохраните изменения и снова запустите createBigQueryPresentation() . Однако перед его выполнением вашему приложению потребуются дополнительные разрешения от пользователя для просмотра и управления презентациями Google Slides. После проверки и предоставления этих разрешений приложение будет работать как прежде.
7. Теперь, помимо созданной электронной таблицы, вы также получите новую презентацию Slides, состоящую из 3 слайдов (заголовок, таблица данных, диаграмма данных), как показано ниже:

7. Заключение

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

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

С учетом изменений, внесенных в последнем задании (за исключением PROJECT_ID ), ваш bq-sheets-slides.js теперь должен выглядеть следующим образом:

/**
 * Copyright 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a spreadsheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
  
  // Return the chart object for later use.
  return chart;
}

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
 * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet);
  var deck = createSlidePresentation(spreadsheet, chart);
  Logger.log('Results slide deck created: %s', deck.getUrl());
}

Этот пример кода также можно найти в final папке репозитория GitHub .

8. Дополнительные ресурсы

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

Ресурсы для этого приложения

• Репозиторий исходного кода
• Видео для разработчиков (и) сообщение в блоге
• Сессия Google Cloud NEXT '18

Документация

• Сайт документации Google Apps Script
• Apps Script: Сервис электронных таблиц
• Apps Script: Сервис слайдов
• Apps Script: Расширенный сервис BigQuery

Видео

• Ещё один секрет Google Apps Script
• Доступ к Google Maps из электронной таблицы
• Совершенно без сценария
• Google Workspace Developer Show

Новости и обновления

• Блог Google Cloud Platform
• Блог Google Cloud Data Analytics
• Блог разработчиков Google
• Twitter: Google Developers (@googledevs)
• Блог разработчиков Google Workspace
• Twitter: Google Workspace Developers (@workspacedevs)

Другие семинары по кодированию

Введение

• [Google Sheets] Основы работы с AppScript в Google Sheets
• [REST API] Используйте Google Workspace и Google API для доступа к файлам и папкам в вашем Google Диске.

Средний

• [Apps Script] Инструмент командной строки CLASP Apps Script
• [Apps Script] Дополнения Google Workspace для Gmail
• [Apps Script] Пользовательские боты для чата Hangouts
• [REST API] Используйте Google Таблицы в качестве инструмента для создания отчетов вашего приложения.
• [REST API] Создавайте презентации Google Slides с помощью API BigQuery

9. Следующий шаг: задания по программированию.

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

• Приложение — Не хотите ограничиваться использованием JavaScript или ограничениями, накладываемыми Apps Script? Перенесите это приложение на свой любимый язык программирования, использующий REST API для Google BigQuery, Sheets и Slides.
• BigQuery — Поэкспериментируйте с другим запросом к интересующему вас набору данных о Шекспире. Другой пример запроса можно найти в оригинальном демонстрационном приложении BigQuery на Apps Script .
• BigQuery — Поэкспериментируйте с другими общедоступными наборами данных BigQuery, чтобы найти тот, который будет для вас более значимым.
• BigQuery — Ранее мы упоминали другие запросы, которые можно создавать с использованием произведений Шекспира или других общедоступных таблиц данных. Их можно найти на этой веб-странице и в этом репозитории GitHub .
• Листы — Поэкспериментируйте с другими типами диаграмм в Галерее диаграмм .
• Электронные таблицы и BigQuery — Используйте собственные большие наборы данных в электронных таблицах. В 2016 году команда BigQuery представила функцию, позволяющую разработчикам использовать электронную таблицу в качестве источника данных. Для получения дополнительной информации перейдите по ссылке ( Google BigQuery интегрируется с Google Drive ).
• Слайды — Добавьте в созданную презентацию другие слайды, например изображения или другие материалы, связанные с анализом больших данных. Вот справочная документация по встроенной службе «Слайды» .
• Google Workspace — Используйте другие сервисы Google Workspace или встроенные сервисы Google из Apps Script. Например, Gmail, Календарь, Документы, Диск, Карты, Аналитика, YouTube и т. д., а также другие расширенные сервисы. Для получения дополнительной информации перейдите к обзору встроенных и расширенных сервисов.