GAS スプレッドシートでのシフト表自動作成

Question

実現したいこと

GASを利用してスプレッドシートでのシフト表作成を自動化しようと試みているのですが、行き詰まってしまったので質問させていただきました。具体的には出勤できない日のセルに色をつけてもらい、日付ごとに　色がついていないセルの中からランダムに３人選び'A'と記入するというものです。

発生している問題・分からないこと

色がついていないセルがちょうど３つだった場合の処理を追加しようと試行錯誤しているのですが、エラーの連続でどうにも上手くいきません。初歩的な内容かもしれませんが、答えてくださるとありがたいです。

該当のソースコード

function getBackground_color() {
  var value = [];

  // 指定のシートを取得
  const spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet1.getSheetByName('2024');

  // セル範囲を設定
  const range = sheet.getRange('E87:AI90');
  range.clearContent();

  // 背景色を取得
  const color = range.getBackgrounds();

  // 白いセルを取得
  for (let j = 0; j < range.getNumColumns(); j++) {
    var white = [];
    for (let i = 0; i < range.getNumRows(); i++) {
      if (color[i][j] == '#ffffff') {
        white.push([i,j]);
      } 

      // ランダムに三人選ぶ
      var select = [];
      if (white.length > 3) {
        while (select.length < 3) {
          const randomIndex = Math.floor(Math.random() * white.length);
          const selectIndex = white.splice(randomIndex, 1)[0];
          select.push(selectIndex);
        }} else {}
        value = value.concat(select);
    }}
  
  // 選択されたセルに'A'を書き込む
  for (let n = 0; n < value.length; n++) {
  sheet.getRange(value[n][0]+87, value[n][1]+5).setValue('A');
  }
}

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

分岐処理でいろいろ試したのですが、今の状態から少しでも変えると別の箇所に影響が出てエラーになってしまいます。

補足

エラーの一例を載せておきます。
TypeError: Cannot read properties of undefined (reading '0')

Comments

[pecmm]

提示されているソースコードですが、一部書式がおかしくなっているのでフォーマッタをかけた方がよいかと思います。
実行できないとか質問に支障があるとかではないですが、インデントがずれたままコーディングすると把握しづらい・ミスしやすいなど圧倒的にデメリットがでかいです。

apps script標準のエディタであれば、右クリックで「ドキュメントのフォーマット」ができるはずです。

[short3]

ご指摘ありがとうございます。
そんな機能があるんですね、恥ずかしながら知りませんでした。
これから活用させていただきます。

Answer 1

行方向のforループが必要ない範囲を含んでいたので
そこを修正してからwhite.lengthの条件を>から>=に変更しました。
あとは、単にわかりやすくするための整形です。

  // 白いセルを取得
  for (let j = 0; j < range.getNumColumns(); j++) {
    var white = [];
    for (let i = 0; i < range.getNumRows(); i++) {
      if (color[i][j] == '#ffffff') {
        white.push([i, j]);
      }
    } // ←★追加 '}' を追加

    // ランダムに三人選ぶ
    var select = [];
    if (white.length >= 3) { // ←★修正　'>' → '>='
      while (select.length < 3) {
        const randomIndex = Math.floor(Math.random() * white.length);
        const selectIndex = white.splice(randomIndex, 1)[0];
        select.push(selectIndex);
      } // ★修正 '}} else {}' → '}' と '}'の間に改行を挿入し、else{}を削除 
    } // ★上の修正の続き
    value = value.concat(select);
  } // ★修正 '}}' → '}'

Comments

[YellowGreen]

ちなみに、
冒頭で
const numOnShift = 3; // シフトを割り当てる人数
と定義しておいて、

if (white.length >= 3) {
while (select.length < 3) {
を
if (white.length >= numOnShift) {
while (select.length < numOnShift) {
に修正しておくと、
シフトに割り当てる人数を簡単に変更できるようになります。

[short3]

前回に続きありがとうございます。
forループにはよく注意しなければなりませんね。
これがうまくいったら徐々に拡張しようと思っていたので、追加情報もとても助かります。

Answer 2

現状は、4人(87～90行目の4行)のデータでのみ実行して動作確認されている状況でしょうか？

おそらくですが、現状「4人以上のデータで白が4セル以上ある日についてはうまく動いている」→「白が3セルの日でも動くようにしたい」という認識をされていると思うのですが
実際には「4人のデータの場合のみ、たまたま白が4セル以上ある日についてはうまく動いているように見える」ではないかと思います。
一度、データの対象範囲を5人,6人,7人... と増やしながら動作確認してみると、想定してないちょっと興味深い結果が出てくるはずです。
そちらの問題を先に解決してから、改めて空白=3の対応をされることをお勧めします。

---

動作確認する場合は、console.log で重要なデータ・怪しげなデータ、ループの出入り部分やループごとの値の変化などを出力しまくって、中身の変化やじっさいの実行順序を確認するとよいです。

やり方が分かるのであれば、ブレークポイントを設定してデバッグで1行ずつステップ実行していくのもよいでしょう。

Comments

[short3]

試作段階なので現状は４人を想定して行っています。
ご指摘の通り４人のデータの場合のみうまくいっており、データを変更すると不具合が起こる状態でした。
console.log は動作確認で使っていたのですが、ブレークポイントを設定してデバッグで1行ずつステップ実行するというのは知りませんでした。もしよければやり方を教えてくださるとありがたいです。