How to import rows according to certain criteria with the addition of a trigger?

Question

Google Apps Script

Help, please, in writing a script for google sheets.

I had to replace importrange with a script due to the fact that import began to fall off often.

Conditionally there is a file "Spreadsheet_1" in which there is a sheet "imp_1"
from which I need to import columns 1,2,8,10 in the file "Spreadsheet_2" to the sheet "imp_2"

provided that I only need those rows from these columns, that match the following criteria:

* I only need those rows from these columns where the value in column 9 contains the word "Moscow"

and the second condition (simultaneously with the first):
* the rows of column 2 contain the same word as in the file "Spreadsheet_2 " on the "info" sheet in cell A3

And it would also be great to write a trigger so that the script is updated only after editing has occurred in the "Spreadsheet_1" file, on the "imp_1" sheet.

Answer 1

You can use this crutch:

=еслиошибка(
importrange("ссылка";прописн(диапазон))
;importrange("ссылка";строчн(диапазон))
)

Or this code:

/**
 * Возвращает массив данных импортированных с таблицы url, с листа sheetName со столбцов columsn
 *
 * @param {String} url Ссылка на таблицу
 * @param {String} sheetName Имя листа
 * @param {Array[Number]} columsn Массив номеров столбцов. A = 1
 * @return Возвращает массив импортированных данных
 */
function importColumns(url, sheetName, columsn) {
  columsn = [...columsn].flat(Infinity).map(column=>-1+column);
  let sourceSpreadsheet = SpreadsheetApp.openByUrl(url);
  let sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);

  let data = sourceSheet.getDataRange().getValues();
  outData = data.map(row=>{    
    return  columsn.map(column=>row[column]);
  });

  return outData;
}

Demo table