GASでシート間の値をIDが一致したときにコピーしたい

Question

GASで以下のようなことを実現したいのですが、どのようなコードを書けばよいのでしょうか。

①顧客IDを検索し、シート名2：請求情報データに記載された『当月請求金額』をシート名１：請求管理表の請求月（請求情報データの作成年月を参照）に金額を書き込みたい。
②請求情報データにいない顧客IDの当月(作成日時)の請求金額は0」にする。

スプレッドシート名：請求管理表
シート名１：管理表

|請求管理表_2021年||||||
|:--|:--:|--:|
|顧客ID|氏名|2021年3月|2021年4月|2021年5月|2021年6月|・・・・|
|0001|浦島　太郎|\10000|\20000|\50000||
|0002|佐藤　一郎|\10000|\20000|\50000|
|0003|鈴木　次郎|\10000|\20000|\50000|
|0004|伊藤　花子|\10000|\20000|\50000|
|0005|山田　三郎|\10000|\20000|\50000|

シート名2：請求情報データ
|顧客ID|氏名|銀行名|支店名|手数料|当月請求金|作成日時
|:--|:--:|--:|
|0001|浦島　太郎|ＡＢＣ銀行|ＧＨＩ支店|\100|\10000|2021/6/20|
|0002|佐藤　一郎|ＤＥＦ銀行|ＬＭＮ支店|\50|\10000|2021/6/20|
|0005|山田　三郎|ＷＸＺ銀行|ＱＲＳ支店|\50|\10000|2021/6/20|

Comments

[退会済みユーザー]

編集前[2021/06/24 21:48]時点の投稿内容に対する質問：
・「請求管理表の請求月(3行6列目)に金額を書き込みたい。」とのことですが、ここでの"3行目”／”6列目”　というのは、何を拠り所にして導かれるものなのでしょうか？

画像から推測しうる限りでは、「シート名１：管理表」で「請求管理表_2021年」という値のあるセルを1行目/1列目と仮定した場合：
3行目は、「顧客ID 0001」の行であり　、
6列目は「2021年6月」のデータであり、
3行目6列目には、すでに「\10000」という値が入力されています。

"3行目”／”6列目”というセル位置は固定しつつ、なおかつ、この\10000という値を無条件に上書きしてよいということでしょうか？

②「請求情報データにいない顧客IDの当月の請求金額は0」にする。」とのことですが、ここでの「当月」とは、プログラム上、何を拠り所にして導かれるものなのでしょうか？
（仮定１）「当月」とは、「請求情報データ」の7列目にある「作成日時」の月のことである。
（仮定２）「当月」とは、スクリプトを実行する時点の月のことである。
（仮定３）「当月」は、質問文には記載されていない別のところで与えられる（または計算される）。

[退会済みユーザー]

編集後 [2021/06/24 22:23]時点の投稿内容に対する質問：
・請求情報データの
「作成日時」列の値は、顧客によってバラバラな可能性はあり得るのでしょうか？
例：0001　浦島さんの請求データの「作成日時」は2021/6/20であるが、
0002　佐藤さんの請求データの「作成日時」は2021/7/20である。
という可能性も考慮しなければならないのか？ということです。

[Astrogation_nm7]

作成日時の値は全て同じになります。

Answer 1

下記は、請求管理表シート：ヘッダを除く実データが3行目から、請求情報データ：ヘッダを除いて2行目から始まる前提です。

function getYearMonth(date) {
  // dateが日付型ならば「yyyy年M月」という文字列に変換。
  const typestr = Object.prototype.toString.call(date);
  if (typestr === '[object Date]') {
    return Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy年M月');
  }
  // dateが文字列型かつ「yyyy/M/d」という形式ならば、「yyyy年M月」という文字列に変換
  
  if (typestr === '[object String]') {
    const s = date.split('/');
    if (s.length > 1) return `${s[0]}年${Number(s[1])}月`;
  }
  // いずれにも該当しない場合はdateをそのまま返す。
  return date;
}

function searchColumn(header, value){
  // headerの中にvalueに該当する年月があればその列番号を返す。なければ-1を返す。
  for (let idx = 0; idx < header.length; idx++){
    if( getYearMonth(header[idx]) === getYearMonth(value)) return idx + 1;
  }
  return -1;
}

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shChargeData = ss.getSheetByName('請求情報データ');
  const shChargeTable = ss.getSheetByName('請求管理表');
  
  const dataRecords = shChargeData.getRange(2, 1, shChargeData.getLastRow()-1, 7).getValues();
  const tableRecords = shChargeTable.getRange(3, 1, shChargeTable.getLastRow()-2, 20).getValues();
  // 請求管理表のヘッダ(2行目)
  const tableHeader = shChargeTable.getRange(2, 1, 1, 20).getValues()[0];
  // 請求管理表の請求月の列番号（1 origin）
  let targetColumn = -1;
  
  for (let i=0; i<tableRecords.length; i++) {
    const tableRow = tableRecords[i]
    let found = false;
    for (const dataRow of dataRecords) {
      // 請求管理表のヘッダから、該当する月の列を探す。
      // 「作成日時の値は全て同じ」なので、一度取得すれば以後探す必要なし。
      if (targetColumn === -1) {
        targetColumn = searchColumn(tableHeader, dataRow[6]);
        // 該当する年月が見つからなかった場合はスキップ
        if (targetColumn === -1) {
          found = true;
          break;
        }
      }
      // 一致するIDが存在した場合は請求金額を転記
      if (dataRow[0] === tableRow[0]) {
        found = true;
        shChargeTable.getRange(i+3, targetColumn, 1, 1).setValue(dataRow[5]);
        break;
      }
    }
    // 請求管理表に一致するIDがない場合は「￥0」を転記
    if (!found) {
      shChargeTable.getRange(i+3, targetColumn, 1, 1).setValue('￥0');
    }
  }
}