Back to Blog

How to Import JSON to Google Sheets Without Coding or Using the Apps Script

There are two common ways to import JSON data from RESTful APIs into Google Sheets: code and no-code. The code way is suitable for tech-savvy users who are more or less proficient in Apps Script or Python. The no-code way involves the use of a GSheets add-on or software that will do the job. In this blog post, we’ll introduce both ways and explain how you can fetch your JSON data, place it in a spreadsheet, and even schedule recurrent imports. Interested? Then read on and explore more about how you can do this.

Import JSON to Google Sheets without coding

To parse JSON data to spreadsheets, you need to install a JSON importer tool. It’s a software that connects Google Sheets to a JSON API data source, fetches JSON data, and converts it to tabular format. Coupler.io provides such a tool.

JSON Client Importer to import JSON API to Google Sheets 

Coupler.io is a Google Sheets add-on that lets you integrate spreadsheets with different data sources, such as Airtable, Xero, Jira Cloud, and others. Each integration is implemented through an importer to be set up. To import JSON data from HTTP APIs, you’ll need to set up a JSON Client Importer. 

JSON Client Importer gives you power over data. It allows you to integrate Google Sheets with different platforms, such as Salesforce, Typeform, HelpScout, and more. The only prerequisite is having a RESTful API available. Check out our blog posts featuring the JSON Client importer to build integrations between Google Sheets and third-party apps:

You don’t have to be a programmer to configure and master the JSON Client Importer. 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, no worries. This article will explain the core points you need to take into account when working with the JSON Client importer.

How to import JSON data from an HTTP API into Google Sheets with Coupler.io

First, install Coupler.io from the Google Workspace Marketplace:

Open Coupler.io in the Add-ons tab of a spreadsheet, click on the +Add Importer button and choose JSON Client.

To set it up, you need to take the following steps. 

Title

Name your importer. 

Source

Insert the URL of the endpoint to import from into the “JSON URL” field. You can find the JSON URL in the RESTful API documentation of your data source. For example, the base URL for making HTTP requests to Slack is:

https://slack.com/api/...

Click Show advanced to set up optional parameters 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 for your request. For example, the Authorization header lets you specify credentials required to make an HTTP request.
  • URL query string – you can assign values to specified parameters.
  • Request body – if your request method is POST, PUT, PATCH or DELETE, you can add data to your request to be sent to 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.
  • Path – you can select nested objects from the JSON response.

Destination

Set up the Destination parameters:

  • Destination account – a Google account to connect to.
  • Sheet name – add the name of the sheet that will be receiving data.

Click Show Advanced to expand an optional parameter – Cell address. This lets you specify the first cell where the JSON data will be imported to.

Settings

In the Settings section, you can enable Automatic data refresh. Customize the schedule for automatic data exports from JSON API to Google Sheets. Check out more about Automatic data refresh.

For more about the optional parameters in the Settings section, refer to the Coupler.io knowledge base.

Once you’ve completed those steps, click Save to save the parameters or Save & Run to save the parameters and run the initial import right away.

That’s it for the theory. Now, let’s see how the JSON Client Importer works in practice. For this, we’ll try to parse JSON data from Typeform. 

Export JSON to Google Sheets from Typeform

You may need to import data from Typeform to calculate Net Promoter Score or other metrics. You should begin with Typeform’s API documentation. Here, you’ll learn the base URL:

https://api.typeform.com/

The endpoint (JSON URL) to request all forms responses of a type form is the following:

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

As you can see, there are no authentication strings attached to this URL. For Typeform’s HTTP request, you’ll have to use the Authorization HTTP header.  

Authorization: Bearer {your_access_token}

You can read about how and where to get your Typeform’s Personal access token here. 

Here is how the Source parameters of the JSON Client setup should look:

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

To import all responses of a particular form, use the following JSON URL:

https://api.typeform.com/forms/{form_id}/responses
  • {form_id} – you can find the ID of your form right in the form URL. For example, in
https://railsware.typeform.com/to/NfGhMX

the form ID is NfGhMX.

For more importing capabilities, read Typeform’s REST API documentation in detail. Check out also how we built a Post-COVID Destinations Dashboard using Typeform, Google Sheets and Data Studio. 

Import JSON into Google Sheets using code

Above, we exported JSON 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.

Import JSON file to Google Sheets using 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 to Google Sheets:

  • ImportJSON() – to import JSON from a URL.
  • ImportJSONFromSheet() – to import JSON from one of the Sheets.
  • ImportJSONViaPost() – to import JSON from a URL using POST parameters.
  • ImportJSONBasicAuth() – to import JSON from a 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 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("https://api.exchangeratesapi.io/latest") 

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")

Parse JSON file 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 JSON data into Google Sheets. We explained in detail how you can do this in the tutorial on How to Export Google Calendar to Google Sheets.

Bonus stage: Export Google Sheets to JSON

Would you mind if we explore how you can export your spreadsheet as JSON? Let’s say we have the following data range that need to be made accessible as a JSON endpoint:

Complete the following steps to convert it to JSON.

Publish data to the web

Go to File => Publish to the web.

Click Publish.

Confirm your intent to publish. Once you get a link to your document, close the window and move to the next step.

Note: There is no need to copy the link – we won’t use it.

Share with anyone with the link

Go to the Share settings of your spreadsheet and change its accessibility to anyone with the link. Click Done.

If you need a more detailed explanation of how to do this, read our blog post on how to share a Google Sheets doc.

Make a JSON URL

Use the following URL template to create a JSON endpoint:

https://spreadsheets.google.com/feeds/cells/{spreadsheet-id}/{sheet-number}/public/full?alt=json
  • {spreadsheet-id} – replace this with the ID of your Google Sheets document. You can find it in the address bar of your browser:
  • {sheet-number} – replace this with the number of the sheet in your Google Sheets doc. For example, for our case, we need sheet number 3.

So, your JSON URL should look like this:

https://spreadsheets.google.com/feeds/cells/1iLQYlI_FZddzRRMuDas2YwyfJe5w6ftlNvBhxvxR-mQ/3/public/full?alt=json

Try to open the JSON URL in your browser. Here is what we’ve got:

Retrieve JSON from Google Sheets

Let’s check out whether this spreadsheet endpoint works. Open Coupler.io and insert this URL into the JSON URL field. Path is another parameter you should apply. It lets you select nested objects from the JSON response. In our case, we’ll use feed.entry. Click Save & Run, and there we go!

Do I need the JSON Client Importer to import JSON files to Google Sheets?

Importing JSON to Google Sheets is not a walk in the park, but it’s not rocket science either. JSON Client Importer by Coupler.io gives you flexibility in configuring the way to fetch data from your data source. However, it’s not a ready-to-use solution like Xero Importer or Jira Importer. If you invest the extra effort to master it, it will pay you back double. Feel free to provide your feedback in the comments, and good luck!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free