Use setValue with importRange to create a dynamically linked cell?

Question

I am running the following function after filling in a row of data on my master sheet (named 'eventlist'). I want the script to create a new sheet with cells that are linked to my master sheet, so that it pulls in data from the row of information I have just entered using ImportRange. But for the purposes of this example, I'm just pulling data from one cell until I get it working...

I am able to create the sheet and send data to a cell. I am also able to draw on my master sheet and have it update the other. But, I'm not able to bring the two together. The code below works up until the 'setValue', I'm ultimately trying to get the final cell to contain the formula '=importrange (relevant sheet and cell range information);' but I'm not sure how to send this formula text and include my row & col variables so that they work alongside it to create the final formula.

Thanks in advance for any pointers!

function addSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var col = sh.getActiveRange().getColumnIndex();
  var rData = sh.getRange(row, 1, 1, 3).getValues();

  if (row == 1) {
    ss.toast("These are column names. You can not send these.");    
    return
  }

  if(rData[0][0] != null || rData[0][1] != null || rData[0][2] != null) {
    try {      
      ss.insertSheet(rData[0][0]);
     // ss.setActiveSheet(ss.getSheets()[0]);
      ss.getRange("D"+(row)).setValue("=importrange("MYSHEETKEY", "eventlist! +col +row : +col +row" )");
    } catch(e) {
      throw 'Something has gone wrong...';      
    }
  }
}

Do you want it to pull the data on the same row and column as in master sheet? Also, do you want it to pull the whole active range or just one cell? — Akshin Jalilov
Thanks for the reply, for now I think I am sorted. But to clarify, this is part of a much larger project and so I'm taking baby steps with each bit of functionality... because I really don't know what I'm doing. Ultimately I will be taking an entire row of information from the master two or three times a day and displaying the info in a presentable way in a separate document - there are probably slicker ways of doing it, but for now I'm just looking at treating each piece of data one at a time as not to confuse myself. This is what this is! — James

Akshin Jalilov Akshin Jalilov · Accepted Answer · 2015-05-21T08:19:22

Not sure if this is exactly what you want, but this will import the data from the active range

function addSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var col = sh.getActiveRange().getColumnIndex();
  var activeRange = sh.getActiveRange().getA1Notation();
  var rData = sh.getRange(row, 1, 1, 3).getValues();

  if (row == 1) {
    ss.toast("These are column names. You can not send these.");    
    return
  }

  if(rData[0][0] != null || rData[0][1] != null || rData[0][2] != null) {
    try {      
      ss.insertSheet(rData[0][0]);
     // ss.setActiveSheet(ss.getSheets()[0]);
      ss.getRange("D"+(row)).setValue('=importrange("MYSHEETKEY", "eventlist!'+activeRange+'" )');
    } catch(e) {
      throw 'Something has gone wrong...';      
    }
  }
}

Use setValue with importRange to create a dynamically linked cell?

1 Answers