Home » Software Development » How to update Google Sheets with JSON API

About Max Katz

Max Katz

How to update Google Sheets with JSON API

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:

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:

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.

Published on Java Code Geeks with permission by Max Katz , partner at our JCG program. See the original article here: How to update Google Sheets with JSON API

Opinions expressed by Java Code Geeks contributors are their own.

(0 rating, 0 votes)
You need to be a registered member to rate this.
Start the discussion Views Tweet it!
Do you want to know how to develop your skillset to become a Java Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. JPA Mini Book
2. JVM Troubleshooting Guide
3. JUnit Tutorial for Unit Testing
4. Java Annotations Tutorial
5. Java Interview Questions
6. Spring Interview Questions
7. Android UI Design
and many more ....
I agree to the Terms and Privacy Policy
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments