Веб-сервисы 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 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} (и не сохраняйте фигурные скобки).