importrange.gs

const PREFIX = 'prefix';
const EXTERNAL_ID = 'docId';
const TAB_NAME = 'tabName';
const LOCAL_NAME = 'localTabName';
const RANGE = false;

var documentProperties = PropertiesService.getDocumentProperties();

function importFromDoc(prefix = false, externalDocumentId = false, tabName = false, localTab = false, range = false) {
  
  if (!prefix) {
    prefix = setVariable("prefix");
    if (!prefix) {
      return false;
    }
  }

  if (!externalDocumentId) {
    externalDocumentId = setVariable("External Document ID");
    if (!externalDocumentId) {
      return false;
    }
  }

  if (!tabName) {
    tabName = setVariable("Tab to import data");
    if (!tabName) {
      return false;
    }
  }
  
  if (!localTab) {
    localTab = setVariable("Tab in this document", false);
  }

  if (!range) {
    range = setVariable("Range");
  }

  documentProperties.setProperty(prefix + EXTERNAL_ID, externalDocumentId);
  documentProperties.setProperty(prefix + TAB_NAME, tabName);
  documentProperties.setProperty(prefix + LOCAL_NAME, localTab);
  documentProperties.setProperty(prefix + RANGE, range);

  fetchExternal(externalDocumentId, tabName, range, localTab);
}

function setVariable(name, required = true)
{
  var value = false;
  var ui = SpreadsheetApp.getUi();

  var result = ui.prompt(
    `Set ${name}`,
    'value',
    ui.ButtonSet.OK
  );

  let close = false;

  var button = result.getSelectedButton();
  var text = result.getResponseText();

  if (button == ui.Button.OK) {
    value = text;
  } else if (button == ui.Button.CLOSE) {
    close = true;
  }

  if (!required) {
    close = true;
  }

  while (!close && !value) {
    value = setVariable(name);
  }

  return value;
}

function fetchExternal(externalDocumentId, tabName, range, localTab) {
  let spreadsheet = SpreadsheetApp.openById(externalDocumentId);
  let tab = spreadsheet.getSheetByName(tabName);

  let spreadsheet2 = SpreadsheetApp.getActiveSpreadsheet();

  let tab2 = false;
  if (localTab) {
     tab2 = spreadsheet2.getSheetByName(localTab);
  } else {
    tab2 = SpreadsheetApp.getActiveSheet();
  }

  let values = tab.getRange(range).getValues();
  
  let rows = values.length;
  let cells = values[0] ? values[0].length : 0;
  
  let localRange = tab2.getRange(1, 1, rows, cells);
  
  tab2.clear();
  
  localRange.setValues(values);
}

function opapa() {
   importFromDoc("test", "tableID", "blocked list", "test", "A1:B5");
}

function onOpen() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet();

  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Imports')
  .addItem('Import from document', 'importFromDoc')
  .addSeparator()
  .addItem('Test, predifined data', 'opapa')
  .addToUi();
}