Роман

Проблема в том, что Query смотрит на данные, а при вызове ImportRange внутри Query, данных еще нет.
Попробуйте сделать промежуточную таблицу, куда вы сначала импортируете, а потом уже по выделенному диапазону сделаете то, что вам нужно

>когда удаляю из неё ненужные данные
У гугла простая логика — ищу последнюю пустую строку и вставляю туда значения.
Если у вас, например, диапазон A1:Z999 пустой, а в ячейке Z1000 есть что-то, то гугл вставит новые значения под последнюю строку.
Думаю, проблема была в том, что вы не все данные удаляли из строк и поэтому происходил пропуск. А когда удалили сами строки (то есть удалились и данные), проблема исчезла

function Flat_data() {

var a = ["Sheet #1", "Sheet #2", "Sheet #3"];
var total = [];
var index;
for (index = 0; index < a.length; ++index) {
   /// Logger.log(a[index]);

var SOURCESHEET = SpreadsheetApp.openById("ID таблицы с которой беруться данные").getSheetByName(a[index]); 


var source_last_row = SOURCESHEET.getLastRow();

var source_range1 = SOURCESHEET.getRange("G6:I"+(source_last_row)).getValues();
var source_range2 = SOURCESHEET.getRange("J6:L"+(source_last_row)).getValues();
var source_range3 = SOURCESHEET.getRange("N6:P"+(source_last_row)).getValues(); 
var source_range4 = SOURCESHEET.getRange("Q6:S"+(source_last_row)).getValues(); 

var targetvalues = [];
targetvalues = source_range1.concat(source_range2,source_range3,source_range4);
total.push(targetvalues);
}  
Logger.log(total);
}

Логика у вас нарушена в скрипте немного.
У вас нет хранилища, куда вы записываете без удаления.

Есть отличная формула unique. Но она не удаляет значения, а выводит только уникальные из диапазона.
Для удаления можете воспользоваться специальным аддоном
https://chrome.google.com/webstore/detail/remove-d...

Дайте пример таблицы.
Для подстановки значения поможет формула vlookup, например