Google Apps Script フィルタ設定

function onEdit(e) {

  // セルの編集イベントで発火させる場合

  return;

  let r = e.range.getRow();
  let c = e.range.getColumn();

  if (r == 2 && (c == 2 || c == 3)) {
    setFilter();

    // toast表示する場合

    SpreadsheetApp.getActiveSpreadsheet().toast("フィルタ設定完了しました。","");
  }
}


function clearFilter() {

  let s = SpreadsheetApp.getActiveSheet();

  let f = s.getFilter();
  if (f) f.remove();
  
}

function setFilter() {

  let s = SpreadsheetApp.getActiveSheet();

  let f = s.getFilter();
  if (f) f.remove();

  let v1 = s.getRange("B2").getValue();
  let v2 = s.getRange("C2").getValue();

  if (v1 == "" && v2 == "") return;
  
  f = s.getRange("B5:H" + s.getLastRow()).createFilter();

  let lock = LockService.getDocumentLock();

  try {

    lock.waitLock(10000)

    if (v1 != "") f.setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenTextContains(v1).build());
    if (v2 != "") f.setColumnFilterCriteria(3, SpreadsheetApp.newFilterCriteria().whenTextContains(v2).build());

  } finally {

    lock.releaseLock();

  }
}

function onEdit(e) {

// セルの編集イベントで発火させる場合

return;

let r = e.range.getRow();

let c = e.range.getColumn();

if (r == 2 && (c == 2 || c == 3)) {

setFilter();

// toast表示する場合

SpreadsheetApp.getActiveSpreadsheet().toast("フィルタ設定完了しました。","");

}

function clearFilter() {

let s = SpreadsheetApp.getActiveSheet();

let f = s.getFilter();

if (f) f.remove();

}

function setFilter() {

let s = SpreadsheetApp.getActiveSheet();

let f = s.getFilter();

if (f) f.remove();

let v1 = s.getRange("B2").getValue();

let v2 = s.getRange("C2").getValue();

if (v1 == "" && v2 == "") return;

f = s.getRange("B5:H" + s.getLastRow()).createFilter();

let lock = LockService.getDocumentLock();

try {

lock.waitLock(10000)

if (v1 != "") f.setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenTextContains(v1).build());

if (v2 != "") f.setColumnFilterCriteria(3, SpreadsheetApp.newFilterCriteria().whenTextContains(v2).build());

} finally {

lock.releaseLock();

}