How can I use IMPORTRANGE to populate my create calendar event sheet without affecting the script?

Question

This formula is in A2 and populates from data in my booking sheet

=QUERY(
   ImportRange("Sheet_Key","sheet1!A7:Z"),
   "Select Col7,Col2,Col8,Col9,Col12,Col19"
 )

The calendar event script https://stackoverflow.com/a/15790894/6400958 works perfectly and takes 5-10 seconds to run when data is entered directly but creates duplicates and exceeds run time when the import range formula is used with exactly the same amount of data.

Here is a Demo file. Note that there are only three rows of event data.

Below is the script:

 /**
  * Adds a custom menu to the active spreadsheet, containing a single menu item
  * for invoking the exportEvents() function.
  * The onOpen() function, when defined, is automatically invoked whenever the
  * spreadsheet is opened.
  * For more information on using the Spreadsheet API, see
  * https://developers.google.com/apps-script/service_spreadsheet
  */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "My_CALENDAR_ID";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i++) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = new Date(row[2]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[3]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[5];
    var id = row[6];              // Sixth column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
      row[6] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

Answer 1

It's very likely that the open ended reference ("sheet1!A7:Z") makes that the resulting range of var range = sheet.getDataRange(); includes a lot of blank rows. Add a where clause to filter out the blank rows, something like where Col2 <> ''. The final formula will look like the following

=QUERY(
  ImportRange("Sheet_Key","sheet1!A7:Z"),
  "Select Col7,Col2,Col8,Col9,Col12,Col19 where Col2 &lt> ''"
 )