Remove Importxml formula immediately after scrape data in every active cell in Google Sheets

0
votes

I use this script to scrape data from any website in every 15 minute. I want to make this script auto remove Importxml formula and keep value only, but yet still can't achieve it.

function fetchData (){
  var wrkBk= SpreadsheetApp.getActiveSpreadsheet();
  var wrkSht= wrkBk.getSheetByName("Sheet1");
  var url= "https://coinmarketcap.com/currencies";
  for (var i= 2;i <=6;i++)
  {
  var coin= wrkSht.getRange('A' + i).getValue();
  var formula = "=IMPORTXML(" + String.fromCharCode(34) + url + "/" + coin + String.fromCharCode(34) + "," + String.fromCharCode(34)+"//span[@class='cmc-details-panel-price__price']"+ String.fromCharCode(34)+")";
  wrkSht.getRange('C' + i).activate();
    wrkSht.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  wrkSht.getRange('C'+i).setFormula(formula);
 
    
    Utilities.sleep(1000);
  
    
}}

And I try put this script before Utilites.sleep(1000); and yet still not success

First try

var range = wrkSht.getRange('C'+i);  
range.copyTo(range, {contentsOnly: true});

Second try

var range = wrkSht.getCurrentCell();
range.copyTo(range, {contentsOnly: true});

This is my Google Spreadsheet https://docs.google.com/spreadsheets/d/1vykBSNJQ9xO23jA1ZT8fQAjfmtUQOQTqzQXFfCqz8oQ/edit?usp=sharing

enter image description here

Hope someone can help me, Thanks you

1
javascriptgoogle-apps-scriptgoogle-sheets

1 Answers

2
votes

By default Google Apps Script applies the changes made by the code until the execution ends. Use SpreadsheetApp.flush() to force the changes be applied before doing the copy/paste as values only operation.

Instead of

var range = wrkSht.getCurrentCell();

Use

SpreadsheetApp.flush(); // This force to apply the previous changes (add the formula)
Utilities.sleep(30000); // This is required to wait for the spreadsheet to be recalculated (importxml import the data)
var range = wrkSht.getDataRange(); // This is in case that you want to paste the whole sheet as values

Instead of sleep you could use a loop to poll the spreadsheet until the spreadsheet is recalculated.

NOTE: Whenever it's possible we should avoid to use Google Apps Script classes and methods inside loops because they are (extremely?) slow and the execution time limit is small for free accounts (6 mins) and not so big for G Suite accounts (30 mins). The official docs explain this and we have several questions about this here.

Resources