0

Hi I am trying to get the data on my google sheet to be recalculate every minute

by using this script. 

    var sh = SpreadsheetApp.getActiveSheet();
    var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
    var r = "A1";
    var f = '=ImportHTML("https://coinmarketcap.com/all/views/all/","table")';
    sh.getRange(r).setFormula(f);
    Utilities.sleep(2000);
    sh.getRange(2,2,sh.getLastRow(),sh.getLastColumn()-1).setValues(sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()-1).getValues());
  }

but I got a a #REF error in the sheet

enter image description here

I just want the data been recalculated

any help ?

player0
  • 124,011
  • 12
  • 67
  • 124
Allex Ferreira
  • 3
  • 1
  • Two questions: 1) Have you got your function set up on a trigger? 2) Why do you move the data range with `sh.getRange(2,2,sh.getLastRow(),sh.getLastColumn()-1).setValues(sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()-1).getValues());`? The data *is* being recalculated on load of the function, but you're getting a `#REF` error because of moving the values and asking the formula to overwrite the newly placed data. – Rafa Guillermo Mar 23 '20 at 10:11
  • 1 - yes 2 I just copy and paste da script Now I am using this : function getData() { var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet7"); var queryString = Math.random(); var cellFunction = '=importjson("https://blockchain.info/ticker")'; sheetName.getRange('A1').setValue(cellFunction); } Can you help ? – Allex Ferreira Mar 24 '20 at 13:50
  • This new script works but I don't see the new data only old or some minutes before – Allex Ferreira Mar 24 '20 at 13:53

1 Answers1

0

Answer:

You can do this by clearing the Sheet and re-loading the formula, on a time-based Installable trigger.

Code:

function getData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var r = "A1";
  var f = '=ImportHTML("https://coinmarketcap.com/all/views/all/","table")';
  sheet.clear();
  sheet.getRange(r).setFormula(f);
}

function getData2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var r = "A1";
  var f = '=ImportHTML("https://bitinfocharts.com/","table")';
  sheet.clear();
  sheet.getRange(r).setFormula(f);
}

References:

Zoe
  • 27,060
  • 21
  • 118
  • 148
Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54
  • but.... ''unction getData2() { var sh = SpreadsheetApp.getActiveSheet(); var sheet = SpreadsheetApp.getActive().getSheetByName('sheet2'); var r = "A1"; var f = '=ImportHTML("https://bitinfocharts.com/","table")'; sh.clear(); sh.getRange(r).setFormula(f); }'' I can't add this script together with the other one on tab 2 or Sheet 2 – Allex Ferreira Mar 24 '20 at 18:29
  • `'Function getData2() { var sh = SpreadsheetApp.getActiveSheet(); var sheet = SpreadsheetApp.getActive().getSheetByName('sheet2'); var r = "A1"; var f = '=ImportHTML("https://bitinfocharts.com/","table")'; sh.clear(); sh.getRange(r).setFormula(f); } ` – Allex Ferreira Mar 24 '20 at 18:29
  • Thank you it did work would it work for API formulas as well ? – Allex Ferreira Mar 25 '20 at 04:01
  • What do you mean by `would it work for API formulas`? – Rafa Guillermo Mar 25 '20 at 08:09
  • I have try to find a way to get the data quick in the google sheets . lots of Bitcoin data sites the offer API , Json, etc... The easiest one is that one I posted – Allex Ferreira Mar 25 '20 at 09:23
  • but it does not seem that the data has been changing every min . I set the triggers for 1 minute , but the data does not change – Allex Ferreira Mar 25 '20 at 09:24
  • You might be calling the API at a shorter interval than it updates data. Each time the script runs it re-calls the API. – Rafa Guillermo Mar 25 '20 at 09:30