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();
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?


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.