I have the following output coming from an API. How can I parse through this using Google Apps Script and get that output as a Google Sheets table?
I tried the following, but only getting Nulls.
var response = UrlFetchApp.fetch(url, options);
var text = response.getContentText();
for (i in text) {
Logger.log(text[i]);
}
{
"status":1,
"complete":1,
"list":{
"3357271691":{
"item_id":"3357271691",
"resolved_id":"3357271691",
"given_url":"https:\/\/www.bloomberg.com\/news\/features\/2021-06-15\/airbnb-spends-millions-making-nightmares-at-live-anywhere-rentals-go-away",
"given_title":"",
"favorite":"0",
"status":"0",
"time_added":"1623783338",
"time_updated":"1623783338",
"time_read":"0",
"time_favorited":"0",
"sort_id":0,
"resolved_title":"Airbnb Is Spending Millions of Dollars to Make Nightmares Go Away",
"resolved_url":"https:\/\/www.bloomberg.com\/news\/features\/2021-06-15\/airbnb-spends-millions-making-nightmares-at-live-anywhere-rentals-go-away",
"excerpt":"When things go horribly wrong during a stay, the company\u2019s secretive safety team jumps in to soothe guests and hosts, help families\u2014and prevent PR disasters. Megaphone.fm: How Airbnb Makes Nightmares Disappear...",
"is_article":"1",
"is_index":"0",
"has_video":"0",
"has_image":"1",
"word_count":"155",
"lang":"en",
"top_image_url":"https:\/\/assets.bwbx.io\/images\/users\/iqjWHBFdfxIU\/iWjqS9z4c46I\/v0\/1200x630.jpg",
"domain_metadata":{
"name":"Bloomberg",
"logo":"https:\/\/logo.clearbit.com\/bloomberg.com?size=800",
"greyscale_logo":"https:\/\/logo.clearbit.com\/bloomberg.com?size=800&greyscale=true"
},
"listen_duration_estimate":60
}
}
}
I believe your goal is as follows.
text and put the data to Spreadsheet.In this case, how about the following sample script? In this sample script, the values are parsed and create a 2-dimensional array, and the array is put to the Spreadsheet.
function myFunction() {
var response = UrlFetchApp.fetch(url, options);
var text = response.getContentText();
// I added below script.
var header = ["item_id", "resolved_id", "given_url","given_title","favorite","status","time_added","time_updated","time_read","time_favorited","sort_id", "resolved_title","resolved_url","excerpt","is_article","is_index","has_video","has_image","word_count","lang","top_image_url", "domain_metadata.name","domain_metadata.logo","domain_metadata.greyscale_logo","listen_duration_estimate"];
var values = [header, ...Object.values(JSON.parse(text).list).map(o => header.map(k => {
var temp = k.split(".");
if (temp.length == 1) {
return o[temp[0]] || "";
}
return o[temp[0]] && o[temp[0]][temp[1]] ? o[temp[0]][temp[1]] : "";
}))];
console.log(values) // You can check the created array.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Articles"); // Please set the sheet name.
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
text value is the same as the value shown in your question. When the structure of the value is different, this script might not be able to be used. Please be careful about this.