loading...

How to update Google Sheets with JSON API

maxkatz profile image Max Katz Originally published at maxkatz.org on ・3 min read

Google Sheets is a well-known service and online spreadsheet. Google Sheets can be more than just a spreadsheet, it can be used as a back-end or a database for applications. For example, Glide uses Google Sheets as a database for its mobile applications. Glide allows to build a mobile application without any code connected to Google Sheets. It’s incredible how fast you can build a real app, .

I was building an app in Glide that displays the latest news using News API. I connected the app to a Google Sheets spreadsheet where I entered a number of news stories manually. The app looks like this:

Glide app

This is how the Google Sheets spreadsheets looks:

Data for Glide app in Google Sheets

If I need to update any news I can manually edit the Google Sheets spreadsheets and the Glide app will be updated.

This manual update is not ideal of course.

I wanted to see if there is a way to consume an API form Google Sheets and update the spreadsheet. One way is to add a script to Google Sheets. I wanted to see if there is way to achieve the same result using no-code tools. I went back to Parabola and created a flow that calls a REST API and updates a Google Sheets document.

The flow looks like this:

Parabola flow to invoke an API and export the result to a Google Sheets spreadsheet

Let’s look at each step.

The API Import step invokes an external REST API. In this example I’m using News API service.

API Import step

The next step is JSON Flattener. This steps take the API response and puts it in columns/rows format. Below we are specifically flattening the articles column.

JSON Flattener step

The Column Filter step is optional. It allows to remove columns that we don’t need in Google Sheets spreadsheet. Instead of removing columns you can specify which columns to keep as shown below.

Column Filter step

The last step, Google Sheets Export exports the data to a spreadsheet.

Google Sheets Export step

When the flow is run it first gets the latest news from News API and then exports the result to Google Sheets. I like that this is a no-code approach. We are updating a Google Sheets spreadsheet with JSON from an external API. You can also look at as consuming an API from Google Sheets.

Posted on Apr 13 by:

Discussion

markdown guide