CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsrefresh
Povi1as
Povi1as

Reputation: 25

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:

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?

Upvotes: 1

Views: 42

Answers (1)

Tanaike
Tanaike

Reputation: 201358

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:

Upvotes: 1

Related Questions