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

Question

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?

You could try triggering the function with a clientside setInterval function. But you may exceed your quota rather quickly. Keep in mind your quota is calculated as a rate/day rather than a fixed number. — Cooper, Mar 13 '21 at 23:50

score 1 · Accepted Answer · answered Mar 14 '21 at 01:03

Modification points:

In order to refresh the formulas on Google Spreadsheet, it seems that it is required to replace others from the current formulas once. In your script, the same formulas are overwritten. I thought that this might be the reason of your issue.
In your situation, I thought that sourceFormulas might be able to be directly put to the sourceRange instead of targetRange using clearContent().

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

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

To:

sourceFormulas = sourceRange.getFormulas();
sourceRange.clearContent();
SpreadsheetApp.flush(); // This might not be required to be used.
sourceRange.setFormulas(sourceFormulas);

Note:

As other approach, when TextFinder is used for your situation, it becomes as follows.

  function sample() {
    var orgFormula = "=TRANSPOSE";
    var tempFormula = "=sample";
    const range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sources').getRange("G6:G14");
    range.createTextFinder(orgFormula).matchFormulaText(true).replaceAllWith(tempFormula);
    range.createTextFinder(tempFormula).matchFormulaText(true).replaceAllWith(orgFormula);
  }

References:

Thank you very much! The first option works as expected. The second one is totally new to me, so even more helpful! — Povi1as, Mar 14 '21 at 12:59
@Povi1as Thank you for replying and testing them. I'm glad your issue was resolved. Thank you, too. — Tanaike, Mar 15 '21 at 00:45

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

1 Answers1

Modification points:

Modified script:

Note:

References: