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

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

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

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

Мотивация

Пример приложения в этой лаборатории написан на основе других примеров кода:

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

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

• Их приложение Node.js и наше приложение Apps Script.
• Они используют REST API, а мы используем службы Apps Script.
• Они используют Google Диск, но не Google Таблицы, тогда как это приложение использует Таблицы, но не Диск.

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

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

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

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

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

2. Опрос

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

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

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

Новичок Средний Опытный

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

Новичок Средний Опытный

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

Новичок Средний Опытный

3. Обзор

Теперь, когда вы знаете, о чем эта лаборатория кода, вот что вы собираетесь делать:

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

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

Скрипт Google Apps и BigQuery

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

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

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

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

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

• Как встроенный сервис
• В качестве расширенной услуги

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

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

Доступ к листам и слайдам из скрипта приложений

В отличие от BigQuery, Sheets и Slides имеют встроенные сервисы. У них также есть расширенные услуги для доступа к функциям, доступным только в API. Прежде чем приступить к написанию кода, просмотрите документацию для встроенных сервисов «Таблицы» и «Слайды» . Обратите внимание, что есть также документация по расширенным сервисам Sheets и Slides .

4. Задача 1. Запустите BigQuery и запишите результаты в Таблицы.

Введение

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

• Создайте проект Google Apps Script и Cloud Console.
• Включите доступ к расширенному сервису BigQuery.
• Откройте редактор скриптов и введите исходный код приложения.
• Перейдите к процессу авторизации приложения (OAuth2).
• Запустите приложение, которое отправляет запрос в BigQuery.
• Просмотрите новую таблицу Google, созданную с использованием результатов 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. Скопируйте значение, указанное в разделе «Номер проекта» . (Отдельное поле идентификатора проекта используется позже в кодовой лаборатории.)

Примечание . Вы также можете найти информацию о своем проекте на странице панели управления в Cloud Console.

Далее вы подключите проект Apps Script к проекту Cloud Console.

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

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

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

ПРИМЕЧАНИЕ . После включения 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 Sheet. Вы можете просмотреть его в верхней части 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 Таблиц.

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

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

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

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

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

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 Sheet в нашу новую колоду слайдов. Добавьте этот фрагмент кода в функцию:

  // 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 с тремя слайдами (заголовок, таблица данных, диаграмма данных), как показано ниже:

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
• Скрипт приложений: служба электронных таблиц
• Скрипт приложений: сервис слайдов
• Скрипт приложений: расширенный сервис BigQuery

Видео

• Еще один секрет Google Apps Script
• Доступ к Картам Google из электронной таблицы
• Совершенно без сценария
• Выставка разработчиков Google Workspace

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

• Блог облачной платформы Google
• Блог Google Cloud Data Analytics
• Блог разработчиков Google
• Твиттер: Разработчики Google (@googledevs)
• Блог разработчиков Google Workspace
• Твиттер: разработчики Google Workspace (@workspacedevs)

Другие лаборатории разработки

Вводный

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

Средний

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

9. Следующий шаг: проблемы с кодом

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

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