Download CSV GoogleSheet with GoogleFinance connection

Question

We have a Google Sheet that downloads GoogleFinance data (raw, for every day of the rolling date frame).

=googleFinance("CURRENCY:USDNZD","price", B2,C14,"daily")

However, whenever we try to download the file to CSV, the values from the googleFinance formula disappear and we are left with a "#N/A" value.

**Google Sheet**
Google finance data (raw, for every day of the rolling date frame)  
Date    Close
10/1/2015 23:58:00  1.56304964
10/2/2015 23:58:00  1.55134983
10/3/2015 23:58:00  1.55134983
10/4/2015 23:58:00  1.55002736
10/5/2015 23:58:00  1.53985973
10/6/2015 23:58:00  1.52854965
10/7/2015 23:58:00  1.51434867

**CSV**
Google finance data (raw, for every day of the rolling date frame)
#N/A

I assume this is because the data does not actually exist within the Google Sheet, and is instead being dynamically loaded per interval.

Is there any way to download the values of the data when saving as a csv?

I found the same issue also with different formula from other add-on. Instad of #N/A, I have the #NAME? By refreshing many times, happen that values come out correctly... Didn't found solution yet? — pittuzzo

pittuzzo pittuzzo · Accepted Answer · 2021-06-20T15:25:30

I had the same issue and I solved by writing a script, which copy all the values (no formulas) in a new sheet and publishing only this last one.

So create in your Spreadsheet a new empty sheet, in my example the orginal sheet is Funds and new one is FundsValue. So publish only FundsValue.

Than create the following script (tools->script editor):

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(), 2);

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

}

In my example the rangeToCopy take only 2 columns, change the number if you need more.

So you can set the script to be executed automatically every day from the script editor, by setting a trigger.

Download CSV GoogleSheet with GoogleFinance connection

2 Answers