How to ensure correct value from add-on formula on a GoogleSheet published as CSV

Question

By creating a GoogleSheet which use formula from an add-on (muFund), if I publish on the web the CSV and import it as a source on my power bi report, often (only few times it is working fine) the whole columns calculated by using muFund function shows the label #NAME? instead of correct value.

How can I ensure that the value calculated by an "add-on formula" will output a coherent CSV?

The google sheet is simply this one:

where as you can see the formula is =muFunds("nav";A2)

The published file (csv) is the following: https://docs.google.com/spreadsheets/d/e/2PACX-1vS4stYNCZIWp-ScKmCMlR1PHWsxRbsWjykKUhBBpeBcVz9tcDvFewCkgTvUh3QuRTJiRU_w17_H_cma/pub?gid=0&single=true&output=csv

Probably by downloading the file above, all will go smooth... but probably sometimes it will happen that the calculated values by muFunds will be #NAME?.... and this is strange. I found also this question not answered: Download CSV GoogleSheet with GoogleFinance connection

But by using this csv file with a PowerBI Report, the issue happen 9 times on 10 trials. So I wrote following query in Power Query for my report

let
    Source = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vS4stYNCZIWp-ScKmCMlR1PHWsxRbsWjykKUhBBpeBcVz9tcDvFewCkgTvUh3QuRTJiRU_w17_H_cma/pub?gid=0&single=true&output=csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"I" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"I"

And here we are with the problem:

Any suggestion?

Answer 1

The issue seems to be related to the google publish, which probabily is faster than the formula calculation and so the value is not calculated properly.

For this reason I made a small script in order to copy all the values (Funds) in another sheet (FundsValue) so they can be published in csv without any issue.

In the following the script made:

function daily_copy() {
  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Funds");
  var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FundsValue");

  var sheet = SpreadsheetApp.getActiveSheet();
  
  var rangeToCopy = source.getRange(1, 1, destination.getMaxRows(), 3);

//Paste to another sheet from first cell onwards
rangeToCopy.copyTo(destination.getRange(1, 1),{contentsOnly:true});

}

In this all values are handled properly so I also add a trigger which daily execute the script automatically