シート2のD列に、シート2のA,B,C列と値が一致する時、一致する行の日付(〇月/〇日)をシート1のA列から取り出したい。

Question

前提・実現したいこと

シート2のD列に、シート2のA,B,C列と値が一致する時、一致する行の日付(〇月/〇日)をシート1のA列から取り出したい。

##スプレッドシートの概要

シート1　　　　　　　　　　　　　　　　　　　　　　
　　A列　　　　B列
2021/07/01	32.884
2021/07/02	32.530
2021/07/03	32.419
2021/07/04	31.549
2021/07/05	31.372
2021/07/06	32.178
2021/07/07	31.110
2021/07/08	31.214
2021/07/09	31.215
2021/07/10	32.167
2021/07/11	33.781
2021/07/12	30.996
2021/07/13	31.071
2021/07/14	34.012
2021/07/15	34.997
2021/07/16	34.847
2021/07/17	33.972
2021/07/18	30.536
2021/07/19	33.064
2021/07/20	34.600
2021/07/21	34.401
2021/07/22	31.434
2021/07/23	30.068
2021/07/24	33.504
2021/07/25	33.030
2021/07/26	32.517
2021/07/27	30.452
2021/07/28	32.282
2021/07/29	34.367
2021/07/30	33.253
2021/07/31	30.902
2021/08/01	32.226
2021/08/02	33.546
2021/08/03	32.180
2021/08/04	34.812
2021/08/05	31.137
2021/08/06	31.605
2021/08/07	33.410
2021/08/08	31.882
2021/08/09	30.000
2021/08/10	31.426
2021/08/11	31.788
2021/08/12	31.694
2021/08/13	31.725
2021/08/14	30.455
2021/08/15	31.551
2021/08/16	30.949
2021/08/17	30.648
2021/08/18	30.447
2021/08/19	31.984
2021/08/20	32.693
2021/08/21	32.719
2021/08/22	33.717
2021/08/23	33.663
2021/08/24	31.980
2021/08/25	31.480
2021/08/26	34.817
2021/08/27	30.382
2021/08/28	31.390
2021/08/29	34.001
2021/08/30	31.138
2021/08/31	34.734

シート2
A列   B列　　C列    　D列
2021  7月  30.179　　対応する値(日付を入れたい)
      8月  30.082　　対応する値(日付を入れたい)

ここにより詳細な情報を記載してください。

javascript初学者です。
現在、javascriptの学習をしており、javascriptを活用して
処理を行いたいと考えており今回の箇所の処理の仕方で半日ほど考えたのですが、
知識が少なく、前に進むことができません。
お時間がある方がいらっしゃいましたら、ご教示いただけますと幸いです。

Comments

[macaron_xxx]

一致するものが複数ある場合はどうなるのでしょうか？

[aaoe]

一旦、お考えしていただかない方向でお願い申し上げます。

Answer 1

こんな感じでできました

const DATA_SHEET = 'シート1'
const SEARCH_SHEET = 'シート2'

function isFormRange(e) {
  if (e.source.getSheetName() !== SEARCH_SHEET) return false
  if (e.range.getColumn() >= 4) return false
  return true
}

function getChangeFormValues(e) {
  const sheet = e.source.getActiveSheet()
  const ri = e.range.getRow()
  const [[y, m, v]] = sheet.getRange(ri, 1, ri, 3).getValues()
  return { y, m, v }
}

function getSearchData() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(DATA_SHEET)
  return sheet.getRange('A:B').getValues().filter(([a]) => !!a).map(([a, b]) => [new Date(a), b])
}

function search(y, m, sv) { // return day
  const data = getSearchData()
  const sm = Number(String(m).replace(/[^\d]/g, '')) // "n月" => n
  const hits = data.filter(([date, v]) => 
    v === sv && date.getFullYear() === y && date.getMonth() + 1 === sm
  )
  if (!hits[0]) return 'no hit'
  return hits[0][0].getDate()
}


function writeResult(e, res) {
  const ri = e.range.getRow()
  e.source.getActiveSheet().getRange(ri, 4).setValue(res)
}


function onEdit(e){
  if (!isFormRange(e)) return
  const { y, m, v } = getChangeFormValues(e)
  if (!y || !m || !v) return
  const res = search(y, m, v)
  writeResult(e, res)

}

// function debug() {
//   console.log(search(2021, 8, 31.694))
// }

Comments

[aaoe]

ご回答いただき、誠にありがとうございます。
上記のコードを一つ一つ読み解き、設計させていただきます！

[aaoe]

申し訳ないのですが、お時間がございましたら
各関数にどのようなどのような処理をしているのかコメントアウトを
して頂くことは可能でしょうか？

[anozon]

onEdit は API の通りどこかしらのセルに入力されたら発火します
isFormRangeは検索を実行する範囲への入力であるかチェックしてます
getChangeFormValues は入力された位置の左右3セルの情報を取り出して
search で検索します
search 内の getSearchData は検索対象のセルを全部とってきています
writeResult で結果を書き込んでいます

[aaoe]

ありがとうございます！

Answer 2

「検索用の列を追加する」「シート2では年列を全部埋める」という条件のやり方であればできました。

シート検索ID(追加別シート)
=TEXT('シート1'!A6, "YYYYMM")&"_"&'シート1'!B6

シート2のD列
=TEXT(INDEX('シート1'!A:A,MATCH(A1&TEXT(REGEXREPLACE(B1, "[^\d]",""),"00")&"_"&C1,'シート検索ID'!A:A, FALSE)),"DD")

Comments

[anozon]

今気づいたんですがGAS で実装する前提でしたらすみません????
GASからでしたら条件なしにできると思います

[aaoe]

ご回答のお時間頂き、誠にありがとうございます。
私の記載不足で申し訳ないのですが
スプレッドシート上ではなくjavascriptを活用して
処理がしたいと考えております。