Еще несколько дней назад я не был большим поклонником Google Таблиц. Я имею в виду, насколько хороши вы можете получить программное обеспечение для работы с электронными таблицами? Затем я узнал, что Google Таблицы могут запускать JavaScript и выполнять задания cron. Ага. Вы правильно поняли. Если вы удивлены, как и я, продолжайте читать. Я покажу, как писать и выполнять Javascript и запускать задания Cron в Google Таблицах, построив трекер Covid-19 менее чем за 15 минут. Идея поста в блоге состоит в том, чтобы продемонстрировать, насколько мощна экосистема Google Sheet Ecosystem и как вы можете построить на ее основе некоторые из своих побочных проектов, не написав тонны кода.
Я покажу, как писать и выполнять Javascript и запускать задания Cron в Google Таблицах, построив трекер Covid-19. Идея поста в блоге состоит в том, чтобы продемонстрировать, насколько мощна экосистема Google Sheet Ecosystem и как вы можете построить на ее основе некоторые из своих побочных проектов, не написав тонны кода.
Для простоты мы создадим трекер, который отслеживает общее количество обращений по всему миру. Для этого нам нужны 3 компонента.
- Таблица Google для хранения даты и зарегистрированных случаев.
- Синхронизация таблиц Google с данными реального времени
- Отображение данных из Google Таблиц в виде интерактивного графика.
1. Таблица Google для хранения даты и количества обращений.
Первое, что нам нужно создать, - это лист Google для хранения данных. Вы можете создать новый лист, перейдя на https://sheet.new.
Используйте столбец A таблицы, в котором хранится дата, и столбец B, чтобы сохранить общее количество зарегистрированных случаев. Теперь продолжайте и назовите столбец A как date, а столбец B как cases.
Затем нам нужно заполнить лист историческими данными о случаях Covid-19. Для этого мы можем использовать следующий API.
Https://corona.lmao.ninja/v2/historical/all
Этот API возвращает глобальные исторические данные о Covid-19. Вы можете нажать на ссылку, чтобы увидеть, как будет выглядеть ответ.
Но как получить данные из API и вставить их в Google Таблицы?
Для этого мы можем использовать Google AppsScript.
Google AppsScript - это платформа для быстрой разработки приложений, которая позволяет быстро и легко создавать бизнес-приложения, интегрируемые с G Suite.
Если в этом нет особого смысла, вы можете думать о AppsScript как о редакторе кода, в котором вы можете запускать и выполнять Javascript. Он поставляется с предустановленными библиотеками, которые вы можете использовать для чтения и редактирования данных в продуктах GSuite.
Вы можете получить доступ к редактору кода AppsScript, открыв меню Tools и нажав Code Editor.
Теперь скопируйте и вставьте следующий код в редактор.
function getHistoricalData() { const url = "https://corona.lmao.ninja/v2/historical/all"; var response = UrlFetchApp.fetch(url); return JSON.parse(response)["cases"]; }function populateHistoricalData() { const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); const historicalData = getHistoricalData();for (const date in historicalData) { const cases = historicalData[date]; sheet.appendRow([date, cases]); } }
Код сделает запрос к https://corona.lmao.ninja/v2/historical/all, получит результат, проанализирует и сохранит его в вашей таблице Google.
Сохраните код, нажав кнопку «Сохранить» в меню «Файл». После этого выберите populateHistoricalData в качестве функции по умолчанию для выполнения.
Теперь нажмите кнопку «Выполнить» ▶ ️. Это запустит populateHistoricalData функцию. Функция заполняет лист Google историческими данными из API. Если все работает нормально, ваш лист будет выглядеть примерно так.
2. Синхронизация таблиц Google с данными реального времени.
Данные, которые мы вставили в Google Таблицы, являются историческими и не включают наблюдения за текущий день. Итак, нам нужно найти способ поддерживать лист в актуальном состоянии.
Мы можем сделать это, посылая каждые несколько минут запросы к API, который возвращает самые свежие данные.
Https://corona.lmao.ninja / all - это такой API.
Следующий код запрашивает https://corona.lmao.ninja/all и обновляет счетчик сегодняшнего дня.
function getTotalCasesSoFar() { const url = "https://corona.lmao.ninja/all"; var response = UrlFetchApp.fetch(url); var jsonResponse = JSON.parse(response); const casesSoFar = jsonResponse["cases"]; return casesSoFar; }function updateTotalCasesToday() { const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); const todayDate = new Date(); todayDate.setHours(0, 0, 0, 0); const lastRowId = sheet.getLastRow(); const lastDate = sheet.getRange("A" + lastRowId).getValue(); const casesSoFar = getTotalCasesSoFar(); if (lastDate.getTime() === todayDate.getTime()) { const oldCountCell = sheet.getRange("B" + lastRowId); oldCountCell.setValue(casesSoFar); } else { sheet.appendRow([todayDate, casesSoFar]) } }
Скопируйте и вставьте код под нашим существующим кодом в редактор скриптов. Теперь выберите updateData в качестве функции по умолчанию и нажмите "Выполнить". Если все пойдет хорошо, вы увидите, что вставляется новая строка, которая содержит текущую дату и текущие дела. Если вы запустите код еще раз, он попытается обновить обращения, сделав еще один запрос к API. Обновление происходит при изменении номера.
Теперь, когда у нас есть готовый код для синхронизации данных, нам нужно найти способ автоматического запуска этого кода. Для нас не рекомендуется нажимать кнопку «Выполнить» ▶ ️ каждые несколько минут. Нам нужно задание cron, которое запускает этот скрипт каждые несколько минут. Для этого мы можем использовать Triggers.
Откройте меню редактирования и нажмите Current project's triggers. Откроется панель, которая выглядит следующим образом.
Вы можете создать новый триггер, щелкнув один create a new trigger. Это откроет форму. Установите функцию как updateTotalCasesToday и источник события как Time-driven. Вы можете установить minute interval по своему усмотрению. В моем случае я установил каждые 10 минут.
Щелкните Save, чтобы создать триггер.
Вуаля! Скрипт Google Apps будет вызывать функцию каждые несколько минут.
Теперь, когда наш лист синхронизирован с последними данными, приступим к последнему шагу.
3. Отображение данных из Google Sheet в виде интерактивного графика.
Для этого мы можем использовать Google Data Studio. Google Data Studio - это продукт, используемый для создания интерактивных отчетов и информационных панелей. Мы можем использовать его для создания графика временных рядов из нашей таблицы Google.
Перейдите на https://datastudio.google.com/ и создайте новый отчет.
Теперь выберите Google Таблицы в качестве источника данных и выберите Лист, который вы создали на предыдущем шаге.
Теперь щелкните меню Insert и выберите Time series. Это добавит график в ваш отчет.
Теперь в правой боковой панели установите значение Date Range Dimension на Date и Dimension на Date столбец. Установите для метрики значение Cases и выберите агрегатор Max. Агрегатор Max использует максимальное количество значений наблюдений, если имеется более одной строки с одинаковой датой.
Теперь отчет должен содержать график временных рядов.
Вы также можете вставить Date range, который позволяет пользователям изменять диапазон дат. Для этого нажмите «Вставить» и выберите Date range. Окончательный результат будет выглядеть примерно так.
Вы можете поделиться отчетом с кем угодно, нажав «Поделиться». Перейдите по ссылке ниже, чтобы увидеть, как отчет будет выглядеть, если кому-то поделились.
Https://datastudio.google.com/open/14nOy1s9OWDxNRsGjPHFD1FqyO5MQqT-y
Вы также можете встроить отчет на любой веб-сайт, щелкнув меню File и выбрав Embed report. Вы можете увидеть отчет, встроенный ниже, в качестве примера.
Здорово. Мы создали трекер Covid-19. Подвести итог
- Мы узнали, как управлять таблицами Google с помощью JavaScript.
- Как запускать задания cron с помощью триггеров.
- Как создавать интерактивные графики с помощью Google Data Studio.
Учебник предназначен только для демонстрационных целей. Я бы не стал использовать код, данные и т. Д. В производстве без должной осмотрительности. Надеюсь, этот пост в блоге был полезен :)
