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()
orNOW()
) - 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()
orNOW()
) 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:
- 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.
- Use
IMPORTDATA
in the spreadsheet to fetch the JSON data from the API. - 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!
Until next time,
michael 😀
Share this post:
![Google Apps Script: 2 Caching Methods You Need to Use!](/_next/image?url=%2Fassets%2Fblog%2F2024%2Fgoogle-apps-script-how-to-cache-data%2Fcover-image.webp&w=3840&q=75)
Google Apps Script: 2 Caching Methods You Need to Use!
![Using LAMBDAs in Google Sheets to Create Filler Data](/_next/image?url=%2Fassets%2Fblog%2F2024%2Fgoogle-sheets-lambdas%2Fcover-image.webp&w=3840&q=75)
Using LAMBDAs in Google Sheets to Create Filler Data
![Google's Decision to Effectively Kill-off Small Sites](/_next/image?url=%2Fassets%2Fblog%2F2024%2Fgoogle-hcu-massacre%2Fcover-image.webp&w=3840&q=75)
Google's Decision to Effectively Kill-off Small Sites
![Odd Behaviour with Alternating Colors in Google Sheets](/_next/image?url=%2Fassets%2Fblog%2F2024%2Fgoogle-sheets-alternating-colors%2Fcover-image.webp&w=3840&q=75)
Odd Behaviour with Alternating Colors in Google Sheets
![How to Redirect URLs with Netlify](/_next/image?url=%2Fassets%2Fblog%2F2024%2Fnetlify-redirects%2Fcover-image.webp&w=3840&q=75)
How to Redirect URLs with Netlify
![Develop Google Apps Script Code in Your Local Code Editor](/_next/image?url=%2Fassets%2Fblog%2F2024%2Fapps-script-vs-code%2Fcover-image.webp&w=3840&q=75)
Develop Google Apps Script Code in Your Local Code Editor
![Create an Investment Gain Forecaster using Google Sheets](/_next/image?url=%2Fassets%2Fblog%2F2024%2Finterest-gain-forecaster%2Fcover-image.webp&w=3840&q=75)
Create an Investment Gain Forecaster using Google Sheets
![Limit the Number of Pre-rendered Pages in Next.js](/_next/image?url=%2Fassets%2Fblog%2F2024%2Flimit-number-of-pages-nextjs%2Fcover-image.webp&w=3840&q=75)
Limit the Number of Pre-rendered Pages in Next.js
![Build a Weather Widget Using Next.js](/_next/image?url=%2Fassets%2Fblog%2F2024%2Fnextjs-weather-widget%2Fcover-image.webp&w=3840&q=75)
Build a Weather Widget Using Next.js
![Filtering Data in Google Spreadsheets](/_next/image?url=%2Fassets%2Fblog%2F2023%2Ffiltering-google-spreadsheets%2Fcover-image.webp&w=3840&q=75)
Filtering Data in Google Spreadsheets
![5 Ways to Redirect URLs in Next.JS](/_next/image?url=%2Fassets%2Fblog%2F2023%2Fredirect-urls-in-next-js%2Fcover-image.webp&w=3840&q=75)
5 Ways to Redirect URLs in Next.JS
![Create a Budget Tool with Google Sheets](/_next/image?url=%2Fassets%2Fblog%2F2023%2Fgoogle-sheets-budget-template%2Fcover-image.webp&w=3840&q=75)
Create a Budget Tool with Google Sheets
![Fix Path Name Mismatches in Local & Remote Git Repos](/_next/image?url=%2Fassets%2Fblog%2F2023%2Fgit-file-folder-name-mismatches%2Fcover-image.webp&w=3840&q=75)
Fix Path Name Mismatches in Local & Remote Git Repos
![Fix "Cannot find module ..." TypeScript errors in VS Code](/_next/image?url=%2Fassets%2Fblog%2F2023%2Fcannot-find-module-errors%2Fcover-image.webp&w=3840&q=75)
Fix "Cannot find module ..." TypeScript errors in VS Code
![Fix "Unknown at rule @tailwind" errors in VS Code](/_next/image?url=%2Fassets%2Fblog%2F2023%2FVSCode-Unknown-Tailwind-rule%2Fcover-image.webp&w=3840&q=75)
Fix "Unknown at rule @tailwind" errors in VS Code
![Build a Simple Contact Form with Next.JS and Netlify](/_next/image?url=%2Fassets%2Fblog%2F2023%2FNextJS-Contact-Form%2Fcover-image.webp&w=3840&q=75)
Build a Simple Contact Form with Next.JS and Netlify
![Fix "Hydration failed ..." Errors in React](/_next/image?url=%2Fassets%2Fblog%2F2023%2FHow-to-Fix-Hydration-Errors-in-React-and-Next-JS%2Fcover-image.webp&w=3840&q=75)
Fix "Hydration failed ..." Errors in React
![Updating Turborepo to use Yarn Berry](/_next/image?url=%2Fassets%2Fblog%2F2023%2FUpdate-Turborepo-to-use-Yarn-Berry%2Fcover-image.webp&w=3840&q=75)
Updating Turborepo to use Yarn Berry
![Create an Expense-tracker with Google Sheets - Part 2](/_next/image?url=%2Fassets%2Fblog%2F2021%2FSimple-Expense-Tracker-Spreadsheet-Part-2%2Fcover-image.webp&w=3840&q=75)
Create an Expense-tracker with Google Sheets - Part 2
![Create an Expense tracker with Google Sheets - Part 1](/_next/image?url=%2Fassets%2Fblog%2F2021%2FSimple-Expense-Tracker-Spreadsheet-Part-1%2Fcover-image.webp&w=3840&q=75)
Create an Expense tracker with Google Sheets - Part 1
![Quick and Dirty Portfolio Tracker Part 2 - Crypto](/_next/image?url=%2Fassets%2Fblog%2F2020%2FQuick-and-Dirty-Portfolio-Tracker-Part-2-Crypto%2Fcover-image.webp&w=3840&q=75)
Quick and Dirty Portfolio Tracker Part 2 - Crypto
![Quick and Dirty Portfolio Tracker Part 1 - Stocks](/_next/image?url=%2Fassets%2Fblog%2F2020%2FQuick-and-Dirty-Portfolio-Tracker-Part-1-Stocks%2Fcover-image.webp&w=3840&q=75)
Quick and Dirty Portfolio Tracker Part 1 - Stocks
Comments