別ファイル（スプレッドシート）の重複チェック機能とデータベース化

Question

前提・実現したいこと

①2つのGoogleスプレッドシート間にて片方のシートに存在するデータを参照し、それらのデータ内に重複（完全一致）があれば、もう片方のシート入力された場合にセルの色が変わる設定。
②色が変わらなかった（重複しなかった）データのみ抽出され、片方のシートの最終行A列に追加

（例）運用イメージ
スプレッドシートα：マスターシート（A列に10万行のデータが記入）
スプレッドシートβ：個人シート（B列に毎日30〜100のデータが記入される）
個人シートは複数人に配布予定で、β、β'、β''、…と増えていくイメージです。

βのB列にデータを打ち込むとαのA列との比較で重複がチェック（β：B列に色付け）され、通過したもの（β：B列の色付けされなかったもの）がαのA列に追加されデータベース化される。

コードに疎く、実現性も把握していないのですが、もし可能であればご教示いただきたいです。

Comments

[k.a_teratail]

下記ですが、別々の実現方法でも問題ないでしょうか？
> βのB列にデータを打ち込むとαのA列との比較で重複がチェック（β：B列に色付け）
条件付き書式で マスターシートに値がある場合、色をつける

> （β：B列の色付けされなかったもの）がαのA列に追加されデータベース化される。
図形描画でボタンを作成して、GASで反映させる

[s-ueda]

k.a_teratail様

ご質問ありがとうございます！
別々の実現方法で問題ございません！

[k.a_teratail]

条件付き書式設定をβ側に仕込みましたが、未重複セルを反映しても背景色が変わらないので別の策を考える必要がありそうです。

とりあえず、β側に仕込んだ条件付き書式のカスタム数式
=IF(COUNTIF(IMPORTRANGE("マスター側のスプレッドシートのキー値", "シート1!A:A"),INDIRECT("B"&ROW()))=1, TRUE, FALSE)

Answer 1

①2つのGoogleスプレッドシート間にて片方のシートに存在するデータを参照し、それらのデータ内に重複（完全一致）があれば、もう片方のシート入力された場合にセルの色が変わる設定。

図形描画で実行するボタンを配置し、下記コードのメソッド名をスクリプト割り当てすることで可能になります。
コードは「スプレッドシートβ：個人シート」の方で記載する必要があります。

以下はサンプルコード

// 個別シート
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
// 個別シートのB列最終行
const bColLastNum = sheet.getRange("B1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow()
// 個別シートのB列の入力範囲のみを取得  
const sheetRange = sheet.getRange(`B1:B${bColLastNum}`)

function reflectionDataExtraction() {

  // マスターシートのデータ取得
  let masterSheet = SpreadsheetApp.openById('マスターのスプレッドシートID').getSheetByName('シート1')
  // A列の最終行
  let aColLastNum = masterSheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow()
  let masterValues = masterSheet.getRange(`A1:A${aColLastNum}`).getValues().flat()

  // 個別シートの入力値を取得
  let values = sheetRange.getValues().flat()

  values.forEach((v, index) => {
    if (masterValues.indexOf(v) != -1) { // マスターシートに存在する場合
      sheet.getRange(`B${index + 1}`).setBackground("#08fc08")
    } else { // マスターシートに存在しない場合
      sheet.getRange(`B${index + 1}`).setBackground("#ffffff")
    }
  })
}

実行前	実行後

---

②色が変わらなかった（重複しなかった）データのみ抽出され、片方のシートの最終行A列に追加

①と同じですが、下記コードのメソッド名をスクリプト割り当ててください。

こちらもサンプルコードです。

// 個別シート
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
// 個別シートのB列最終行
const bColLastNum = sheet.getRange("B1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow()
// 個別シートのB列の入力範囲のみを取得  
const sheetRange = sheet.getRange(`B1:B${bColLastNum}`)

function masterRefrection() {

  // 背景に何も色がついてない行数を抽出
  let refRows = sheetRange.getBackgrounds().flat().flatMap((_, index) => {
    if (_ === "#ffffff") return index+1
    else return []
  })

  // 背景に何も色がついてない値を取得
  let refValues = []
  refRows.forEach((v) => {
    refValues.push([sheet.getRange(`B${v}`).getValue()])
  })

  // 反映データ無しで終了
  if (refValues.length == 0) return

  // マスターシートへの反映
  let masterSheet = SpreadsheetApp.openById('マスターのスプレッドシートID').getSheetByName('シート1')
  
  // A列の最終行
  let aColLastNum = masterSheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow()
  // マスターシートへの追加
  masterSheet.getRange(aColLastNum + 1, 1, refValues.length, 1).setValues(refValues)
}

実行前	実行後	実行後追加値