Harness JSON in Google Sheets

How to fetch JSON and use its data in Google Sheets

Add a Script to your Sheet:

Go to the Tools menu and click Script Editor. This should invoke an Apps Script project with a gs script and a function in it.

Make the API Request:

In the function, make your request and store the data in an object.

    var url = "https://yourweb.site/api/";
    var response = UrlFetchApp.fetch(url);
    var json = response.getContentText();
    var obj = JSON.parse(json);

Populate the Sheet with Data:

Depending on your case, you may need clear the sheet first. If you do this, make sure to append the headers. Then you can iterate through the JSON object and append row data to the sheet.

    for (var entry in obj.data)
    {
       sheet.appendRow([entry.name, entry.value]);
    }

Date: March 30, 2021

Tags: guides