Google-sheets – How to use a function in a Google sheet parameter

google sheetsimportrange

I am using function IMPORTRANGE as follows:

=importRange("https://docs.google.com/spreadsheets/d/SPREADSHEETKEY/edit", "2017!D4")

which works great. Now I need to substitute the D4 in my parameter with a ROW function, so something like:

=importRange("https://docs.google.com/spreadsheets/d/SPREADSHEETKEY/edit", "2017! ROW()")

which does not work. The purpose is to import a cell from another spreadsheet which has the same position as the row being called from.

Best Answer

ROW() gives a number. The number of current row. So, If you are in the fourth row, you'll get a 4, whereas you need D4 not 4 i.e., A column reference and a row reference to point to a single cell. You could use something like

"2017!D"& row ()

However, This whole set up is high on the import range i.e., you need a import range call per row. This will be too much on Google servers and your own bandwidth and will surely slow your entire sheet, If you scale up. A better method would be to Import a entire range in one call and use query or filter on it.

Related Topic