Google-sheets – IMPORTRANGE as dependent function

google sheetsgoogle-apps-scriptimportrange

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?

Best Answer

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.

Related Solutions

Google-sheets – Creating a function that runs when a Google spreadsheet is opened

This little script will:

create an extra menu item, thus making it accessible throughout the workbook. T
the menu item will set the active range to column E, last row + 1.

See the example I've prepared: Creating a function...Google spreadsheet is opened (edit rights)

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "get last row", functionName: "getLastRow"}];

  // add to menu
  ss.addMenu("Rows", menu);  

  // execute function
  getLastRow();  
}

function getLastRow() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActive();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get column F
  var indLastCol = sh.getLastColumn()-1;

  // get last row + 1
  var indRowWithout = sh.getLastRow()+1;

  // set new active range
  sh.setActiveCell('E'+indRowWithout);
}

Google-sheets – Pull data from one sheet (has a locked cell) to another sheet (has a locked cell) every day automatically

Instead of using an simple on edit trigger use an installable trigger as it runs with the trigger creator credentials.

Best Answer

Related Solutions

Google-sheets – Creating a function that runs when a Google spreadsheet is opened

Google-sheets – Pull data from one sheet (has a locked cell) to another sheet (has a locked cell) every day automatically

Related Topic