API to Google Sheets With/Without Coding | Coupler.io Blog (2024)

The instructions provided in this article are sufficient for creating an automated integration. But please note that we are not responsible for the outcome if you use the instructions incorrectly, specify incorrect settings, or if there are changes in the app’s API. If you need help with the setup, we can offer this premium integration at an additional fee.

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.

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:

https://coda.io/apis/v1/docs

• 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:

Authorization: Bearer {API-token}

• 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:

mailbox: {mailbox_id}status: open

• 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,

customer_firstname, customer_lastname, customer_email, status

• 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.

_embedded.mailboxes

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:

https://api.typeform.com/forms/

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

Authorization: Bearer {your_access_token}

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:

• How to retrieve data from Yahoo Finance
• How to import currency rates data from financial services APIs into Google Sheets
• How to export Magento orders to Google Sheets

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:

function ImportJSON(url, query, parseOptions) { return ImportJSONAdvanced(url, null, query, parseOptions, includeXPath_, defaultTransform_);} function ImportJSONViaPost(url, payload, fetchOptions, query, parseOptions) { var postOptions = parseToObject_(fetchOptions); if (postOptions["method"] == null) { postOptions["method"] = "POST"; } if (postOptions["payload"] == null) { postOptions["payload"] = payload; } if (postOptions["contentType"] == null) { postOptions["contentType"] = "application/x-www-form-urlencoded"; } convertToBool_(postOptions, "validateHttpsCertificates"); convertToBool_(postOptions, "useIntranet"); convertToBool_(postOptions, "followRedirects"); convertToBool_(postOptions, "muteHttpExceptions"); return ImportJSONAdvanced(url, postOptions, query, parseOptions, includeXPath_, defaultTransform_);} function ImportJSONFromSheet(sheetName, query, options) { var object = getDataFromNamedSheet_(sheetName); return parseJSONObject_(object, query, options, includeXPath_, defaultTransform_);} function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) { var jsondata = UrlFetchApp.fetch(url, fetchOptions); var object = JSON.parse(jsondata.getContentText()); return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc);} function ImportJSONBasicAuth(url, username, password, query, parseOptions) { var encodedAuthInformation = Utilities.base64Encode(username + ":" + password); var header = {headers: {Authorization: "Basic " + encodedAuthInformation}}; return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_);} function URLEncode(value) { return encodeURIComponent(value.toString()); } function AddOAuthService__(name, accessTokenUrl, requestTokenUrl, authorizationUrl, consumerKey, consumerSecret, method, paramLocation) { var oAuthConfig = UrlFetchApp.addOAuthService(name); if (accessTokenUrl != null && accessTokenUrl.length > 0) { oAuthConfig.setAccessTokenUrl(accessTokenUrl); } if (requestTokenUrl != null && requestTokenUrl.length > 0) { oAuthConfig.setRequestTokenUrl(requestTokenUrl); } if (authorizationUrl != null && authorizationUrl.length > 0) { oAuthConfig.setAuthorizationUrl(authorizationUrl); } if (consumerKey != null && consumerKey.length > 0) { oAuthConfig.setConsumerKey(consumerKey); } if (consumerSecret != null && consumerSecret.length > 0) { oAuthConfig.setConsumerSecret(consumerSecret); } if (method != null && method.length > 0) { oAuthConfig.setMethod(method); } if (paramLocation != null && paramLocation.length > 0) { oAuthConfig.setParamLocation(paramLocation); }} function parseJSONObject_(object, query, options, includeFunc, transformFunc) { var headers = new Array(); var data = new Array(); if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) { query = query.toString().split(","); } // Prepopulate the headers to lock in their order if (hasOption_(options, "allHeaders") && Array.isArray(query)) { for (var i = 0; i < query.length; i++) { headers[query[i]] = Object.keys(headers).length; } } if (options) { options = options.toString().split(","); } parseData_(headers, data, "", {rowIndex: 1}, object, query, options, includeFunc); parseHeaders_(headers, data); transformData_(data, options, transformFunc); return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;} function parseData_(headers, data, path, state, value, query, options, includeFunc) { var dataInserted = false; if (Array.isArray(value) && isObjectArray_(value)) { for (var i = 0; i < value.length; i++) { if (parseData_(headers, data, path, state, value[i], query, options, includeFunc)) { dataInserted = true; if (data[state.rowIndex]) { state.rowIndex++; } } } } else if (isObject_(value)) { for (key in value) { if (parseData_(headers, data, path + "/" + key, state, value[key], query, options, includeFunc)) { dataInserted = true; } } } else if (!includeFunc || includeFunc(query, path, options)) { // Handle arrays containing only scalar values if (Array.isArray(value)) { value = value.join(); } if (!data[state.rowIndex]) { data[state.rowIndex] = new Array(); } if (!headers[path] && headers[path] != 0) { headers[path] = Object.keys(headers).length; } data[state.rowIndex][headers[path]] = value; dataInserted = true; } return dataInserted;} function parseHeaders_(headers, data) { data[0] = new Array(); for (key in headers) { data[0][headers[key]] = key; }} function transformData_(data, options, transformFunc) { for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[0].length; j++) { transformFunc(data, i, j, options); } }} function isObject_(test) { return Object.prototype.toString.call(test) === '[object Object]';} function isObjectArray_(test) { for (var i = 0; i < test.length; i++) { if (isObject_(test[i])) { return true; } } return false;} function includeXPath_(query, path, options) { if (!query) { return true; } else if (Array.isArray(query)) { for (var i = 0; i < query.length; i++) { if (applyXPathRule_(query[i], path, options)) { return true; } } } else { return applyXPathRule_(query, path, options); } return false; }; function applyXPathRule_(rule, path, options) { return path.indexOf(rule) == 0; } function defaultTransform_(data, row, column, options) { if (data[row][column] == null) { if (row < 2 || hasOption_(options, "noInherit")) { data[row][column] = ""; } else { data[row][column] = data[row-1][column]; } } if (!hasOption_(options, "rawHeaders") && row == 0) { if (column == 0 && data[row].length > 1) { removeCommonPrefixes_(data, row); } data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " ")); } if (!hasOption_(options, "noTruncate") && data[row][column]) { data[row][column] = data[row][column].toString().substr(0, 256); } if (hasOption_(options, "debugLocation")) { data[row][column] = "[" + row + "," + column + "]" + data[row][column]; }} function removeCommonPrefixes_(data, row) { var matchIndex = data[row][0].length; for (var i = 1; i < data[row].length; i++) { matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex); if (matchIndex == 0) { return; } } for (var i = 0; i < data[row].length; i++) { data[row][i] = data[row][i].substring(matchIndex, data[row][i].length); }} function findEqualityEndpoint_(string1, string2, stopAt) { if (!string1 || !string2) { return -1; } var maxEndpoint = Math.min(stopAt, string1.length, string2.length); for (var i = 0; i < maxEndpoint; i++) { if (string1.charAt(i) != string2.charAt(i)) { return i; } } return maxEndpoint;} function toTitleCase_(text) { if (text == null) { return null; } return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });} function hasOption_(options, option) { return options && options.indexOf(option) >= 0;} function parseToObject_(text) { var map = new Object(); var entries = (text != null && text.trim().length > 0) ? text.toString().split(",") : new Array(); for (var i = 0; i < entries.length; i++) { addToMap_(map, entries[i]); } return map;} function addToMap_(map, entry) { var equalsIndex = entry.indexOf("="); var key = (equalsIndex != -1) ? entry.substring(0, equalsIndex) : entry; var value = (key.length + 1 < entry.length) ? entry.substring(key.length + 1) : ""; map[key.trim()] = value;} function toBool_(value) { return value == null ? false : (value.toString().toLowerCase() == "true" ? true : false);} function convertToBool_(map, key) { if (map[key] != null) { map[key] = toBool_(map[key]); } } function getDataFromNamedSheet_(sheetName) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var source = ss.getSheetByName(sheetName); var jsonRange = source.getRange(1,1,source.getLastRow()); var jsonValues = jsonRange.getValues(); var jsonText = ""; for (var row in jsonValues) { for (var col in jsonValues[row]) { jsonText +=jsonValues[row][col]; } } Logger.log(jsonText); return JSON.parse(jsonText);}

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():

=ImportJSON(url, query, parseOptions)

• 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("http://api.exchangeratesapi.io/v1/latest?access_key={your-access-key}") 

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.

function ImportJSONAuth(url, api_token, query, parseOptions) { var header = {headers: { 'Authorization': api_token, 'Accept': 'application/json'}}; return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_);}

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 {your-API-token}

Check out how it works:

=ImportJSONAuth("https://api.typeform.com/forms", "Bearer 3K**********************Krbe")

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.

API to Google Sheets with or without coding?

We demonstrated that you can connect API to Google Sheets with just a few clicks and some research. Of course, it is not a walk in the park, but it’s not rocket science either. Coupler.io provides you with a JSON integration that pulls data from most JSON APIs without the need for coding. However, it’s not a ready-to-use solution like Xero to Google Sheets. If you invest the extra effort to master it, it will pay you back double. Good luck with your data!

Home