Руководство по интеграции API Google Таблиц v4

Веб-сервисы Google стали неотъемлемой частью инфраструктуры многих проектов, жизненно важным элементом интеграции. Мы уже не можем представить онлайн-сервисы без них. Тем временем разработчики Google работают над расширением возможностей своих сервисов, разработкой новых API и повышением безопасности наших данных. Обычно обновления выпускаются гладко для пользователей и не требуют каких-либо изменений с вашей стороны. Но не в этот раз с новым API Google Sheets.

Предисловие: Прогресс — это боль

В 2021 году Google представила 4 версию своего API Sheets, которая несовместима с предыдущей. Это повлияло на безопасность данных и конфиденциальность. Поддержка Sheets API версии 3 была продлена до августа 2021 года, чтобы предоставить разработчикам больше времени для перехода на новую версию API. После прекращения поддержки API v3 многие разработчики JavaScript столкнулись с проблемами миграции. И хотя Google предоставил подробное руководство по миграции, как это обычно бывает, в нем отсутствуют несколько важных деталей.

Как инженер службы поддержки AnyChart, я получал и продолжаю обрабатывать многочисленные запросы о помощи от пользователей нашей библиотеки диаграмм JS, которые внезапно столкнулись с проблемами при загрузке визуализаций данными из своих электронных таблиц Google. Это показывает, что проблема была и остается действительно актуальной. Поэтому я решил сделать краткое руководство по интеграции Google Sheets API v4 для всех остальных.

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

Содержание

Доступ к таблицам Google из JavaScript
Конфигурация на стороне Google
Доступ к данным электронной таблицы Google из приложений JavaScript
1) Создание базового приложения JavaScript
2) Редактирование вывода — показать данные в виде диаграммы

Доступ к таблицам Google из JavaScript

Чтобы получить доступ к электронной таблице Google Sheets из кода JavaScript, вам потребуется google-api-javascript-client и Sheets API, а также настроенный проект Google и сам документ.

Позвольте мне провести вас через все это шаг за шагом.

Конфигурация на стороне Google

1) Создать проект

Перейдите на облачную платформу Google :
Создайте новый проект:

2) Включить API

Перейдите к «Включить APIS и сервисы»:
Введите «Google Sheets» в поле поиска, чтобы найти API:
Выберите «API Google Таблиц»:
Включите API Google Таблиц:

3) Полномочия

1. Перейдите на вкладку «Учетные данные»:

2. Нажмите «Создать учетные данные» и выберите «Ключ API»:

Примечание. Скопируйте и сохраните ключ API. Он понадобится вам позже в коде JavaScript ( {GOOGLE_API_KEY}в коде JS).

3. Нажмите «Ограничить ключ»:

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

4. В раскрывающемся меню «Ограничить ключ» найдите пункт «API Google Sheets»:

5. Выберите его, нажмите «ОК» и «СОХРАНИТЬ»:

4) Создать документ

1. Создайте документ Google Sheets, как обычно, и заполните его некоторыми данными. Задайте имя для листа с вашими данными или скопируйте имя по умолчанию — оно потребуется позже в JS-коде ( {SHEET_NAME} ).

2. Разрешить доступ к документу по ссылке. Вы можете сделать это, нажав кнопку «Поделиться» и выбрав «Все, у кого есть ссылка». (Достаточно доступа «Просмотрщик».)

3. Скопируйте идентификатор документа. Его можно найти в URL-адресе документа между частями «/spreadsheets/d/» и «/edit». Этот идентификатор потребуется позже в коде JS ( {SPREADSHEET_ID} ).

Все необходимые настройки на стороне Google выполнены. Перейдем к приложению.

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

Теперь я объясню, как создать простое приложение JavaScript, которое извлекает данные из электронной таблицы и показывает их пользователям. Для подключения приложения к Sheets API я буду использовать клиентскую библиотеку Google API для JavaScript (она же gapi), которая хорошо описана в ее репозитории на GitHub.

1) Создание базового приложения JavaScript

В первую очередь включите библиотеку gapi на свою страницу по прямой ссылке.

Добавьте <table>тег в код HTML и примените понравившийся код CSS для таблицы и ее будущего содержимого.

В коде JavaScript создайте функцию, которая будет использоваться для получения данных.

const start = () => {};

Внутри этой функции инициализируйте клиент gapi с помощью созданного ранее ключа Google API.

 gapi.client.init({
    'apiKey': '{GOOGLE_API_KEY}',
    'discoveryDocs': ["https://sheets.googleapis.com/$discovery/rest?version=v4"],
  })

Затем выполните запрос на получение значений через клиент gapi. В запросе вы должны указать идентификатор электронной таблицы и диапазон ячеек, в которых находятся данные, к которым вы хотите получить доступ.

.then(() => {
    return gapi.client.sheets.spreadsheets.values.get({
      spreadsheetId: '{SPREADSHEET_ID}',
      range: '{SHEET_NAME}!{DATA_RANGE}', // for example: List 1!A1:B6
    })
  })

Если все настройки верны, разрешенный промис возвращает ответ с извлеченными данными. Теперь вы можете получить данные из ответа и заполнить HTML-таблицу с помощью простого JS-скрипта.

.then((response) => {
    // parse the response data
    const loadedData = response.result.values;

    // populate the HTML table with the data
    const table = document.getElementsByTagName('table')[0];
    
    // add column headers
    const columnHeaders = document.createElement('tr');
    columnHeaders.innerHTML = `<th>${loadedData[0][0]}</th>
<th>${loadedData[0][1]}</th>`;
    table.appendChild(columnHeaders);

    // add table data rows
    for (let i = 1; i < loadedData.length; i++) {
      const tableRow = document.createElement('tr');
      tableRow.innerHTML = `<td>${loadedData[i][0]}</td>
<td>${loadedData[i][1]}</td>`;
      table.appendChild(tableRow);
    }
  }).catch((err) => {
  	console.log(err.error.message);
  });

Чтобы выполнить код, вызовите функцию load() из библиотеки gapi и передайте созданную выше функцию в качестве аргумента.

gapi.load('client', start);

Полученное приложение выглядит так, как показано ниже. Вы можете ознакомиться с полным шаблоном кода этой HTML-таблицы с данными из Google Sheets на JSFiddle . Чтобы ваша собственная вещь работала, просто замените {GOOGLE_API_KEY} , {SPREADSHEET_ID} , {SHEET_NAME} и {DATA_RANGE} своей собственной информацией (и не сохраняйте фигурные скобки).

2) Редактирование вывода — показать данные в виде диаграммы

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

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

Прежде всего, добавьте базовый JS-модуль AnyChart в HTML:

<script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-base.min.js"></script>

Кроме того, добавьте <div>теги для контейнеров панели мониторинга и примените для каждого подходящий идентификатор:

<div id="container1"></div>
<div id="container2"></div>

Большая часть кода JavaScript остается абсолютно неизменной. Я просто переработаю код, обрабатывающий ответ Sheets API.

Итак, оставьте первую часть кода JS без изменений:

const start = () => {
  // Initialize the JavaScript client library
  gapi.client.init({
    'apiKey': '{GOOGLE_API_KEY}',
    'discoveryDocs': ["https://sheets.googleapis.com/$discovery/rest?version=v4"],
  }).then(() => {
    return gapi.client.sheets.spreadsheets.values.get({
      spreadsheetId: '{SPREADSHEET_ID}',
      range: '{SHEET_NAME}!{DATA_RANGE}', // for example: List 1!A1:B6
    })
  }).then((response) => {

In the response handler, parse the data to compose a structure compatible with the AnyChart API:

    const loadedData = response.result.values;
    const parsedData = {
      'header': loadedData.shift(),
      'rows': loadedData,
    };

Now we’ve got everything we need to create and configure charts for the dashboard: 

    // create an instance of a column chart
    const columnChart = anychart.column();

    // set the data
    columnChart.data(parsedData);

    // configure chart appearance settings
    columnChart.title('Sales volume by manager');
    columnChart.xAxis().title('Manager');
    columnChart.yAxis().title('Sales volume, $');

    // set the container element and draw the chart
    columnChart.container('container1').draw();

    // create a pie chart likewise
    const pieChart = anychart.pie(parsedData);
    pieChart.title('Sales volume distribution in the department');
    pieChart.legend().itemsLayout('vertical').position('right');
    pieChart.container('container2').draw();

Then goes the same ending part as with the HTML table — let’s recall it just in case:

  }).catch((err) => {
  	console.log(err.error.message);
  });
};

// load the JavaScript client library
gapi.load('client', start);

Ниже показано, как выглядит получившаяся информационная панель. Вы можете ознакомиться с полным кодом шаблона этой панели инструментов, визуализирующей данные из Google Sheets с помощью API версии 4 на JSFiddle. Чтобы получить свой собственный проект, просто укажите свою информацию вместо {GOOGLE_API_KEY}, {SPREADSHEET_ID}, {SHEET_NAME} и {DATA_RANGE} (и не сохраняйте фигурные скобки).