0

Been collecting historical bitcoin data from CoinGecko API using Google Sheets. Manual method where I have to open the sheet and copy and "Paste values only" to paste data in the history list. History on the left side and data collection on the right. I want to automate this process if possible. Current code pulls 10 rows of data that's not in the history yet. The history row ends in a "BOTTOM" field to indicate bottom of page. Script Editor already set to run at midnight for data collection. Here is my example: https://docs.google.com/spreadsheets/d/1kAcVtF2x9ox7gNCt5liQdhApQpGaBw1kl4I8PjKMfx8/edit?usp=sharing

Linda Paiste
  • 38,446
  • 6
  • 64
  • 102
Linus Visser
  • 3
  • 3
  • what's the API URL you are calling? if the API needs an access token don't post it here – nabais Sep 30 '20 at 09:34
  • Its =IF(F6<>"",importJSON("https://api.coingecko.com/api/v3/coins/"&$A$1&"/history?date="&G6&"&localization=false", "/market_data/current_price/usd,/market_data/market_cap/usd,/market_data/total_volume/usd", "noInherit,noHeaders"),"") , where A1 id "bitcoin" and G6 the date required. – Linus Visser Sep 30 '20 at 09:41
  • API do not need access token, ist a free API with a limited 100 calls per minute. – Linus Visser Sep 30 '20 at 09:46

2 Answers2

0

Answer

You have to make use of the Sheet and Range classes.

Code

In order to automate that process add the following code to your existing Apps script:

function Pull_History_bitcoin() {

  //PREVIOUS CODE

  var days = parseInt(targetSheet.getRange("J4").getValue().toString());

  if (days > 0) {
    var range = targetSheet.getRange("G6:J" + (5+days)).getValues();
    var lastRowHistory = targetSheet.getRange("G1").getValue();
    var nextLastRowHistory = parseInt(lastRowHistory[1]) + days;

    var bottomCell = targetSheet.getRange("A" + nextLastRowHistory);
    bottomCell.setValue("BOTTOM");

    var nextRange = targetSheet.getRange(`${lastRowHistory}:D` + (nextLastRowHistory - 1));
    nextRange.setValues(range);
  }
}

Where days define the number of entries after calling the external API.

Don't worry about the values rounded because they are just showing rounded, the current values are like the original ones.

Reference

Sheet class

Range Class

Jose Vasquez
  • 1,678
  • 1
  • 6
  • 14
  • Hi Jose, your answer did it with a minor change on my side. Thank you very much for your help. Following is my full script as reference to anyone wishing to acquire crypto historical information from the free CoinGecko API... – Linus Visser Sep 30 '20 at 17:54
0

Here below find my full google-apps-script as reference to anyone wishing to acquire crypto historical information from the free CoinGecko Api service.

function Pull_History_bitcoin() {
  var targetSS = SpreadsheetApp.getActive();
  var targetSheet = targetSS.getSheetByName("bitcoin");

  // To force an update
  targetSheet.insertRowBefore(3);
  targetSheet.deleteRow(3);

  // Copy newly acquired history into the history columns
  var days = parseInt(targetSheet.getRange("J4").getValue().toString());

  if (days > 0) {
    var range = targetSheet.getRange("G6:J" + (5+days)).getValues();
    var lastRowHistory = targetSheet.getRange("G1").getValue();
    //var nextLastRowHistory = parseInt(lastRowHistory[1],4) + days;
    var nextLastRowHistoryStr = lastRowHistory.slice(1);
    var nextLastRowHistory = Number(nextLastRowHistoryStr) + days;

    var nextRange = targetSheet.getRange(`${lastRowHistory}:D` + (nextLastRowHistory - 1));
    nextRange.setValues(range);

    var bottomCell = targetSheet.getRange("A" + nextLastRowHistory);
    bottomCell.setValue("BOTTOM");
  }
}
Linus Visser
  • 3
  • 3