Google Sheets: Dynamic ImportRange Key

Question

I have one sheet with a lot of workbooks inside and every workbook include a lot of IMPORTRANGE functions. An example is like this:

=IMPORTRANGE("1i_lJGwT5345345gfdgdfgd37RCjTn0RlmzZr50C_Wg","Sheet - Name!K3:K999")

So this is monthly report and if I copy this sheet I must update all sheet keys in each function every month (this is really time consuming!)

Is there any way to make dynamic key with Script Editor? I try like this:

function dynamicKeys() {
  var thisSheetKey = '1543fgdfgdfg23463gdgi7EhmlPkn9j60';
  var dataSheetKey = '';
}

And inserting function in cell like this:

=IMPORTRANGE('+thisSheetKey+',"FB - Slovenia!K3:K999")

But sadly this doesn't work for me.

Thanks for any help.

Answer 1

You can reference the key of the sheet using getId() like this:

function dynamicKeys() {
  var thisSheetKey = SpreadsheetApp.getActiveSpreadsheet().getId();
  var dataSheetKey = '';
  ... 
  =IMPORTRANGE('+thisSheetKey+',"FB - Slovenia!K3:K999")
}