Go to Qiita Advent Calendar 2024 Top
LoginSignup
FujeyG
@FujeyG

Are you sure you want to delete the question?

If your question is resolved, you may close it.

Leaving a resolved question undeleted may help others!

We hope you find it useful!

Apps scriptでスプレッドシートに関して

Q&A

spreadsheetappsscript

解決したいこと

Googleスプレッドシートで
ゲームの戦績をまとめてメモを残すシステムを作っています。
しかし、スクリプト初心者かつAIを用いながらの作成なので
現在起こっている問題がどの点で躓いているかわかりません。
ご教授願います。

発生している問題・エラースクリーンショット 2024-12-14 212431.png

スクリーンショット 2024-12-14 212437.png

作っているシートの説明からします。
BからH列に値を入れたらA列のIDを生成しIJK列が自動生成されます。
その後、IDを選択しながらスクリプトで作成したボタンを押下すると二枚目の画像に表示されているシートに移動し自動でMainIDが記載されます。
MemoTypeを選択しMemo Contentを入力すると自動でMemo IDが生成され
Main Sheetに移行しL列に記載されていきます。

問題点は
・メモを残す用のシートに残した最後のメモ（ここでいうtest T）がなぜかMemoに反映されない。
・Memo IDが1-K1から始まるようにしているつもりが何故か１から始まらない。
スクリーンショット 2024-12-14 213403.png
スクリーンショット 2024-12-14 213410.png
・上記のように新たなIDを生成しメモをするとMemo列の一番上に記載されてしまう。
以上になります。

該当するソースコード

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Actions')
    .addItem('Handle ID', 'handleID')
    .addItem('Delete Row', 'deleteRow')
    .addToUi();
}

function handleID() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selectedRange = sheet.getActiveRange();
  if (!selectedRange) {
    SpreadsheetApp.getUi().alert("Please select a valid ID cell in column A.");
    return;
  }

  var row = selectedRange.getRow();
  var col = selectedRange.getColumn();

  if (sheet.getName() === "Main Sheet" && col === 1 && row > 1) { // A列クリック時
    var selectedID = selectedRange.getValue();
    if (selectedID) {
      var ui = SpreadsheetApp.getUi();
      var response = ui.alert(
        "ID Action",
        "Would you like to write or view the memo?",
        ui.ButtonSet.YES_NO_CANCEL
      );

      if (response == ui.Button.YES) { // Write Memo
        var memoSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Memo Sheet");
        if (memoSheet) {
          memoSheet.activate();
          var lastRow = memoSheet.getLastRow() + 1;
          memoSheet.getRange(lastRow, 1).setValue(selectedID); // Set Main ID
        }
      } else if (response == ui.Button.NO) { // View Memo
        var memoSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Memo Sheet");
        if (memoSheet) {
          var memoData = memoSheet.getRange(2, 1, memoSheet.getLastRow() - 1, 1).getValues(); // Skip header row
          for (var i = 0; i < memoData.length; i++) {
            if (memoData[i][0] == selectedID) {
              memoSheet.activate();
              memoSheet.setActiveSelection(memoSheet.getRange(i + 2, 1)); // Highlight the corresponding row
              return;
            }
          }
          SpreadsheetApp.getUi().alert("No related memos found for this ID.");
        }
      }
    }
  } else {
    SpreadsheetApp.getUi().alert("Please select a valid ID cell in column A.");
  }
}

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var editedRange = e.range;
  var row = editedRange.getRow();
  var col = editedRange.getColumn();

  if (sheet.getName() === "Main Sheet" && row > 1) {
    var gamesWonCell = sheet.getRange(row, 7); // Games Won
    var gamesLostCell = sheet.getRange(row, 8); // Games Lost
    var winLoseCell = sheet.getRange(row, 9); // Win/Lose
    var dateCell = sheet.getRange(row, 10); // Date
    var timeCell = sheet.getRange(row, 11); // Time
    var idCell = sheet.getRange(row, 1); // ID列

    var gamesWon = parseInt(gamesWonCell.getValue(), 10);
    var gamesLost = parseInt(gamesLostCell.getValue(), 10);

    // ID自動生成
    if (idCell.getValue() === "" && gamesWonCell.getValue() !== "" && gamesLostCell.getValue() !== "") {
      var idRange = sheet.getRange(2, 1, sheet.getLastRow() - 1); // ID列全体
      var existingIDs = idRange.getValues().flat().filter(String).map(Number);
      var newID = 1;
      while (existingIDs.includes(newID)) {
        newID++;
      }
      idCell.setValue(newID);
    }

    // Win/Lose判定と日時記録
    if (!isNaN(gamesWon) && !isNaN(gamesLost)) {
      if (gamesWon > gamesLost) {
        winLoseCell.setValue("Win");
      } else if (gamesWon < gamesLost) {
        winLoseCell.setValue("Lose");
      } else {
        winLoseCell.setValue("Draw");
      }

      // 日時を設定
      if (dateCell.getValue() === "" && timeCell.getValue() === "") {
        var currentDate = new Date();
        dateCell.setValue(Utilities.formatDate(currentDate, Session.getScriptTimeZone(), "yyyy/MM/dd"));
        timeCell.setValue(Utilities.formatDate(currentDate, Session.getScriptTimeZone(), "HH:mm:ss"));
      }
    } else {
      winLoseCell.setValue(""); // リセット
      dateCell.setValue(""); // リセット
      timeCell.setValue(""); // リセット
    }
  }

  if (sheet.getName() === "Memo Sheet" && row > 1) {
    var mainIDCell = sheet.getRange(row, 1);
    var memoIDCell = sheet.getRange(row, 2);
    var memoTypeCell = sheet.getRange(row, 3);
    var memoContentCell = sheet.getRange(row, 4);

    if (col === 3 && memoTypeCell.getValue() !== "") { // Memo Type edited
      var memoType = memoTypeCell.getValue();

      // Ensure Main ID is filled
      if (mainIDCell.getValue() === "") {
        var lastMainID = getLastMainID(sheet);
        mainIDCell.setValue(lastMainID);
      }

      // Generate Memo ID
      var mainID = mainIDCell.getValue();
      var memoSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Memo Sheet");
      var memoRows = memoSheet.getDataRange().getValues();
      var typeCount = memoRows.filter(r => r[0] == mainID && r[2] == memoType).length;
      var memoID = mainID + "-" + memoType[0] + (typeCount + 1);
      memoIDCell.setValue(memoID); // Set Memo ID

      // Update Main Sheet Memo column
      var mainSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main Sheet");
      if (mainSheet) {
        var mainRowFinder = mainSheet.createTextFinder(mainID).findNext();
        if (mainRowFinder) {
          var mainRow = mainRowFinder.getRow();

          // Get all memos for this Main ID
          var allMemos = memoRows
            .filter(r => r[0] == mainID && r[1] && r[3]) // Main ID一致かつMemo ID, Contentが存在する
            .map(r => `${r[1]}: ${r[3]}`)
            .join("\n");

          mainSheet.getRange(mainRow, 12).setValue(allMemos); // Update Memo column
        }
      }
    }
  }
}

function deleteRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selectedRange = sheet.getActiveRange();
  if (!selectedRange) {
    SpreadsheetApp.getUi().alert("Please select a row to delete.");
    return;
  }

  var row = selectedRange.getRow();

  if (sheet.getName() === "Main Sheet" && row > 1) {
    var idToDelete = sheet.getRange(row, 1).getValue();
    if (idToDelete) {
      var ui = SpreadsheetApp.getUi();
      var response = ui.alert(
        "Confirm Deletion",
        "Are you sure you want to delete this row? Deleted data cannot be recovered.",
        ui.ButtonSet.YES_NO
      );
      if (response === ui.Button.YES) {
        // Delete related Memo Sheet data
        var memoSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Memo Sheet");
        if (memoSheet) {
          var memoData = memoSheet.getDataRange().getValues();
          for (var i = memoData.length - 1; i >= 1; i--) {
            if (memoData[i][0] == idToDelete) { // Main ID matches
              memoSheet.deleteRow(i + 1);
            }
          }
        }
        // Delete Main Sheet row
        sheet.deleteRow(row);
      }
    } else {
      SpreadsheetApp.getUi().alert("No ID found in the selected row.");
    }
  } else {
    SpreadsheetApp.getUi().alert("Please select a valid row to delete.");
  }
}

function getLastMainID(sheet) {
  var mainIDColumn = sheet.getRange(2, 1, sheet.getLastRow() - 1).getValues();
  var mainIDs = mainIDColumn.flat().filter(String);
  return mainIDs[mainIDs.length - 1] || "1"; // Default to "1" if no IDs exist
}


### 自分で試したこと
type countに問題があるのではと考えて数値などを変えてみましたが
前述のとおり全くの初心者なのでうまくできませんでした。
0

1Answer

ta_yano
@ta_yano

memoシートの行番号とmainシートの行番号が使い分けされていないからだと思います。
mainIDCellをmainIDを取得した後に
var mainIDCell = getMainIdCell(mainID);
みたいにして
getMainIdCellをこんな感じで作成すれば良いと思います。
エラートラップは実装していないのでそれは自分でお願いします。

function getMainIdCell(id) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A:A");
    
  var searchValue = String(id);
    
  var textFinder = range.createTextFinder(searchValue);
  return textFinder.findNext();
}
0Like

Your answer might help someone💌

LoginSign Up