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

Существует множество инструментов для анализа больших данных, доступных специалистам по анализу данных, но разве вам всё равно не приходится обосновывать эти результаты перед руководством? Обилие цифр на бумаге или в базе данных вряд ли будет понятно ключевым заинтересованным сторонам. Эта промежуточная лабораторная работа по Google Apps Script использует две платформы разработки Google: G Suite и Google Cloud Platform (GCP), чтобы помочь вам пройти этот последний этап.

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

Мотивация/предшествующий уровень техники

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

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

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

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

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

Чему вы научитесь

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

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

• Доступ к Интернету и веб-браузеру
• Учетная запись Google (для учетных записей G Suite может потребоваться одобрение администратора)
• Базовые навыки JavaScript
• Знание разработки Apps Script может быть полезным, но не обязательным.

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

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

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

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

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

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

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

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

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

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

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

Скрипт Google Apps и BigQuery

Google Apps Script — это платформа разработки G Suite, работающая на более высоком уровне, чем REST API Google. Это бессерверная среда разработки и хостинга приложений, доступная разработчикам с широким диапазоном навыков. Одним предложением можно сказать: «Apps Script — это бессерверная среда выполнения JavaScript для автоматизации, расширения и интеграции G Suite».

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

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

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

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

• встроенный/нативный сервис
• расширенный сервис

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

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

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

В отличие от BigQuery, в Google Таблицах и Презентациях есть встроенные сервисы (а также расширенные сервисы, которые используются только для доступа к функциям, доступным только в API). Прежде чем приступать к коду, ознакомьтесь с документацией по встроенным сервисам Таблиц и Презентаций . Конечно, есть и документация по расширенным сервисам; вот она для Таблиц и Презентаций соответственно.

Введение

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

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

Настраивать

1. а) Создайте новый проект Apps Script, перейдя на сайт script.google.com . Существуют различные линейки продуктов G Suite , и способ создания нового проекта может отличаться в зависимости от используемой версии. Если вы используете только аккаунт Gmail и новичок в разработке проектов, вы увидите пустой экран с кнопкой для создания первого проекта:
   
   
   

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



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



г) Для тех, кто предпочитает командную строку. Ваш инструмент — clasp . В частности, вам нужно выполнить команду clasp create .

e) Последний способ создания нового проекта скрипта — просто перейти по ссылке: https://script.google.com/create .

2. Независимо от того, какой метод вы использовали для начала нового проекта, в конечном итоге вы должны будете попасть в редактор кода Apps Script, экран которого выглядит следующим образом:
   
   
   
3. Нажмите Файл > Сохранить и дайте проекту имя.
4. Далее вам необходимо создать проект Google Cloud Console для выполнения запросов BigQuery.

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

5. Создайте новый проект , дайте ему имя, выберите платежный аккаунт и нажмите СОЗДАТЬ .

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

6. После завершения создания проекта в правом верхнем углу страницы появится уведомление. Чтобы открыть проект, нажмите «Создать проект: <Имя проекта>» .
7. Нажмите на значок меню. в левом верхнем углу и перейдите в раздел «API и службы» > «Учётные данные» . Нажмите на вкладку «Экран согласия OAuth» ( прямая ссылка ).
8. В поле Имя приложения введите «Big Data Codelab» и нажмите кнопку Сохранить внизу.
9. Нажмите на значок с тремя точками. в правом верхнем углу разверните меню и выберите Настройки проекта ( прямая ссылка ).
10. Скопируйте значение, указанное в поле «Номер проекта» . (Существует отдельное поле «Идентификатор продукта» , которое мы будем использовать позже в этой лабораторной работе.)
11. Вернувшись в редактор скриптов приложений, нажмите Ресурсы > Проект Cloud Platform .
12. Введите номер проекта в текстовое поле и нажмите «Установить проект» . При появлении запроса нажмите «Подтвердить» .
13. По завершении нажмите кнопку «Закрыть» , чтобы закрыть диалоговое окно.
14. Теперь, когда у вас настроен новый проект, вам нужно включить расширенную службу BigQuery, для этого откройте Ресурсы -> Расширенные службы Google и включите API BigQuery.
    
    
    
15. В примечании внизу указано: «Эти службы также должны быть включены в «Панели инструментов API Google Cloud Platform», поэтому нажмите на эту ссылку, которая открывает другую вкладку браузера с консолью разработчика или сокращенно «devconsole».
16. В консоли разработчика нажмите кнопку «Включить API и службы» вверху, найдите «bigquery», выберите API BigQuery ( а не API передачи данных BigQuery) и нажмите «Включить» , чтобы включить его. Оставьте эту вкладку браузера открытой.
    
    ПРИМЕЧАНИЕ : после включения API на этой странице может появиться заметка вроде: «Чтобы использовать этот API, вам необходимо создать учетные данные...», но пока не беспокойтесь об этом — Apps Script выполнит этот шаг за вас.
    
    
    
17. Вернувшись на вкладку браузера с редактором кода, вы всё ещё находитесь в меню «Дополнительные службы Google», поэтому нажмите «ОК» , чтобы закрыть диалоговое окно и остаться в редакторе кода. Нажмите на название проекта вверху и назовите его как угодно, например, «BigQuery demo» или что-то подобное. Мы назвали свой проект «final mile».

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

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

1. Скопируйте код из поля ниже и вставьте его поверх всего текста в редакторе кода:

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://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 BQ 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 new 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());
}

Теперь сохраните только что созданный файл, переименовав его из Code.gs в bq-sheets-slides.js . Что же делает этот код? Мы уже говорили, что он отправляет запрос к BigQuery и записывает результаты в новую Google Таблицу, но что это за запрос? Вы можете увидеть его в начале метода 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.

2. Мы почти готовы, но ещё не совсем, попробовать. Как вы видите в верхней части этого фрагмента кода, требуется действительный идентификатор проекта, поэтому нам нужно добавить ваш идентификатор в код приложения. Для этого вернитесь в окно или вкладку браузера, где находится страница консоли разработчика. (Мы же просили вас оставить её открытой, помните?)
3. Вверху слева от аватара вашего аккаунта Google находится селектор выпадающего меню ( ). Щелкните по нему и выберите «Настройки проекта» . Вы увидите название, идентификатор и номер проекта. Скопируйте идентификатор проекта и задайте для переменной PROJECT_ID в верхней части файла bq-sheets-slides.js значение, полученное из консоли разработчика. ПРИМЕЧАНИЕ: Если селектор меню застревает и перестает работать, перезагрузите страницу.
4. Оператор if предотвращает дальнейшую работу приложения без идентификатора проекта. После добавления идентификатора проекта сохраните файл и запустите код, выбрав в строке меню «Выполнить» > «Выполнить функцию» > «RunQuery» , затем нажмите «Проверить разрешения ». Это приложение не проверено. Ниже представлен анимированный GIF-файл (для другого приложения), иллюстрирующий следующие несколько шагов:
   
5. После запроса на проверку разрешений откроется новое диалоговое окно, как показано выше. Выберите нужную учётную запись Google, для которой будет запущен скрипт, выберите «Дополнительно» , прокрутите вниз и нажмите «Перейти к <ИМЯ ВАШЕГО ПРОЕКТА> (небезопасно)», чтобы открыть экран авторизации приложения OAuth2. (Подробнее о процессе проверки , чтобы узнать, почему этот экран находится между вами и диалоговым окном авторизации OAuth2, читайте ниже.)
   
   
   ПРИМЕЧАНИЕ: После авторизации приложения вам больше не придётся повторять этот процесс при каждом запуске. Только при выполнении задания 3 в этом руководстве вы снова увидите это диалоговое окно с запросом разрешения пользователя на создание и управление презентациями Google Slides.
6. После нажатия кнопки «Разрешить» в диалоговом окне OAuth2 скрипт начнёт выполняться... вверху вы увидите пастельно-жёлтое диалоговое окно. Он выполняется довольно быстро, поэтому вы можете не заметить его запуска или завершения выполнения.
   
   
7. После завершения диалоговое окно исчезает, поэтому, если вы его не видите, вероятно, работа уже завершена. Поэтому перейдите на Google Диск ( drive.google.com ) и найдите новую таблицу Google с названием «Наиболее употребительные слова во всех произведениях Шекспира» или с тем названием, которое вы присвоили переменной QUERY_NAME :
   
   
   
8. Откройте электронную таблицу, и вы увидите 10 строк слов и их общее количество, отсортированное в порядке убывания:
   
   

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

Осознайте, что только что произошло... вы запустили некий код, который запросил все произведения Шекспира (не ОГРОМНЫЙ объем данных, но определенно больше текста, чем вы можете легко просмотреть самостоятельно), просматривая каждое слово в каждой пьесе, управляя количеством таких слов, а затем сортируя их в порядке убывания появлений. Вы не только попросили BigQuery сделать это от вашего имени, но и смогли использовать встроенную службу в Apps Script для Google Таблиц, чтобы поместить эти данные туда для удобства использования.

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

Если вас интересуют другие запросы, которые можно использовать с произведениями Шекспира или другими общедоступными таблицами данных, посетите эту и эту веб-страницы. Независимо от того, какой запрос вы используете, вы всегда можете просто протестировать его в консоли BigQuery, прежде чем запускать в Apps Script. Пользовательский интерфейс BigQuery доступен разработчикам по адресу bigquery.cloud.google.com . Например, вот как выглядит наш запрос с использованием BigQuery UI:

Хотя в описанных выше действиях использовался редактор кода Apps Script, вы также можете разрабатывать локально через командную строку. Если вам удобнее, создайте скрипт с именем bq-sheets-slides.js , вставьте в него приведённый выше код и загрузите его в Google с помощью команды clasp push . (Если вы пропустили эту ссылку, вот ссылка на clasp и инструкцию по его использованию.)

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

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

/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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. Возвращаем spreadsheet . Вышеприведенному методу createColumnChart() требуется объект spreadsheet, поэтому нам нужно обновить runQuery() так, чтобы он возвращал объект spreadsheet , который можно передать createColumnChart() . После регистрации успешного создания Google Sheet верните объект spreadsheet в конце runQuery() , сразу после строки журнала:
   

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

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

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

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

4. Сделайте код более пригодным для повторного использования . Выше вы выполнили два важных шага: вернули объект электронной таблицы и создали управляющую функцию. Что, если коллега захочет повторно использовать runQuery() и не захочет регистрировать URL? Чтобы сделать runQuery() более удобным для общего использования, нам нужно переместить эту строку журнала. Куда лучше всего её переместить? Если вы предположили createBigQueryPresentation() , вы правы! После перемещения строки журнала она должна выглядеть так:
   

/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
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 ):

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Sheet} Returns a sheet with results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
 */
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 BQ 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 new 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 columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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 in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

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

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

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

/**
 * Create presentation with spreadsheet data & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Slide deck with results
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new 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('via GCP and G Suite APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

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

  // 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 of
  // the dimensions of the data range; fails if Sheet 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. Return chart . Теперь, когда наша последняя функция завершена, ещё раз взгляните на её сигнатуру. Да, createSlidePresentation() требуются как объекты электронной таблицы, так и диаграммы. Мы уже настроили runQuery() для возврата объекта Spreadsheet , но теперь нам нужно внести аналогичное изменение в createColumnChart() для возврата объекта диаграммы ( EmbeddedChart ). Вернитесь в код своего приложения и добавьте последнюю строку в конце createColumnChart() для этого:
   

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

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

/**
 * Runs a BigQuery query, adds data and a chart in a Sheet,
 * 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 слайдами (заголовок, таблица данных, диаграмма данных), как показано ниже:

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

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

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

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.
 */

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://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 BQ 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 new 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 columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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 & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Returns a slide deck with results
 * @see http://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new 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('via GCP and G Suite 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 of
  // the dimensions of the data range; fails if Sheet 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 in a Sheet,
 * 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());
}

«Второй файл» в этой лабораторной работе не играет никакой роли — это файл манифеста Apps Script, appsscript.json . Вы можете получить к нему доступ, перейдя на вкладку браузера редактора кода и выбрав «Вид» > «Показать файл манифеста» в меню в верхней части страницы. Его содержимое должно выглядеть примерно так:

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "BigQuery",
      "serviceId": "bigquery",
      "version": "v2"
    }]
  },
  "exceptionLogging": "STACKDRIVER"
}

Файл манифеста — это системный файл конфигурации, который Apps Script использует для определения доступной среды выполнения для вашего приложения. Описание содержимого файла манифеста выходит за рамки данной практической работы, но вы можете получить представление о его функциях.

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

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

• Ссылка на эту практическую работу: g.co/codelabs/bigquery-sheets-slides
• Репозиторий исходного кода
• Видео разработчика (и) запись в блоге
• Сессия Google Cloud NEXT '18

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

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

Похожие и общие видео

• Еще один секрет Google (Apps)? (Вступительное видео Apps Script)
• Доступ к Google Картам из электронной таблицы?!? ( видео )
• Видеобиблиотека Google Apps Script
• Серия видеороликов Launchpad Online (предшественник...)
• Серия видеороликов G Suite Dev Show

Связанные и общие новости и обновления

• Коннектор данных Google Таблиц для BigQuery ( объявление )
• Google BigQuery интегрируется с Google Drive ( ссылка1 , ссылка2 )
• Блог разработчиков Google
• Блог Google Cloud Platform
• Блог Google Cloud о больших данных и машинном обучении
• Твиттер разработчиков Google (@GoogleDevs)
• Блог разработчиков G Suite
• Разработчики G Suite в Twitter (@googleworkspace)
• Ежемесячный информационный бюллетень для разработчиков G Suite

Другие лабораторные работы

Вводный

• [REST API] G Suite и Google API (Drive API)
• [App Maker] Создание веб-приложения с базой данных в App Maker

Средний

• [Apps Script] Инструмент командной строки CLASP Apps Script
• [Apps Script] Дополнения Gmail
• [Apps Script] Дополнение Docs и API естественного языка GCP
• [Apps Script] Фреймворк чат-бота Hangouts
• [REST API] Пользовательский инструмент отчетности (API Таблиц)
• [REST API] Пользовательский генератор слайдов для лицензии Github Анализатор BigQuery (слайды+BigQuery API)

Справочные приложения

• Конвертер Markdown-в-Google Slides (API Slides)

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

• Приложение . Не хотите ограничиваться использованием JavaScript или ограничениями Apps Script? Перенесите это приложение на свой любимый язык программирования, использующий REST API для Google BigQuery, Таблиц и Презентаций.
• BigQuery . Поэкспериментируйте с другим запросом для набора данных «Шекспир». Возможно, вы найдёте запрос, который вас заинтересует. Другой пример запроса можно найти в оригинальном примере приложения Apps Script BigQuery .
• BigQuery . Поэкспериментируйте с другими общедоступными наборами данных BigQuery... возможно, найдёте набор данных, который окажется для вас более значимым.
• BigQuery . Ранее мы упоминали другие запросы, которые можно попробовать выполнить к произведениям Шекспира или другим общедоступным таблицам данных, и хотели бы ещё раз поделиться этой и этой веб-страницами.
• Таблицы . Поэкспериментируйте с другими типами диаграмм .
• Таблицы и BigQuery. Переверните таблицы... возможно, у вас где-то в электронной таблице хранится большой набор данных. В 2016 году команда BigQuery представила функцию, позволяющую разработчикам использовать таблицу в качестве источника данных (подробнее см. в первой и второй публикациях блога).
• Слайды . Добавьте в созданную презентацию другие слайды, например изображения или другие материалы, связанные с анализом больших данных. Вот руководство по встроенному сервису «Слайды», которое поможет вам начать работу.
• G Suite . В Apps Script можно найти применение другим встроенным сервисам G Suite или Google, например, Gmail, Google Drive, Calendar, Docs, Maps, Analytics, YouTube и т. д., а также другим расширенным сервисам. Подробнее см. в обзоре встроенных и расширенных сервисов.