Как вставить api в гугл таблицы

How to consume any JSON API using Google Sheets, and keep it up-to-date automagically

Google Sheets are very powerful when you know how to use them. You can do so much with those, and at Unly, we rely on them to automate many things, without relying on developers to build custom tools. #productivity

Most of today’s online services provide ways of extracting data into a spreadsheet, whether it’s CSV, Google Sheets, Excel, etc.

But we often run into limitations when using such services, because they do not always export data the way we’d like them to.

Now, imagine if you could just plug your spreadsheet to any API, extract exactly what you want, the way you want, and use the data however you want with any other service of your liking. Sounds nice right?

We recently encountered such limitations with online services such as PipeDrive and TypeForm

PipeDrive limitations:

We wanted to export our PipeDrive contacts to a spreadsheet.
But the online export feature would not allow us to have two distinct columns for first name and last name, it would just merge both together and that was an issue for our sales team.

Such limitation sounds silly, but it actually impacted the productivity of our sales team. To get around this, we connected Google Sheet to PipeDrive API directly, to get back control on our data.

How to connect Google API to any JSON API

Now, let’s see how we do that! We’ve put together a very straightforward example. If you want to play around with it we encourage you to make a copy! (File > Make copy)

Spreadsheet example:

Spreadsheet explanation:

In this Google Sheet, look at the A1 cell, that’s where the magic is hidden!

This single line basically fetch the jsonplaceholder.typecode.com API.
(which doesn’t do anything meaningful besides displaying “todos”).
Then, it parses the JSON data returned by the API and display them below. Boom!

How does it work?

Make a copy using File > Make copy if you haven’t done that already. Making a copy will also copy the scripts that are behind the function.

In the Script Editor, you will see those 2 scripts (Tools > Script Editor):

is the script that does most of the magic, basically fetch the data, parse them and display them. It’s open source (GNU v3)
1. triggerAutoRefresh.gs is a custom script we wrote which updates the cell B1 in the “Settings” sheet, it’s a simple trick to force the sheet to refresh when the function is triggered. It is configured to refresh upon page loading for this example, and not on a regular interval (in order not to spam the API), but refreshing on a regular interval is easily doable.

I won’t explain here what the ImportJSON does, if you want to deep-dive then take a peak at https://blog.fastfedora.com/projects/import-json which gives a good overview of the features (even if it’s marked as outdated, it’s the best documentation I’ve found)

Keeping the data up-to-date automatically

If you made a copy you probably noticed it doesn’t refresh on its own. That’s because even though the scripts were copied, the triggers were not.

You have to add them yourselves, it’s pretty simple:

1. Go to https://script.google.com/home/all
2. Select your project (should be“TEMPLATE — Google Sheet to JSON API”)
3. Go to Project details > Triggers(see below)
4. Add trigger (by default it adds a trigger on the “Spreadsheet open” event)
5. Select the triggerAutoRefresh function
6. Select “Event source: Time driven”, select a 5 minutes interval and voilà!

That’s it! Your spreadsheet will now be updated every 5 min due to the trigger, which will write a new random value in the cell Settings$B$1 which in turn will trigger a refresh of the ImportJSON API calls, that will refresh the displayed data!

Demo with a real use-case

We wrote another article which showcases Typeform.
In this demo you’ll see how Google Sheets can be used to fetch real-time data from an API, as those data are created by yourself.

How building our own “Typeform to Google Sheets connector” improved our productivity

Empowering collaborators to analyse data from Typeform, without involving developers

Limitations

There are a few limitations with this kind of usage. It’s very simple to consume an API that doesn’t require an authentication, such as the one used in the example. If you need authentication, then things can get harder.

For PipeDrive for instance, since they allow authentication through a api_token to send as GET parameter (query string), it’s pretty simple to configure:

Very simple. Beware of whom you share that sheet with though, they’ll be able to read your API token.

Word of caution, if you have the ability to decide what permissions that token grants, then only allow what you use. For instance, it doesn’t make sense to use a token with write permissions if you only use it to visualize your data.

If you get stuck with authentication, there are other workarounds, such as UrlFetch (OAuth). But it won’t cover all use-cases either.

External APIs | Apps Script | Google Developers

Google Apps Script can interact with APIs from all over the web. This guide shows how to work with different types of…

The ability to consume JSON API through a Spreadsheet is very powerful.
At Unly, it gives our sales and marketing teams the ability to consume online services, without needing our developers’ intervention.

We’ve used this trick for a few months and it hasn’t let us down, we hope it will help you as much as it has helped us!

Что нам стоит автоматизацию построить. Использование HTTP API в Google Sheets

В эпоху повальной автоматизации пользователям хочется «нажать на кнопку и получить ответ». Ну или дополнительно немного подвигать мышкой. Автоматизация же отчетов и других штук, которые удобно представить в виде таблички, часто строится в Excel с использованием своих макросов или же просто встроенных формул. Плагинами к Excel нынче никого уже не удивишь, кстати, у нас такой тоже есть, но это предмет отдельной статьи. А как насчет Google Sheets? Ранее мой коллега рассказывал, как можно прикрутить наше API к Telegram, я же попробую рассказать, как использовать его в гуглотаблицах.

Под катом чуть-чуть кода и много костылей.

Работать мы будем, очевидно, в браузере. Для написания своих функций будем использовать Google Apps Script, который по синтаксису подозрительно похож на урезанный javascript. Исходим из принципа, что кодить мы не умеем, а читать документацию не хотим, зато активно используем подходы, изложенные в технике Stackoverflow Driven Development.

Если вы начинающий трейер, то предлагаем вам почитать тут.

Подготовка

Для начала получаем доступ к API. Бесплатно (если только аккаунт-менеджеры не замучают звонками) и без смс, но с регистрацией. Документацию читать не будем (все равно там картинок нет), а токен для доступа мы сгенерируем руками через jwt.io. Почему руками? Потому что токен, генерируемый нашим сайтом, истекает через час. Это полезно, например, для использования на вебсайте, но для нормальной работы в Sheets мы хотим, чтобы он жил дольше, допустим, год. Подробнее о процедуре создания токена можно почитать здесь.

Работа с API

Теперь создаем пустую таблицу и идем в редактор скриптов; если кто не знает, попасть туда можно путем вызова Tools → Script editor. В редакторе объявим несколько глобальных переменных:

Также зададим функции для работы с запросами:

Подробнее про UrlFetchApp и его аргументы можно почитать здесь. Дополнительно мы вылавливаем коды, отличные от 200, и показываем пользователю «человекочитаемую» ошибку из запроса.

Статическая информация из API

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

Для начала напишем метод, реализующий запрос финансовых инструментов. Как мне подсказывают, для экономии трафика информацию об инструментах разделили в два конца — /symbols/:symbolId и /symbols/:symbolId/specification :

Здесь и далее имя финансового инструмента ( symbol ) должно кодироваться, хотя бы потому что может содержать странные символы, например, / .

Затем создадим аналогичные методы для работы с опционами и фьючерсами.

Котировки и «свечки»

Свечки — это такой специальный индикатор на финансовых графиках. Для понимания того, что мы делаем, достаточно знать, что одна «свечка» представлена четырьмя значениями — [цена_на_начало_интервала, максимальная_цена_в_интервал, минимальная_цена_в_интервал, цена_на_конец_интервала] . Интервал у нас задается в секундах, в общем виде функция будет выглядеть так:

Тогда запрос наподобие EXANTEOHLC(«EUR/USD.E.FX», 60, «high») вернет нам максимальную цену за последнюю минуту.

С котировками чуть сложнее. На момент написания статьи единственное API для получения котировок — это стрим, который неудобно использовать в Apps Script. (Кстати, обещают добавить новое API для единичной котировки в будущих релизах). Поэтому пришлось накостылить решение из имеющихся средств. По построению, close незакрытой свечки (то есть за текущие минуту/час/день) — это среднее между последними пришедшими ценами покупки и продажи, поэтому:

Для полного счастья можно еще сделать функцию конвертации из одной валюты в другую:

Использование

Теперь мы попробуем использовать наши функции как обычные методы в Excel. Первая же проблема, с которой мы столкнемся — это обновление значений. Дело в том, что Google считает, что нет нужды часто пересчитывать пользовательскую функцию, если параметры не изменились. В случае котировок, которые предполагаются как «live», это немного критично. Для обхода данной проблемы добавим еще один «изменчивый» (а на самом деле нет), но не используемый аргумент в наши функции EXANTEOHLC , EXANTECROSSRATES и EXANTEMID и назовем его timestamp :

Теперь реализуем функцию, которая будет генерировать этот timestamp .

Обратите внимание, что мы нагло приватизировали ячейку A1 , а заодно и потребовали дополнительных прав на модификацию листа. Для повышения безопасности гугл рекомендует вставить @OnlyCurrentDoc , чтобы скрипт не просил права сразу на все документы:

Кстати, на первый взгляд, можно было бы просто использовать функцию NOW() (она одна из немногих умеет пересчитываться раз в минуту, при наличии специальной галочки в настройках Юзабилити), но ее значение нельзя передать в пользовательскую функцию, печаль.

Для автоматического обновления данных раз в минуту можно создать триггер для написанной функции в Edit → Current project’s triggers:

Для полного пользовательского счастья дополнительно можно добавить кнопку (делается в нашей табличке через Insert → Drawing. ) и связать ее с функцией EXANTEUPDATE .

О, кажется, теперь с этим можно работать. Давайте попробуем взять ближайший фьючерсный контракт на FORTS:Si (который USD/RUB) и посмотреть на его свечки:

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

Но методы обхода этой проблемы я предлагаю найти читателю самостоятельно 🙂 Вероятно, не лучшее, но вполне рабочее решение для однотипных запросов, где мы забираем из JSON только одно поле (например, EXANTEOHLC ) — использовать кэш в глобальных переменных. Более правильное решение — в одном запросе (например, для свечек) посылать списки из нескольких финансовых инструментов, разделенных запятой.

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

Опциональный пункт, который я упустил в ходе повествования. Можно оформить комментарии к функциям в соответствие с JSDoc и дополнительно добавить @customfunction , например:

В таком случае пользователь увидит красивую справочку о том, как правильно использовать данную функцию, какие аргументы она требует и что возвращает. Следует отметить, что парсит гугл докстринг по своему усмотрению, но в целом очень похоже на JSDoc.

На этом все. Кажется, теперь можно пользоваться и опубликовать. Только токен вырежьте 🙂 Исходный код этого «скрипта» можно найти на гитхабе под MIT лицензией.

Как подключить API Google Search Console к Google Таблицам

В статье ( «Google Apps Script: полезные функции и фишки для SEO (часть первая)» ) я демонстрирую скрипты, которые будут работать с API Google Search Console (и другими сервисами Google, так как подключение к API происходит подобным образом). Чтобы облегчить настройку, буду ссылаться на эту статью.

Перед тем как перейти к делу, давайте разберёмся, зачем подключать API Google Search Console к Google Таблицам.

С помощью этой настройки вы сможете выгружать из Google Search Console (GSC) такие данные:

1. Search Analytics: это то, что мы можем найти в разделе «Эффективность» в GSC (в старой версии GSC этот раздел называется «Анализ поисковых запросов»).
2. Sitemaps: добавлять и удалять карты сайта, получать информацию о конкретных картах сайта.
3. Sites: добавлять и удалять сайты (в GSC), получать информацию и списки сайтов в GSC.
4. URL Crawl Errors Counts. Получать количество ошибок по типам: authPermissions, flashContent, manyToOneRedirect, notFollowed, notFound, other, roboted, serverError, soft404. В разрезе mobile, smartphoneOnly и web.
5. URL Crawl Errors Samples. Извлекать сведения об ошибках, получать список URL определенных ошибок, помечать URL предоставленного сайта, как «исправленный» и удалять его из списка.

Как подключить API Google Search Console к Google Таблицам

1. Делаем копию таблицы, к которой подключим API Google Search Console. К ней уже подключена библиотека OAuth2.

1. Заходим на сайт Google Cloud Platform.

1. Мы попали в Google Cloud Platform. Затем в поисковой строке Google Cloud Platform нужно ввести «Search Console API» и перейти далее.

1. Нажимаем на кнопку «Включить» («Enable»).

1. Далее переходим в «Учетные данные» («Credentials»).

1. Переходим по ссылке:

1. Создаем учетную запись «Идентификатор клиента OAuth».

1. Теперь выбираем «Веб-приложение».

Если поле неактивно, нужно перейти по ссылке «Настроить окно запроса доступа» и заполнить все поля. После нужно сохранить и продолжить настройку — нажать “Теперь выбираем «Веб-приложение»”.

1. Ниже, в поле «Разрешенные URI перенаправления» («Authorized redirect URIs») нужно вставить строку: https://script.google.com/macros/d/<ключ_проекта>/usercallback

• ( <ключ_проекта>нужно взять в редакторе Apps Script, кликнув на «Файл» — «Свойства проекта» — «Ключ проекта»).

1. Далее появится окно с идентификатором и секретом клиента (your client id and your client secret). Сохраните себе эти строки, они понадобятся для работы с API Google Search Console в Google Таблицах.

1. Далее в скрипте во вкладке «Variables», в переменной «CLIENT_ID» вставляем «Ваш идентификатор клиента». В переменной «CLIENT_SECRET» вставляем «Ваш секрет клиента» (эти данные мы получили на прошлом шаге) и нажимаем «Сохранить».

1. Переходим в наш ранее скопированный документ и запускаем скрипт для окончательной авторизации:

1. Получаем разрешение.

1. Выбираем аккаунт, который нам нужен:

1. Следуем подсказкам на скриншоте:

1. Жмём «Разрешить»:

1. Копируем URL и в новой вкладке переходим по нему.

1. Если все настроили верно, нужно будет указать свой профиль и дать разрешение, как мы это делали в пунктах 16 и 18.

1. Если видим такое окно, значит API Google Search Console подключили:

1. Чтобы убедиться в этом, переходим в наш документ и заново запускаем скрипт, как это было в пункте 14. В результате во вкладке «Sites» появятся все сайты, которые есть в аккаунте Google Search Console.

Не получается подключиться? Пишите в комментариях, постараюсь помочь.

Кстати, скоро будет второй пост из цикла статей о Google Apps Script, не пропустите.

Выводы

Изучив возможности API методов GSC, можно автоматизировать процесс проверки сайта в этом сервисе. В цикле статей о Google Apps Script я поделюсь скриптом, который регулярно автоматически выгружает нужные нам данные. Затем, если есть какие-то существенные изменения, отсылает письмо на почту. Кроме того, с помощью Google Apps Script можно и отправлять email .

Информация в посте актуализирована Евгением Лукьянюком в октябре 2021 года.

С SEO познакомился в 2015 году.
Работал и обучался в компании Palmira Studio.
C августа 2015 года работаю SEO-специалистом в агентстве интернет-маркетинга Netpeak.
Сертификация: Google Analytics, Google AdWords по направлениям «поисковая и мобильная реклама».

API to Google Sheets – How You Can Connect With and Without Coding

The main reason for connecting API to Google Sheets is to transfer information to a spreadsheet on a recurrent schedule. This allows you to automate the process and forget about having to manually export/import the data you need. For this, you only need to write a few lines of code and enjoy the automation. Now, what about the non-tech-savvy users who do not have any coding skills? They also have a solution which lets them import API data to Google Sheets with a few clicks. Read on to discover both options that can make your life easier.

What is API – a refresher for non-techs

API is the acronym for Application Programming Interface. Let’s clarify this:

• Interface – a method or a mechanism
• Application Programming – interaction or communication between two applications or software

So, an API is a mechanism for communication between one app and another. In our case, API to Google Sheets, we will use API to connect an app to Google Sheets.

REST API meaning

Now that it’s clear what API means, what about REST API? REST is a set of architectural principles that make up the API. The essence of it is the following:

• When a client app sends a request to a server app, it gets a representation of the state of the resource. This information is delivered via HTTP in a specific format. The most popular format for the representation information is JSON, which is language-agnostic and human readable at the same time.

In view of this, you may encounter APIs that are called JSON APIs, Web APIs, or even HTTP APIs. They mostly all mean the same thing. So, let’s discover how you can connect your Google spreadsheet to the API.

Options to connect REST API to Google Sheets

There are two common ways to add JSON API to Google Sheets: code and no-code.

• The code option is suitable for tech-savvy users who are more or less proficient in Google Apps Script, Python, or another programming language.
• The no-code option involves the use of Coupler.io and its JSON integration, which allows you to schedule recurrent imports.

Let’s start with the no-code solution first.

How to import API to Google Sheets without coding

We’ll import the API to Google Sheets with the help of Coupler.io, a data integration tool. It provides multiple ready-to-use integrations between different data sources, such as Airtable, Xero, Jira Cloud, and three destinations: Google Sheets, Excel, and BigQuery.

Streamline your data analytics & reporting with Coupler.io!

Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!

• Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
• Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
• Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
• Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
• Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.

Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.

Check out the full list of Google Sheets integrations available.

Additionally, Coupler.io provides a JSON integration that allows you to integrate Google Sheets with different platforms, such as Salesforce, Typeform, Help Scout, and more. The only prerequisite is having a REST API available.

You don’t have to be a programmer to configure and master the JSON integration. However, using API requires technical knowledge, so get ready to work with API documentation of the JSON data source. Each API may require specific workarounds, such as handling authentication, pagination, rate limits, and so on.

If you don’t have a technical background, don’t worry. This article will explain the main points you need to take into account when connecting API to Google Sheets.

How to pull API data into Google Sheets with Coupler.io

Sign up to Coupler.io with your Google account.

Note: Alternatively, you can install the Coupler.io add-on for Google Sheets from the Google Workspace Marketplace, then perform the setup right in your spreadsheet.

Click the Add new importer button, then select JSON as a source app and Google Sheets as a destination app. After that, you’ll need to configure connection to the chosen apps.

Source

• Insert the JSON URL string – this is the API URL + the endpoint where the HTTP requests are sent. The endpoint is the URL postfix which differs depending on the type of data loaded from the API. You can find the JSON URL in the RESTful API documentation of your data source. For example, the JSON URL used to get a list of docs from Coda is the following:

• Click Continue to set up Advanced Settings for the Source:

• HTTP method – you can pick an HTTP method for making a request based on the documentation of your data source platform. GET is the default method.
• HTTP headers – you can apply specific HTTP headers to your request. For example, the Authorization header lets you specify credentials required to make an HTTP request. This is what it looks like for Coda:

• URL query string – you can use filter parameters if they are associated with the JSON URL of the API. For example, here is the URL query string to filter the list of conversations by mailbox and status in the Help Scout API:

• Request body – if your request method is POST, PUT, PATCH or DELETE, you can add data to your request to be sent to the API. You can check out what it looks like in our blog post: Post Messages to Slack from Google Sheets.
• Fields – you can specify the fields (columns) to be imported to your spreadsheet. For example,

• Path – you can select nested objects from the JSON response. In most cases, this allows you to exclude any unnecessary information in the data exported from the API. For example, using the following Path parameter, the data from Help Scout will be placed in multiple rows.

Note: You can find information about the parameters used in those fields in the API documentation of your application. Pay attention to the information about API authentication and details of API endpoints – these are usually located in separate sections.

Once you’re ready with the Source setup, Jump to the Destination Settings.

Destination

You can also change the destination app to Microsoft Excel if you need to connect API to Excel.

• Select a Google Sheets file on your Google Drive that will be the destination for the transferred data. Select an existing sheet or enter a name to create a new one. Click Continue.

• If you want to change the first cell for your imported data range, specify your value in the Cell address field. The A1 cell is set by default.
• Choose the import mode for your data: you can replace your previous information or append new rows under the most recently imported entries.

• Toggle on the Last updated column feature if you want to add a column to the spreadsheet that contains the date and time of the last refresh.

Schedule

If you want to automate data imports on a schedule, toggle on the Automatic data refresh and customize the schedule:

• Select Interval from 15 minutes to once per month
• Select Days of the week
• Select Time preferences
• Schedule Time zone

Once you’ve set up your API to Google Sheets connection, click Save And Run to get data to your spreadsheet. Let’s check out how it works in an example.

Example of how to add JSON API to Google Sheets

We’re going to import forms from Typeform. First we should read Typeform’s API documentation. The JSON URL to request all form responses of a typeform is the following:

For HTTP requests to Typeform API, we need to use the Authorization HTTP header.

Note: You can read about how and where to get your Typeform personal access token in our Typeform to Google Sheets guide.

Here is how the Source parameters of the JSON integration should look:

Click Save & Run and welcome your JSON data from Typeform API into Google Sheets:

We encourage you to check out other articles featuring the JSON integration to get data from APIs to Google Sheets:

Streamline your data analytics & reporting with Coupler.io!

Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!

• Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
• Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
• Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
• Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
• Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.

Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.

How to use an external API in Google Sheets using code

Above, we’ve added JSON API to Google Sheets without a line of code, right? Now, let’s take a look at the dark side of the moon. We’ll explore the most obvious solution based on the Google Apps Script in Google Sheets.

How to pull API data into Google Sheets with Apps Script?

The idea of this approach is to create a custom Google Sheets function that will fetch and convert JSON data either manually or automatically.

Open your Google Sheets doc and go to Tools => Script editor.

Add the following code created by Brad Jasper and Trevor Lohrbeer to the Script Editor, name your project and click Save:

This Apps Script accumulates a few functions for you to import JSON from API to Google Sheets:

• ImportJSON() – to import JSON from an API URL.
• ImportJSONFromSheet() – to import JSON from one of the Sheets.
• ImportJSONViaPost() – to import JSON from an API URL using POST parameters.
• ImportJSONBasicAuth() – to import JSON from an API URL with HTTP Basic Auth.
• ImportJSONAdvanced() – to import JSON using advanced parameters.

Learn more about the script at Brad’s Github.

These custom functions work the same way as most Google Sheets functions. For example, here is the syntax of ImportJSON() :

• url is the API URL to a JSON file
• query is a comma-separated list of paths to import (optional parameter)
• parseOptions is a comma-separated list of options that alter processing of the data (optional parameter)

And here is how it works in action. We’ve used the function to import the current foreign exchange rates from the Exchange rates API:

ImportJSON() works for publicly available JSON APIs. So, if you need to parse JSON data from an API that requires an API token for authorization (Typeform, for example), the function will fail. However, this can be fixed as follows.

Apps Script to upload JSON to Google Sheets using an API token

Add the following code snippet to the script in your Script Editor and save the project.

It creates a new function called ImportJSONAuth(), which adds the Authorization header to the HTTP request from Google Sheets to the target API. All you need to do is call the function from the spreadsheet and specify two required parameters:

• URL of the JSON API
• API token in the format: Bearer

Check out how it works:

Check out how we used this to import data from GitHub to Google Sheets.

Connect API to Google Sheets on a schedule

In the Script Editor, you can set up time-driven triggers to run your custom functions. To do this, go to Triggers:

Add a trigger to automate import of data from API to Google Sheets. We explained in detail how you can do this in the tutorial on How to Export Google Calendar to Google Sheets.