👋

3区の図書館を1時間で周回できるか？の検証処理の作成

2023/10/09に公開

GAS

tech

はじめに

以下の記事が詳しいが、東京は近隣在住区、勤務区の図書館も借りれることが多い。よく昼休みに2区にまたがり図書館を巡っているが、「3区の図書館を昼休みに巡ることは可能か？」を検証したい。

具体的には

3区の図書館の組み合わせリストを生成
それぞれの所要時間を算出
最短経路を確認

となる。

準備

図書館リスト

隣接区
https://uub.jp/cpf/rinsetsu.html#13

参考にしたソースコード

処理

phase1

組み合わせリストを生成する。とても巨大なリストになってしまった。

main.gs

function phase1() {
  const libraryList = getLibraryList();
  const wardList = getWardList();
  const tryangleList = libraryList.reduce((tryangleList, library1) => {
    libraryList.forEach(library2 => {
      libraryList.forEach(library3 => {
        
        const isTarget = _ => {
          const wardNameList = [library1, library2, library3].map(library => library.getWard());

          const isDuplicate = array => {
            const set = new Set(array);
            return set.size !== array.length;
          }

          if (isDuplicate(wardNameList)) return false;

          const ward = wardList.find(ward => ward.isSameName(wardNameList[0]));
          return ward.getAdjacentWard().includes(wardNameList[1])
            && ward.getAdjacentWard().includes(wardNameList[2]);
        };

        if(!isTarget()) return;

        const tryangle = new Tryangle();
        tryangle.setLibraryNameList([library1, library2, library3].map(library => library.getName()));
        tryangleList.push(tryangle);
      });
    });

    return tryangleList;
  }, []);


  BaseLibrary.refreshSheet(SHEET.tryangle.name, tryangleList.map(tryangle => tryangle.getLibraryNameList()));
}

sheet.gs

const SHEET = {
  library: {
    name: 'library',
    row: {
      data: 2,
    },
    column: {
      ward: 1,
      name: 2,
      id: 3,
      address: 4,
    },
  },
  ward: {
    name: 'ward',
    row: {
      data: 2,
    },
    column: {
      name: 2,
      adjacentWard: 5,
    },
  },
  tryangle: {
    name: 'tryangle',
    row: {
      data: 2,
    },
    column: {
      libraryNameList: [1, 2, 3],
      time: 4,
    },
  },
};


function getLibraryList(){
  return BaseLibrary.getSheetData(SHEET.library).map(row => new Library(row));
}

function getWardList(){
  return BaseLibrary.getSheetData(SHEET.ward).map(row => new Ward(row));
}

function getTryangleList(){
  return BaseLibrary.getSheetData(SHEET.tryangle).map((row, index) => {
    const tryangle = new Tryangle();
    tryangle.setDataFromSheet(row, index);
    return tryangle;
  });
}

class/Library.gs

class Library{
  constructor(row){
    this.ward = row[SHEET.library.column.ward - 1];
    this.name = row[SHEET.library.column.name - 1];
    this.address = row[SHEET.library.column.address - 1];
  }

  getName(){
    return `${this.ward}_${this.name}`;
  }

  getWard(){
    return this.ward;
  }

  getAddress(){
    return this.address;
  }

  isSameName(name){
    return this.getName() === name;
  }

  isSameWard(library){
    return this.ward !== library.getWard();
  }
}

class/Tryangle.gs

class Tryangle{
  constructor(){
    this.libraryNameList = [];
    this.rowIndex;
  }

  setDataFromSheet(row, index){
    this.libraryNameList = SHEET.tryangle.column.libraryNameList.map(columnIndex => row[columnIndex - 1]);
    this.time = row[SHEET.tryangle.column.time - 1];
    this.rowIndex = SHEET.tryangle.row.data + index;
  }

  isNoTime(){
    return !this.time;
  }

  getLibraryNameList(){
    return this.libraryNameList;
  }

  getLibraryText(){
    this.libraryNameList.sort();
    return this.libraryNameList.join('-');
  }

  setSheetTime(time){
    BaseLibrary.setText(SHEET.tryangle,this.rowIndex, SHEET.tryangle.column.time, time);
  }

  getTime(libraryList){

    const lList = this.libraryNameList.map(ln => libraryList.find(l => l.isSameName(ln)));

    const getPassTime = (place1, place2) => {
      const route = Maps.newDirectionFinder().setOrigin(place1).setDestination(place2).setLanguage("ja").setMode(Maps.DirectionFinder.Mode.BICYCLING).getDirections().routes[0];
      return route.legs[0].duration.value / 60;
    };

    const totalMinutes = getPassTime(lList[0].getAddress(), lList[1].getAddress())
      + getPassTime(lList[0].getAddress(), lList[2].getAddress())
      + getPassTime(lList[1].getAddress(), lList[2].getAddress());
    return totalMinutes;
  }
}

class/Ward.gs

class Ward{
  constructor(row){
    this.name = row[SHEET.ward.column.name - 1];
    this.adjacentWard = row[SHEET.ward.column.adjacentWard - 1];
  }

  isSameName(name){
    return this.name === name;
  }

  getAdjacentWard(){
    return this.adjacentWard;
  }
}

phase2

処理では考慮できていなかったが、a-b-c、b-a-c、c-a-bなどの「順番が違うだけで組み合わせは同じもの」があると思われる。それを確認するために

リストを昇順でソート（これですべてa-b-cの順番になる）
リストを-で接続して一つの文字にする
countifの数式を使い、重複を確認

main.gs

function phase2() {
  const tryangleList = getTryangleList();
  const outList = tryangleList.map(tryangle => [tryangle.getLibraryText()]);
  BaseLibrary.setList(SHEET.tryangle, SHEET.tryangle.row.data, SHEET.tryangle.column.libraryText, outList);
}

function phase2_1() {
  const outList = BaseLibrary.getSheetData(SHEET.tryangle).map(row => {
    return row[0].split('-');
  });
  BaseLibrary.refreshSheet(SHEET.tryangle.name, outList);
}

phase3

後はa-b、b-c、c-aの移動時間をそれぞれ取得して合計値を出す。

main.gs

function phase3() {
  const libraryList = getLibraryList();
  getTryangleList().filter(tryangle => tryangle.isNoTime()).slice(0, 500).forEach(tryangle => {
    tryangle.setSheetTime(tryangle.getTime(libraryList));
  });
  
}