パル配合表作成の技術的なお話

前書き

ちょっと前に書いた下の記事の技術的な話

本文

1ページ目：レシピ登録

A、B、Cにはすでに色が塗られており、空白なら白、重複なら赤く条件付き書式で編集されている。 またプルダウンはデータの入力規則から変える。

条件付き書式(空白)	条件付き書式(重複)	データの入力規則

なお重複判定のカスタム数式は以下である。

=SUMPRODUCT(($A$2:$A=$A2)*($B$2:$B=$B2) + ($A$2:$A=$B2)*($B$2:$B=$A2)) > 1

2ページ目：レシピ検索

ここではFILTER関数をつかう FLITER関数は以下に示すように1つ目の引数に抽出したい範囲、2つ目の引数に検索したい範囲とその条件を入れることで2つ目の引数の条件に合った行を1つ目の引数の範囲から表示してくれる。

=FILTER(抽出したい範囲, 条件)

なお今回は以下のようになっている。

(1列目:A3、2列目:D3)

=FILTER('レシピ登録'!A:B, 'レシピ登録'!C:C=B1)
=FILTER('レシピ登録'!A:C, ('レシピ登録'!A:A=E1)+('レシピ登録'!B:B=E1))

なおプルダウンについては1ページ目：レシピ登録同様

3ページ目：特性遺伝探査

ここが一番大変で難しい。 App Scriptに以下を張り付ければ動く。 拡張機能のところにあります。

function search_row(sheet,row){
  const last_row=sheet.getLastRow()
  const columnValues = sheet.getRange(1, row, last_row).getValues();
  for (let i = last_row - 1; i >= 0; i--) {
    if (columnValues[i][0] !== "" && columnValues[i][0] !== null) {
      return i + 1;  // 行番号（1始まり）を返す
    }
  }
}

function explore(cell1,cell2){
  const recipe_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("レシピ登録");
  
  const ans_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("特性遺伝探査");

  const last_Crow=search_row(recipe_sheet,3);
  const recipes = recipe_sheet.getRange("A1:C" + last_Crow).getValues();
  let queue=[[cell1,[]]];
  let visited = new Set();
  while(queue.length>0){
    let [now_p,now_road]=queue.shift();
    
    if(visited.has(now_p)) continue;
    visited.add(now_p);

    for(let recipe of recipes){
      if(recipe[0]==now_p||recipe[1]==now_p){
        const new_road = [...now_road, recipe];
        if(recipe[2]==cell2){
          ans_sheet.getRange(4, 1, new_road.length, 3).setValues(new_road);
          ans_sheet.getRange("A2").setFontSize(20);
          ans_sheet.getRange("A2").setFontColor("green");
          ans_sheet.getRange("A2").setValue("searching result");
          return;
        }
        queue.push([recipe[2], new_road]);
      }
    }
  }
  ans_sheet.getRange("A2").setFontSize(20);
  ans_sheet.getRange("A2").setFontColor("red");
  ans_sheet.getRange("A2").setValue("Not Found");
  return;
}

function isFilled(value) {
  return value !== null && value !== undefined && value.toString().trim() !== "";
}

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const sheetName = sheet.getName();
  // チェック対象の2セル
  const cell1 = sheet.getRange("B1").getValue();
  const cell2 = sheet.getRange("D1").getValue();
  if (sheetName === "特性遺伝探査") {
    sheet.getRange("A2").clearContent();
    const last_row=sheet.getLastRow()
    if(last_row>3){
      sheet.getRange(4, 1, last_row - 3, 3).clearContent();
    }
    sheet.getRange("A2").setFontSize(20);
    sheet.getRange("A2").setFontColor("green");
    sheet.getRange("A2").setValue("now searching");
    
    if(cell1 == cell2){
      sheet.getRange("A2").setFontSize(20);
      sheet.getRange("A2").setFontColor("red");
      sheet.getRange("A2").setValue("Same Error");
    }else if (isFilled(cell1) && isFilled(cell2)) {
    explore(cell1, cell2);
    }
  }
}

解説

onEdit(e)
これはセル等に変更があった時に常に呼ばれる関数である。このコードで一番最初に呼ばれる。
まず初めに特性遺伝探査のシートであるか確認を行い3行目以降のセルを空白にする。
その後A2セルを以下のように編集する。
初期状態を：now searching
もしB1セル(親)とD1セル(子)で同じなら：Same Error
そしてexplore関数を呼ぶ。

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const sheetName = sheet.getName();
  // チェック対象の2セル
  const cell1 = sheet.getRange("B1").getValue();
  const cell2 = sheet.getRange("D1").getValue();
  if (sheetName === "特性遺伝探査") {
    sheet.getRange("A2").clearContent();
    const last_row=sheet.getLastRow()
    if(last_row>3){
      sheet.getRange(4, 1, last_row - 3, 3).clearContent();
    }
    sheet.getRange("A2").setFontSize(20);
    sheet.getRange("A2").setFontColor("green");
    sheet.getRange("A2").setValue("now searching");
    
    if(cell1 == cell2){
      sheet.getRange("A2").setFontSize(20);
      sheet.getRange("A2").setFontColor("red");
      sheet.getRange("A2").setValue("Same Error");
    }else if (isFilled(cell1) && isFilled(cell2)) {
    explore(cell1, cell2);
    }
  }
}

explore(cell1, cell2)
ここで主な検索アルゴリズムが働く。手法は幅優先探査でqueue配列に今までの道のりを記録し、visited配列に検索済みの要素をいれ登録されたレシピと子が一致するかを常に行う。
結果によってA2を以下のように表示を変更
検索結果が出れば：searching result
経路がない場合は：Not Found

function explore(cell1,cell2){
  const recipe_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("レシピ登録");
  
  const ans_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("特性遺伝探査");

  const last_Crow=search_row(recipe_sheet,3);
  const recipes = recipe_sheet.getRange("A1:C" + last_Crow).getValues();
  let queue=[[cell1,[]]];
  let visited = new Set();
  while(queue.length>0){
    let [now_p,now_road]=queue.shift();
    
    if(visited.has(now_p)) continue;
    visited.add(now_p);

    for(let recipe of recipes){
      if(recipe[0]==now_p||recipe[1]==now_p){
        const new_road = [...now_road, recipe];
        if(recipe[2]==cell2){
          ans_sheet.getRange(4, 1, new_road.length, 3).setValues(new_road);
          ans_sheet.getRange("A2").setFontSize(20);
          ans_sheet.getRange("A2").setFontColor("green");
          ans_sheet.getRange("A2").setValue("searching result");
          return;
        }
        queue.push([recipe[2], new_road]);
      }
    }
  }
  ans_sheet.getRange("A2").setFontSize(20);
  ans_sheet.getRange("A2").setFontColor("red");
  ans_sheet.getRange("A2").setValue("Not Found");
  return;
}

あとがき

1年前で全然覚えてなかったのでいい復習になったかなと思う。
AI達に聞けば簡単に作ってくれる時代になったから必要ないかもだけど...