Update IMPORTHTML result by copying formula from cell and back using GoogleScript

I want to make a portfolio tracker that takes the info from the web and updates every minute (time trigger) plus - by a button (this part is not very relevant I suppose).

Here is the example Sheet with some example data: https://docs.google.com/spreadsheets/d/1Ikqv-XtHkEl6VOdPKG9QotnG31o09sZMPdAozzAM4Qs/edit?usp=sharing

I have tried the script to copy from and back into the same cell in one move, but it does not trigger Sheets to refresh the data.

I guess, that if the range is taken from the existing position, moved to another, and (here is where it fails) move back - it must work. It does update when moved once.

I found the script, that works perfectly to one side. But I am not able to make it to the end.

What I've tried:

• making two functions work one after another with reversed ranges;
• making separate sourceRange, targetRange and source2Range, target2Range with the same logic.
• making similar one, but with flush in between them;
• making one function to do all the moves one after another;

None of the above worked pased the point where the range is moved one time. Here are the code options used:

function refreshPortfolioData() {    var activeSheet,numberOfSourceColumnsToGet,sourceColumnStart,sourceFormulas,sourceRange,        sourceRowStart,targetColumn,targetRange,targetRowStart;      //USER INPUT      sourceRowStart = 6; //Row to start getting formulas from    sourceColumnStart = 7; //Column to start getting formulas from    numberOfSourceColumnsToGet = 1; //Number of columns to get formulas from      targetRowStart = 6; //Row to start copying formulas to    targetColumn = 21; //Column to start copying formulas to      //END OF USER INPUT      activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sources');    sourceRange = activeSheet.getRange(sourceRowStart, sourceColumnStart, activeSheet.getLastRow(), numberOfSourceColumnsToGet);      sourceFormulas = sourceRange.getFormulas();//Get only formulas from the source range      targetRange = activeSheet.getRange(targetRowStart,targetColumn,sourceFormulas.length,sourceFormulas[0].length);      targetRange.setFormulas(sourceFormulas);//Copy the formulas to the target range        targetFormulas = targetRange.getFormulas();//Get only formulas from the source range        //SpreadsheetApp.flush()      sourceRange.setFormulas(targetFormulas);//Copy the formulas to the target range    }  

Example of extention to reverse the changes:

  <...>    SpreadsheetApp.flush()    var activeSheet,numberOfSourceColumnsToGet,sourceColumnStart,sourceFormulas,sourceRange,        sourceRowStart,targetColumn,targetRange,targetRowStart;      //USER INPUT      sourceRowStart = 6; //Row to start getting formulas from    sourceColumnStart = 21; //Column to start getting formulas from    numberOfSourceColumnsToGet = 1; //Number of columns to get formulas from      targetRowStart = 6; //Row to start copying formulas to    targetColumn = 7; //Column to start copying formulas to      //END OF USER INPUT      activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sources');    sourceRange = activeSheet.getRange(sourceRowStart, sourceColumnStart, activeSheet.getLastRow(), numberOfSourceColumnsToGet);      sourceFormulas = sourceRange.getFormulas();//Get only formulas from the source range      targetRange = activeSheet.getRange(targetRowStart,targetColumn,sourceFormulas.length,sourceFormulas[0].length);      targetRange.setFormulas(sourceFormulas);//Copy the formulas to the target range    }  

Anyone can help me with the options here?

https://stackoverflow.com/questions/66615983/update-importhtml-result-by-copying-formula-from-cell-and-back-using-googlescrip March 14, 2021 at 12:37AM