Automate historic data collection from CoinGecko with Google Sheets

Question

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

Answer 1

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

Answer 2

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");
  }
}