Solved: Retrieve JSON file into Google Sheets for Looker S... - Page 2

miguel_krt · 05-09-2025 01:34 PM

Hello community!
@Tony_KRTand I are looking for a way to retrieve data from a JSON file accessed from an API with Basic HTTP Encryption.

This is the structure of the JSON file:

{
- "results": [
+ {...},
+ {...}....
],
- "next_page_url": [url for the next page]
}

The "next_page_url" contains an URL in which I can retrieve another JSON file with the same structure.

I'm able to retrieve the first set of "results" and then writing it into a Google Sheets; then I'm able to retrieve the "next_page_url" section but I'm having trouble appending the "next_page_url" data into the Google Sheets without headers.

Here is the code that I'm using in Google Apps Script:

function updateJsonData() {

var ss = SpreadsheetApp.getActive();

var rangeToUpdate = ss.getRange('Sheet1!A1');

const baseUrl = {baseurl}

var url = {url}

var username = {username};

var password = {password};

const auth = "Basic " + Utilities.base64Encode("username:password");

let options = {

method: "get",

headers: {

Authorization: auth

},

muteHttpExceptions: true

};

let response = UrlFetchApp.fetch(url, options);

let json = JSON.parse(response.getContentText());

let results = json["results"];

var ss = SpreadsheetApp.getActive();

var srcSheet = ss.getActiveSheet();

srcSheet.getRange("A:BP").clearContent();

while (json["next_page_url"]) {

let nextUrl = baseUrl + json["next_page_url"];

response = UrlFetchApp.fetch(nextUrl, options);

json = JSON.parse(response.getContentText());

Logger.log(json);

results = results.concat(json["results"])

}

rangeToUpdate.offset(0, 0, results.length, results[0].length).setValues(results);

}

Could anyone help me in telling me what I'm doing wrong?
Also, sorry in advance if I'm not following community rules, I'm new here.
Thanks in advance for the help provided!

a_aleinikov

Hi @miguel_krt ,

You’re close! The key issue is that results.concat(json["results"]) builds a combined array, but you never update the final sheet with all pages’ data — you only write once at the end and assume the array is shaped for setValues().

Here’s what to fix:
- Build a flat 2D array with only the data (no headers)
- Ensure you match the sheet’s expected format when writing

function updateJsonData() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
sheet.clearContents();

const baseUrl = '{baseurl}';
var url = '{url}';
var username = '{username}';
var password = '{password}';

const auth = "Basic " + Utilities.base64Encode(username + ":" + password);
let options = {
method: "get",
headers: { Authorization: auth },
muteHttpExceptions: true
};

let allResults = [];
let response = UrlFetchApp.fetch(url, options);
let json = JSON.parse(response.getContentText());
let results = json["results"];

allResults = allResults.concat(results);

while (json["next_page_url"]) {
let nextUrl = baseUrl + json["next_page_url"];
response = UrlFetchApp.fetch(nextUrl, options);
json = JSON.parse(response.getContentText());
allResults = allResults.concat(json["results"]);
}

// Convert objects to arrays (if needed)
let rows = allResults.map(obj => Object.values(obj));

// Write to sheet
if (rows.length > 0) {
sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}
}

View solution in original post

Retrieve JSON file into Google Sheets for Looker Studio to read