Google finance as an argument for ArrayFormula

4
votes
=ARRAYFORMULA(IF(ROW(B:B)=1, "Share Price", IF(ISBLANK(B:B), "", googlefinance(B:B, "price"))))

I am trying to make row 1 (my header row) have a name, and then below that, if a valid ticker symbol is an input in column B, have google finance fetch the price of that stock. However, I am getting N/A in all of the cells underneath "share price" and can't figure out why.

Help please.

edit: Link to the sheet: https://docs.google.com/spreadsheets/d/1lyYYzLrFHfjojlL27bQ6ligNz2DBVpSM24jYfAVBVQs/edit?usp=sharing

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-finance
share a copy of your sheet - player0
@player0 I've edited the original post to include a link - Warstolrem

1 Answers

2
votes

GOOGLEFINANCE is kinda ArrayFormula already so this will not work as you would expect...

you will need to use script:

function onOpen(e){this.arrayThis("C1:C");}    //COLUMN WHERE YOU WANT TO HAVE THE RESULT
function arrayThis(range){
SpreadsheetApp.getActiveSpreadsheet().getRange(range).setValue(SpreadsheetApp.getActiveSpreadsheet().getRange(range).getCell(1,1).getFormula());
}

where C1 needs to be:

=IF(ROW(B1)=1, "Share Price", IF(B1="",,GOOGLEFINANCE(B1, "price")))

0