searchFilesAndAdd

/*
Наш канал про Таблицы и скрипты в Telegram: https://t.me/google_sheets
Оглавление канала (конечно, в Таблице): https://docs.google.com/spreadsheets/d/1u0aRlwb0rQHbjE9avbgCqIJSQ3EvPfTxT-h54jTQh8I/edit#gid=0&range=A1:B1
Чат: https://t.me/google_spreadsheets_chat
Донаты: https://t.me/google_spreadsheets_chat/160663
Написать: https://t.me/namokonov
*/

const shName = '⚡️ ПРО НАС';

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu("Скрипты ↓↓")
    .addItem("найти Таблицы", "searchFiles")
    .addItem('добавить лист в Таблицы', 'addShToFiles')
    .addToUi();
}

function searchFiles() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var array = []
  var t = 0
  var max = 5000
  var query = q();
  var files = DriveApp.searchFiles(q())

  while (files.hasNext() && t < max) {
    t++
    var file = files.next();
    try {
      array = array.concat([[file.getName(), file.getUrl(), file.getSharingAccess(), file.getDateCreated(), file.getOwner().getEmail()]]);
    } catch (err) { }
  }
  var r = ss.getRange(4, 1, array.length, array[0].length)
  r.clearContent()
  r.setValues(array)
}

function q() {
  var userEmail = Session.getActiveUser().getEmail()

  return `'${userEmail}' in owners and mimeType = 'application/vnd.google-apps.spreadsheet' and (visibility = 'anyoneWithLink' or visibility = 'anyoneCanFind')`;

}

function addShToFiles() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet()
  const template = ss.getSheetByName(shName);
  const range = sh.getRange("b1:b500")
  const colours = range.getBackgrounds();

  range.getValues()
    .forEach((url, i) => {
      url = url[0];
      if (url && colours[i][0] != 'red') {
        const shTo = SpreadsheetApp.openByUrl(url);
        const shToCheck = shTo.getSheetByName(shName);
        if (!shToCheck) {
          template.copyTo(shTo).setName(shName);
          Logger.log('OK ' + shTo.getName())
        }
        sh.getRange("i" + (i + 1)).setBackground('red');
        SpreadsheetApp.flush();
      }
    })
}