Access a JSON value in the Google Sheets script editor and retrieve the data

Question

Access a JSON value in the Google Sheets script editor and retrieve the data

I'm trying to retrieve a value from a JSON object by making an API call in a Google Sheet.

Below is the script I am using:

function getBitcoinPrice()
{
    var url = "https://acx.io//api/v2/tickers/btcaud.json";
    var response = UrlFetchApp.fetch(url);
    var json = response.getContentText();
    var data = JSON.parse(json);

    var price = data.buy;
                                    
    return price;
}

However, when I try to run this function in my Google Sheet, it gives me an error message:

Error: Unknown range name 'getBitcoinPrice'.

javascript json google-apps-script google-sheets

Answer 1

Answer №1

Here is the JSON data provided for reference.

{
  "timestamp": ###,
  "stocks": {
    "purchase": ###,
    "sale": ###,
    "low_threshold": ###,
    "high_threshold": ###,
    "latest_price": ###,
    "volume": ###
  }
}

If you require the value associated with the key purchase, consider modifying the code snippet as shown below:

Original Code :

var price = data.purchase

Updated Code :

var price = data.stocks.purchase

Answer 2

Here is the JSON data provided for reference.

{
  "timestamp": ###,
  "stocks": {
    "purchase": ###,
    "sale": ###,
    "low_threshold": ###,
    "high_threshold": ###,
    "latest_price": ###,
    "volume": ###
  }
}

If you require the value associated with the key purchase, consider modifying the code snippet as shown below:

Original Code :

var price = data.purchase

Updated Code :

var price = data.stocks.purchase

Answer 3

Answer №2

Have you made sure to call it as a function?

=ccprice()

Answer 4

Have you made sure to call it as a function?

=ccprice()

Access a JSON value in the Google Sheets script editor and retrieve the data

Answer №1

Answer №2

Similar questions

Tips for creating incremental progress on a pop-up page?

Adding a blank data-columns attribute using jQuery:

Struggling with ensuring that Angular-JS pauses and waits for an asynchronous call to complete before moving on

Retrieving data from JSON column is taking an unusually long time

What is the best way to set the `value` attribute and `v-model` attribute for a custom Vue component?

How can JavaScript be used to automatically send a user to a designated page based on a selected option in

how to implement a delay in closing a window using JavaScript

Updating a table without the need for a button in PHP and AJAX with an Excel-inspired approach

Display JSON information in a grid using JQuery

When the `back` button is clicked in a browser from a different domain, does it trigger a page reload?

Customize the text that appears when there are no options available in an Autocomplete component using React

Despite a successful request, the React Redux action was not dispatched

Utilizing JSON for configuration settings in a C# program

Display toggle malfunctioning when switching tabs

Anticipating the execution of pool.query within a callback function in the Express framework

Determine the selected option in the dropdown menu upon loading the page and when clicked

Comparison between setTimeout for this.state and useState

Refresh a function following modifications to an array (such as exchanging values)

Struggling with integrating Meteor.wrapAsync with the mailchimp-api-v3

Get the response of a single variable from the data object through AJAX Success