Google Sheets: Use Built-in Functions to Fetch JSON API Data

This is one that had me scratching my head for a while - how can you fetch data from an API, in Google Sheets, without having to require additional auth scopes, and without having to set up update/refresh triggers in Apps Script?

The Built-in Functions

Well, Google Sheets does have three built-in functions that fetch data from external sources: IMPORTDATA, IMPORTFEED, and IMPORTXML.

The limitations with built-in functions

But these functions are limited in what they can do, and none of them support fetching JSON data.

• IMPORTDATA can fetch data from a URL, but it only supports fetching CSV or TSV data.
• IMPORTFEED can fetch data from an RSS or ATOM feed, but that’s not remotely close to JSON data.
• And finally, IMPORTXML can fetch data from an XML file, but again, that’s not JSON data.

So what can you do if you need your spreadsheet to fetch JSON data from an API, but the above built-in functions won’t do the job?

Using Apps Script to Create a Custom Function

Well, you could write your own custom function from scratch, or even simpler - you could use something like the IMPORTJSON custom function, which is part of the ImportJSON.gs library.

The limitations with custom functions

The problem with creating custom functions to use in your spreadsheet is a pretty major one: Google Sheets caches the results of custom functions based on the input parameters, and doesn’t periodically update/refresh the function automatically, unlike it’s own built-in functions.

The only way it will update the results of a custom function is if you do one of the following:

• supply dynamic inputs/parameters to the function (but note that you cannot use built-in time-based functions like TODAY() or NOW())
• manually refresh the function via a manual action
• reload the entire page
• manually set up a trigger to refresh the function on a set interval/mechanism

You also can’t import data from an external source in Apps Script in a simple trigger, like onEdit, because of the restrictions in place on simple triggers. And that would mean having to set up an installable trigger, which would require additional auth scopes and having the trigger tied to a user account.

To summarize:

• custom functions get cached, and don’t update automatically if the input to the function doesn’t change
• you can’t use built-in time-based functions (such as TODAY() or NOW()) as an input to a custom function
• you can’t use simple triggers to fetch data from an external source
• installable triggers require additional auth scopes, and are tied to user accounts

So while I could technically write the code 100% in Apps Script and then set up an installable trigger, that really wasn’t something I wanted as it felt like a bad compromise. So I had to find another solution.

Getting Creative with Built-in Functions and Apps Script

Ok, so the built-in functions can’t load or parse JSON data, and custom functions get cached, which is a huge problem, and you can’t use simple triggers to fetch data from an external source.

But what if we could use a Google Sheets built-in function to fetch the data, and then use Apps Script to massage the returned data into something we can use?

The Plan

Well, the only built-in function that comes close to doing what we need is the IMPORTDATA function.

It can fetch data from an external URL, but it only supports fetching CSV or TSV data. If you try to fetch JSON data with it, it will actually return the data, but because IMPORTDATA is expecting commas or tabs in the data, it breaks the returned JSON.

Fortunately, it appears to break the data in a somewhat predictable way, so we can actually use Apps Script to clean up the data, and convert it back to a valid JSON object. And because we’re using IMPORTDATA to fetch the data, the results will update automatically whenever the data in the sheet changes, or at whatever interval Google Sheets uses to refresh the data.

So the plan is this:

1. Use Apps Script to create a regular javascript function that will accept an array of data, clean up the data, convert it to a valid JSON object, and then return the newly fixed JSON object.
2. Use IMPORTDATA in the spreadsheet to fetch the JSON data from the API.
3. Wrap the IMPORTDATA function in a custom spreadsheet function which will call the regular function to fix the JSON data, take the returned fixed JSON and convert it to an array, and then return that array to the sheet.

This way, the custom spreadsheet function will be able to refresh periodically, because it’s being called with the IMPORTDATA function’s returned data.

Set up the Google Sheet

Ok, so first off we need a Google Sheet to work with!

So head on off to Google Sheets, and create a new spreadsheet.

Once your new sheet has loaded, click on the Extensions menu, and then click on Apps Script to open the Apps Script editor.

A default Code.gs file will be created for you, with a default blank function called myFunction. You can simply highlight and erase this function, as we won’t be using it.

The Test API

For this example, I’m going to use the JSONPlaceholder API, which is a free fake online REST API for testing and prototyping.

Specifically, we’ll use the https://jsonplaceholder.typicode.com/posts endpoint, which returns a list of fake blog posts / articles.

The API will return an array of objects, where each object represents a blog post, and has the following properties:

• userId: the ID of the user who created the post (a number)
• id: the ID of the post (a number)
• title: the title of the post (a string)
• body: the body of the post (a string)

The Function to Clean Up the Data

The first thing we’ll need to do is clean up the broken JSON data, which we can do with the following function (copy and paste this into the Apps Script editor):

function fixMalformedJSON(malformedJSONString) {
  // Clean any extraneous commas
  let cleanedJSONString = malformedJSONString
    .replace(/,,+/g, ',')  // Replace multiple commas with a single comma
    .replace(/^\[,*|,*\],*$/g, '');  // Remove leading/trailing commas and brackets
  
  // Quote any object keys that are missing quotes
  let fixedJSONString = cleanedJSONString.replace(/(\w+):(?=([^\"]*\"[^\"]*\")*[^\"]*$)/g, '"$1":');

  // Remove quotes from numerical values
  fixedJSONString = fixedJSONString.replace(/:"(\d+)"/g, ':$1');

  // Clean up extra commas within objects and arrays
  fixedJSONString = fixedJSONString
    .replace(/\{,\s*/g, '{')   // Remove commas after opening braces
    .replace(/,\s*\}/g, '}')   // Remove commas before closing braces
    .replace(/,\s*\]/g, ']')   // Remove commas before closing brackets
    .replace(/\[\s*,/g, '[');  // Remove commas after opening brackets

  // Reassemble the array
  fixedJSONString = `[${fixedJSONString}]`;

  // Remove any trailing commas inside the array
  fixedJSONString = fixedJSONString.replace(/,\s*\]/g, ']');

  // Parse the cleaned and fixed JSON string
  let fixedJSON = [];
  try {
    fixedJSON = JSON.parse(fixedJSONString);
  } catch (e) {
    console.error("Failed to parse JSON:", e);
  }

  return fixedJSON;
}

Now the above function will attempt to fix the JSON data, but we can’t really return JSON data directly to a cell in Google Sheets. So we’ll create another function that will act as the custom function wrapper for the IMPORTDATA function (copy and paste this into the Apps Script editor):

function JSONPOSTSTOARRAY(malformedJSON) {
  // Convert the array of data to a string, joined by comma
  const malformedJSONString = malformedJSON.join(",");

  // Call the function to fix the malformed JSON data
  const fixedJSON = fixMalformedJSON(malformedJSONString);

  // Create a posts array with the headers
  const posts = [
    ["userId", "id", "title", "body"]
  ];

  if (Object.keys(fixedJSON).length === 0) {
    return "Error parsing JSON";
  }

  // Loop through the fixed JSON object and push the data to the posts array
  for (let post of fixedJSON) {
    post.userId = post.userId.toString();
    post.id = post.id.toString();

    posts.push([post.userId, post.id, post.title, post.body]);
  }

  return posts;
}

Your Apps Script editor should now resemble this (note that I’ve cut off the bottom of the 2nd function in the image to keep the image from being too tall):

Once you’ve got those functions pasted in the editor, just make sure to save your work by clicking on the floppy disk icon in the toolbar, or by pressing Ctrl + S.

The Google Sheets Function

Now that we’ve got the Apps Script functions set up, we can go back to the Google Sheet, and start setting up the cell functions to import the data from the API, and display it columns and rows in the sheet.

In cell A1, enter the following formula:

=JSONPOSTSTOARRAY(IMPORTDATA("https://jsonplaceholder.typicode.com/posts"))

You’ll notice that the function is underlined in red, and that’s because Google Sheets doesn’t recognize the function as a built-in function. But that’s ok, because as long as we saved the code, and the function is defined in the Apps Script editor, it should work.

Once you’ve entered the formula, press Enter, and you should see the data from the API populate the columns and rows in the sheet, as such:

Note: The first time you enter the formula, you might see a warning message about receiving data from an external source. Just click on Allow access to proceed.

And that’s it! You’ve successfully fetched JSON data from an API, in Google Sheets, without requiring additional auth scopes, and without having to set up triggers in Apps Script!

W00t w00t! 🤪🎉

Caveats

Ok, so time for some real talk:

• The fixMalformedJSON function is pretty basic, and may not work for all types of broken JSON data.

  Some APIs supply the JSON data without extra spacing, others include tabs and newlines. So you may need to adjust the fixMalformedJSON function to account for these differences.

• The JSONPOSTSTOARRAY function is also pretty basic, and only works for the specific JSON data structure returned by the JSONPlaceholder API.

  If you’re working with a different API, you’ll need to adjust the function to match the structure of the JSON data returned by that API, so that the columns are adjusted accordingly.

• The IMPORTDATA function is limited to HTTP Get, and so it may not work well fetching data from an API that requires authentication.

  Note that I haven’t personally tested this, so I can’t say either way, although I suspect it should work fine if the API only requires an API key.

• Error handling is pretty basic, and the functions don’t return any error messages if the JSON data can’t be parsed, other than the console.log() message in the fixMalformedJSON function.

  You may want to add some additional error handling to the functions, to provide more meaningful error messages if something fails.

• There are probably other things this won’t support, so you’d need to try it out and see if it works for your specific use case.

Conclusion

So there you have it! A custom function for retrieving JSON API data in Google Sheets, that’s able to refresh automatically, without requiring additional auth scopes, and without having to set up triggers in Apps Script.

I think this is a pretty neat solution, and I’m pretty happy with how it turned out. It’s a bit of a hack, but it works, and it’s pretty simple to set up.

I hope you found it helpful, and that you’re able to integrate this method into your own Google Sheets projects.

Any suggestions or comments? Please let me know in the comments below!