IMPORTRANGE as dependent function

Question

I am trying to write a script which sets the function IMPORTRANGE in a specific cell, using as KEY the content of another cell. I have the list of the KEYs in Sheet2 and here is what I have so far:

cellA2 = sheet2.getRange(row,1);
cellA2value = cellA2.getValue();
Logger.log(cellA2value)
cellA1.setFormula('=IMPORTRANGE(cellA2value, "Sheet1!b2:b20")');

Well, when looking into the log, I indeed can see the KEY of the Google Sheets I want to import the range from, but when I run the script the formula does not get the text as I want it, but leaves cellA2value as it is. I have tried with single and double quoting already.

How do I solve this?

Answer 1

In the expression

 '=IMPORTRANGE(cellA2value, "Sheet1!b2:b20")'

cellA2value is just a string, a bunch of characters with no particular meaning: letter c followed by letter e, and so on. What you wanted is

 '=IMPORTRANGE(' + cellA2value + ', "Sheet1!b2:b20")'

which is the concatenation of three strings, the second of them being cellA2value.

---

By the way, importrange has to be approved by the user the first time it imports a spreadsheet (via the dialog "connect these spreadsheets"). So you'll need to make sure that each of the possible sources is approved in this way.