Writing a code to consume the REST API https://api.coindesk.com/v1/bpi/currentprice.json with Google Spreadsheets .
Google Sheets doesn’t have a built-in way to directly call external APIs. However, you can achieve this using Google Apps Script, a Javascript-like scripting language for extending Google Sheets functionality.
Here’s the code to consume the CoinDesk API and write the USD price to a specific cell in your Google Sheet:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
function getBitcoinPrice() { // Define the API URL var url = "https://api.coindesk.com/v1/bpi/currentprice.json"; // Fetch the data using UrlFetchApp var response = UrlFetchApp.fetch(url); // Check for successful response (200) if (response.getResponseCode() == 200) { // Parse the JSON data var data = JSON.parse(response.getContentText()); // Extract the USD price var usdPrice = data.bpi.USD.rate; // Specify the sheet and cell to write the data (replace with your values) var sheet = SpreadsheetApp.getActiveSheet(); var cell = sheet.getRange("A1"); // Change "A1" to your desired cell // Write the USD price to the cell cell.setValue(usdPrice); } else { // Handle error (optional) console.error("Error fetching data:", response.getResponseCode()); } } |
Instructions:
- Open your Google Sheet.
- Go to Tools > Script editor.
- Paste the code above into the script editor.
- Replace
"A1"with the actual cell address where you want to write the USD price. - Save the script (File > Save).
- Run the script by clicking Run > getBitcoinPrice.
This script fetches the Bitcoin price from the CoinDesk API and writes it to the specified cell.
Important Notes:
- This script retrieves data only when you run it manually. To automatically update the price at regular intervals, you’ll need to use a trigger (requires Google Apps Script knowledge).
- Consider adding error handling to gracefully handle potential issues like API failures or invalid responses.
This is a basic example. You can modify it to:
- Extract different data points from the JSON response.
- Write the data to different cells or format it as needed.
