I'm trying to save a single sheet in Google Sheets to a CSV. In my example it's historical stock quotes formatted for Quicken import. All of the "work" is done in other sheets - specifying date ranges, specifying securities, pulling data from google finance, etc.
The sheet I want to export has a lot of INDIRECT formulas looking at the other sheets and pulling the appropriate data to export. However it appears that the CSV Download makes it so the formulas cannot see data from other sheets. Is that expected behavior? I'd rather not do this with the extra step of copying and pasting as values (even in a script).
Formulas in columns A, B, & C: note Workdays is a named range for the # of NETWORKDAYS of the quote date range
A: =indirect("'Sheet2'!A" & int((row(A1)-1)/Workdays)+2)
B: =indirect("'Sheet1'!R" & mod(row(B1)-1,Workdays)+6 & "C" & int((row(B1)-1)/Workdays)*2+2,0)
C: =if(B1="","", text(indirect("'Sheet1'!R" & mod(row(B1)-1,Workdays)+6 & "C" & int((row(B1)-1)/Workdays)*2+1,0),"mm/dd/yyyy"))
Here's what it looks like online for the date range May 1-May 4:
Here's how it ends up in the CSV file in Notepad:
See this sheet: https://docs.google.com/spreadsheets/d/1Od05DAly6tm6lrIG4Gp7Rn8lDKCbW-1S5AV4OB-AhMo/edit?usp=sharing
