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

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

Инструменты разработчика Google Cloud позволяют выполнять глубокий анализ данных, затем брать эти результаты, помещать их в электронную таблицу и создавать слайд-презентацию с этими данными, обеспечивая более подходящую стадию для предоставления результатов руководству. Эта лаборатория кода охватывает API BigQuery GCP (как расширенный сервис 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 так, чтобы это больше напоминало реальный вариант использования. Цель состоит в том, чтобы вдохновить вас использовать свое воображение и рассмотреть возможность использования как GCP, так и G Suuite для решения сложных проблем вашей организации или ваших клиентов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скрипт Google Apps и BigQuery

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

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

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

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

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

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

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

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

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

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

Введение

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

• Запустите новый проект скрипта Google Apps
• Включить доступ к расширенному сервису 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. Скопируйте значение, указанное в разделе Номер проекта . (Есть отдельное поле Product ID , которое мы будем использовать позже в лаборатории кода.)
11. Вернувшись в редактор сценариев приложений, щелкните Ресурсы > Проект Cloud Platform .
12. Введите номер проекта в текстовое поле и нажмите Set Project . При появлении запроса нажмите Подтвердить .
13. По завершении нажмите кнопку « Закрыть », чтобы закрыть диалоговое окно.
14. Теперь, когда у вас есть новая настройка проекта, вам нужно включить расширенный сервис BigQuery, поэтому потяните Ресурсы -> Расширенные сервисы Google и включите бит для BigQuery API.
    
    
    
15. В примечании внизу говорится: «Эти службы также должны быть включены в «Панель мониторинга API Google Cloud Platform», поэтому нажмите на эту ссылку, которая откроет другую вкладку браузера для консоли разработчика или для краткости «devconsole».
16. В консоли разработчика нажмите кнопку +Включить API и службы вверху, выполните поиск по запросу «bigquery», выберите API BigQuery ( не API передачи данных BigQuery) и нажмите « Включить », чтобы включить его. Оставьте эту вкладку браузера открытой.
    
    ПРИМЕЧАНИЕ . После включения API вы можете увидеть на этой странице примечание, в котором говорится что-то вроде «Чтобы использовать этот API, вам необходимо создать учетные данные...», но пока не беспокойтесь об этом — скрипт приложений позаботьтесь об этом шаге для вас.
    
    
    
17. Вернувшись на вкладку браузера редактора кода, вы все еще находитесь в меню «Дополнительные службы Google», поэтому нажмите « ОК », чтобы закрыть диалоговое окно, оставив вас в редакторе кода. Нажмите на название проекта вверху и назовите его как хотите, «Демонстрация BigQuery» или подобное — мы назвали наш проект «последняя миля».

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

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

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 Sheets, чтобы поместить эти данные туда для удобства использования .

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

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

Хотя в приведенных выше шагах использовался редактор кода 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. Вернуть таблицу . Выше createColumnChart() нужен объект электронной таблицы, поэтому нам нужно обновить 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. Сделайте код более пригодным для повторного использования . Вы сделали 2 важных шага выше: возврат объекта электронной таблицы и создание управляющей функции. Что, если коллега захочет повторно использовать 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 Sheet, но на этот раз в листе рядом с данными появится диаграмма:

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

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 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 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. График возврата . Теперь, когда наша последняя функция завершена, еще раз взглянем на ее сигнатуру. Да, 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. Теперь в дополнение к созданному листу вы также должны получить новую презентацию слайдов с 3 слайдами (заголовок, таблица данных, диаграмма данных), как показано ниже:

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

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

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

bq-листы-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());
}

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

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

Связанные и общие видео

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

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

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

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

Вводный

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

Средний

• [Apps Script] Инструмент командной строки CLASP Apps Script
• [Сценарий приложений] Дополнения Gmail
• [Apps Script] Надстройка для Документов и API GCP Natural Language
• [Apps Script] Фреймворк для чата Hangouts
• [REST API] Пользовательский инструмент создания отчетов (API Таблиц)
• [REST API] Генератор пользовательских слайдов для лицензии Github Анализатор BigQuery (Slides+BigQuery API)

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

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

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

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