Ways to handle parsing of curly brackets JSON in Google Apps Script?

How can I use Google Apps Script to parse through the output of an API and convert it into a table in Google Sheets?

I attempted the following code, but it only returns Null values.

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
      }
   }
}
javascriptarraysjsongoogle-apps-scriptgoogle-sheets

Answer №1

I think your objective is to extract and parse the content of the retrieved text and then transfer that data to a Google Spreadsheet.

  • It seems like you are looking for a way to parse the values of text and input them into a spreadsheet.

For this purpose, you can consider using the sample script provided below. This script parses the values, creates a 2D array, and then inserts the array into a Google Spreadsheet.

Sample script:

function myFunction() {
  var response = UrlFetchApp.fetch(url, options);
  var text = response.getContentText();

  // Additional script included below.
  var header = [...];
  var values = [header, ...];
  console.log(values); // To check the created array.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Articles");
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
  • In case you wish to modify the header or any values, you can make changes in the script accordingly.

Note:

  • This script assumes that the format of your `text` value matches what was described in the initial question. If the structure of the value differs, this script may not be suitable in that scenario. Please exercise caution when implementing it.

References:

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Creating a dynamic nested list in HTML using JavaScript with data retrieved from a JSON source

I'm trying to generate a dynamic nested ul\li list from a JSON array. IMPORTANT! While I can use jQuery for this transformation, it's in a node.js environment where DOM access is restricted and I must work with a string. The depth of the a ...

javascripthtmlarraysjsonnode.js

Is it possible to utilize an npm package in TypeScript without a d.ts definition file?

Is it possible to use an npm package in TypeScript and Node.js without a .d.ts definition file? If so, how can I make it work? Currently, my code looks like this and I'm getting an error that says "cannot find module 'node-rest-client'" bec ...

javascriptnode.jstypescriptnpm

Nextjs Version 13: Implementing a Loading UI for Search Parameter Changes

I am working on a component that handles user input and updates search parameters accordingly. This results in a page refresh to display updated data on the UI. However, despite these actions, the loading.tsx file for this route is not being triggered. Af ...

javascriptnext.jsnavigationnext.js13urlsearchparams

When the webpage first loads, the CSS appears to be broken, but it is quickly fixed when

Whenever I build my site for the first time, the CSS breaks initially, but strangely fixes itself when I press command + s on the code. It seems like hot reloading does the trick here. During development, I can temporarily workaround this issue by making ...

javascriptreactjsnext.js

Creating a 2-dimensional array from a JSON using the MINIJson library

I'm new to JSON and also a bit inexperienced with c#. However, I have a task of loading JSON data and setting up a gamegrid in unity3d based on a 2d array from the JSON file. I have been using MINIJson from Darktable and I can fetch simple data succe ...

c#jsonunity-game-enginemultidimensional-array

ESLint flags a misuse of promises in my code that I believe is acceptable

This symbol table implementation includes a method that needs some adjustments: public getAllSymbols(type?: typeof Symbol, localOnly = false): Promise<Set<Symbol>> { const promise = super.getAllSymbols(type ?? Symbol, localOnly); ...

javascripteslint

Personalized design created using v-for

I have an array being passed through v-for and I want to use the values within the "style" attribute. Essentially, I need to append the value from v-for to style:"left"+EachValue+"px", but I'm having trouble with the syntax. I'm unsure if this ap ...

javascripthtmlcssvue.js

Exploring the functionality of the `super()` method in TypeScript

I'm trying to enhance the standard JavaScript Error class by adding another property called code, but for some reason, TypeScript is not allowing me to do so. Here is the code snippet: export class HttpError extends Error { public message: string ...

javascripttypescript

Access and process all JSON files within a folder that is constantly being updated

I have a situation where multiple JSON files are stored in a directory that will be constantly updated by users. Users have the ability to add categories, which results in new JSON files being created in the directory, and they can also remove categories, ...

node.jsjson

Are there any alternative methods for clearing form fields following a successful thunk dispatch in React?

When implementing a Post API call in Thunk, I dispatch a boolean success key for successful requests and an error message for errors. Now the goal is to clear form data upon success and display the error message upon an error. To achieve this, I utilize ...

javascriptreactjsreduxreact-reduxredux-thunk

Pass the ID of a dynamic textbox element to a child window upon clicking a link

One of the challenges I'm facing involves a textbox labeled "Branch Code" that links a branch to a corporate card transaction. At times, there is a requirement to split a transaction among multiple branches. To facilitate this process, I am constructi ...

javascriptc#jqueryasp.netasp.net-mvc-5

What is the best way to unpack a JSON string from a GET request in Django?

It seems like there's a glaringly obvious problem that I just can't seem to spot. When using ipython (Python 2.7), the following code works fine: In [1]: json.loads('[]') Out[1]: [] Now, I'm attempting to replicate this simple e ...

pythondjangojsondeserialization

Display array values in JavaScript from different arrays

I'm currently working on extracting data from my API to plot it in a graph. Specifically, I am interested in obtaining an array that contains only the 'date' values. Here's what I have managed to extract so far: https://i.sstatic.net/0 ...

javascriptjson

Is there a way to use AJAX for transferring a value?

I am looking to transmit a value to a php-script (servo.php) that will then write the received data in a file (/dev/servoblaster). The script section of my HTML file (index.html): <script> function tiltt() { var tilt = document.getElementById("tilt ...

javascriptphpjqueryhtmlajax

Looking to execute a PHP script upon form submission

I have a form that I need to submit in order for a PHP script to run using AJAX. <form method="post" action="index.php" id="entryform" name="entryform"> <input type="submit" name="submit" value="Submit" onclick="JavaScript:xmlhttpPost('/web/ ...

phpjavascripthtmlajaxforms

JavaScript causing values to disappear when the page refreshes

When a user hovers over ImageButtons, I use Javascript to change the ImageUrl. However, on submitting the form, the updated ImageUrl property is not reflected in the code behind. Similarly, I also dynamically update a span tag using Javascript, but its alt ...

javascriptjqueryhtmlasp.netvb.net

bespoke theme background hue

I currently have material-ui@next installed and I am attempting to customize the background color of the theme. Here is what I have tried: const customizedTheme = createMuiTheme({ palette: createPalette({ type: 'light', primary: purple ...

javascriptreactjsthemesbackground-colormaterial-ui

What could be the reason for the inability to generate the response using response.writeHead and response.write functions?

I recently started learning about node.js and I'm facing an issue with the code inside the if and else loop for the '/socket.html' case. Despite my efforts, I always get a 200 status code and a blank response when accessing the URL localhost ...

javascriptnode.js

Stop inserting repeatedly if there is no new data available

I'm looking for a simple way to implement an if-else statement in my AJAX code to display new data only once it's found, without repeating the same data. Also, I need to figure out how to store the last ID as a variable so that I can use it when ...

javascriptajax

Javascript object attributes

Could you retrieve the value of one object property based on the value of another property? For instance, in an SQL query, is it possible to fetch the id from the object where the object.name equals "somename"? I am trying to obtain the id of a particula ...

javascriptobject