【GAS】スプレッドシートで検索した値を別のシートへ転記

Question

前提・実現したいこと

スプレッドシートでデータベースを作成し、納品書発行を行っております。
発行漏れを抽出したいと思い、発行したデータをデータベースを同じ形で履歴として別シートに転記を考えています。
発行までの流れは下記のとおりです。

【データベース】全てのデータが記載されています。

【発行対象】納品書に記載するデータだけをqueryで抽出し、発行者と確認者の管理をしています。

【履歴】発行対象で発行者と確認者の両方の入力があるデータをデータベースを同じ形で履歴として残したいです。
★この部分で方法につまづいております。

発生している問題・エラーメッセージ

【履歴】の部分で記載した内容で何か方法があればご教授いただければ幸いです。
【発行対象】のデータをそのまま【履歴】に転記するコードは書けたのですが、
最終的に発行漏れを抽出するのに【データベース】と同じ型のほうが良いと思い方法を探していましたが力及ばずでした。

該当のソースコード

function myFunction() {
  
}
//元データ取得

   var SS_CopyFrom =SpreadsheetApp.openById("1nMt6X1sIVCNLnLrm06toxVX5vtsO3_lAHN8gu1J2GnY");
   var Sheet_CopyFrom = SS_CopyFrom.getSheetByName('発行対象'); 

//元データの最終行、最終列を取得 

   var LastRow = Sheet_CopyFrom.getLastRow();

 //最終行を取得 

   var LastColumn = Sheet_CopyFrom.getLastColumn(); 

//元データの取得した最終列、最終行までに入力された値を取得する。

   var CopyValue = Sheet_CopyFrom.getRange(2,1,LastRow,LastColumn).getValues();


//貼り付け先のスプレッドシートのIDを指定してシート名を指定する

   var SS_CopyTo = SpreadsheetApp.openById("1nMt6X1sIVCNLnLrm06toxVX5vtsO3_lAHN8gu1J2GnY");
   var Sheet_CopyTo = SS_CopyTo.getSheetByName('履歴');

 //コピーした値を貼り付ける。 

   Sheet_CopyTo.getRange(1,1,LastRow,LastColumn).setValues(CopyValue); 
 

試したこと

別のシートでvlookupで抽出して転記する方法も試しましたが、履歴を残したいので却下となりました。

補足情報（FW/ツールのバージョンなど）

半分丸投げな状態での質問で申し訳ございません。
自分でも引き続き方法を探しますが、何卒お力添えいただきたく存じます。
よろしくお願い申し上げます。

Comments

[退会済みユーザー]

素朴な疑問なのですが「発行対象シート」に転記する段階で、「データベース」シートと同じ形にしておくのではだめなのでしょうか。
（図でいえば、「コード１」列を、発行対象シートのＡ列とＢ列の間に入れる）

「発行対象」シートをもとに「履歴」シートを作るのですから、
最初から「発行対象」シートを「データベース」シートと同じ形にしておけば
「履歴」シートも「データベース」シートと同じ形にできると思います。

[qxg8725]

qnoir様
大変参考になるご提案、ありがとうございます。
実際は「データベースシート」は他から抽出したcsvを貼り付けており、列もcc列まであり、実際は必要ないものもあります。
上記を考慮して、下記の内容に変更しようと思いますので、ご意見頂ければと思います。
【csv】新たに追加
csvを貼り付けるだけのシート
【データベース】
csvシートからqueryで必要なデータを抽出し、データベースとする
【発行対象】
発行したいデータを抽出
※データの配列はデータベースと同じ
【履歴】
発行対象で発行者と確認者の入力があった行のみ転記

このようなシートの振り分けだと最終的に未発行の抽出などもし易くなるかと思います。
データベースについては早速変更を行います。
何かご意見ありましたら、ぜひお聞かせ頂きたいと思いますので、何卒よろしくお願い申し上げます。

[退会済みユーザー]

それでよいと思います。
履歴シートは、現状の(質問文記載のＩＤの）練習スプレッドシートの「郵送済」シートと同様に
query関数を使って発行対象シートから「済」と「OK」の行を抽出するという形でできますね、

[qxg8725]

ご回答ありがとうございます。
発行対象シートは発行したい日付を絞って表示させますので、履歴シートはデータベース型で残しておく必要があります。
説明が不足しており、申し訳ございません。
なので、現状と問題としては下記の通りになります。
【csv】
csvを貼り付けるだけのシート
【データベース】
csvシートからqueryで必要なデータを抽出し、データベースとする
【発行対象】
発行したいデータを抽出
データの配列はデータベースと同じ
★また、発行したい日付分のみ抽出
【履歴】
発行対象で発行者と確認者の入力があった行のみ転記
★発行分を最終行に追加していくイメージ
【発行漏れ】
データベースと履歴を照合し、発行漏れを抽出
※vlookupなどの関数で方法を探しております。

職場で相談できる方がいないので、qnoirに色々とお聞きできて、本当に助かっております。
自分でも引き続き方法を探しますが、アドバイス頂けると幸いです。
よろしくお願い申し上げます。

[退会済みユーザー]

すみません。私の理解が悪いせいか、現状では、質問者さんのやりたいこと／今問題となっていることが明確に分かりませんでした。

改めて確認させていただきたいのですが、以下のような理解でよろしいでしょうか。
間違っている点、不足な点があれば言ってください。

・【csv】シート、【データベース】シート、【発行対象】シート（の準備）までは問題なし。
・（納品書？を）発行するときに、【発行対象】シートに、発行者と確認者それぞれが内容をチェックして指定した列に「済」と「OK」を入力する。
・その「済」と「OK」が両方入力されたタイミングで、両方入力された行を【履歴】シートの最後にコピーして追記していきたい（しかし、そのやり方がわからないのでGASでどうやるか教えてほしい）
＋
・【発行漏れ】をチェックする方法をvlookup等で行うやり方があれば教えてほしい。
という理解でよろしいでしょうか？

[qxg8725]

ご返信ありがとうございます。
私の伝え方が悪いせいで、お手数をお掛けしてしまい、申し訳ございません。
知りたい部分ははまとめ頂いた内容で間違いありません。
お力をお貸し頂ければと思いますので、よろしくお願い申し上げます。

[退会済みユーザー]

回答欄に記載しました。
ただし、最後の「【発行漏れ】をチェックする」というのが、具体的にどのようなことを行うのか(何をチェックしたいのか）分かりませんでした。
具体的に説明お願いいたします。

Answer 1

とりあえず、
・【発行対象】シートに「済」と「OK」が両方入力されたタイミングで、両方入力された行を【履歴】シートの最後にコピーして追記していく方法
だけ記載します。（別の「履歴チェック方法」については不明な点があるためコメント欄で別途質問させていただきます）

※１．下記のコードはスプレッドシート紐付きのスクリプトとして作成しないと動作しません(外部スクリプトでは動作しません)。
必ずデータ入力用のスプレッドシートのメニューから「ツール」→「スクリプトエディタ」を選んででてきたエディタ画面にコードを入力してください。

function 確認入力チェック(e) {
  // 下の4つは、実態に合わせて修正して下さい（質問文のままであれば修正は不要です）
  const checkSheetName = "発行対象" // 発行確認用シートのシート名
  const rirekiSheetName = "履歴"; //履歴を記録するシートのシート名
  const check1person   = "発行者" 
  const check2person   = "確認者"

  const activeSheet  = SpreadsheetApp.getActiveSheet();
  const headers = activeSheet.getRange(1,1,1,100).getValues();
  // 「発行者」列および「確認者」列の列数を検索し格納する。
  const check1 = headers[0].indexOf(check1person)
  const check2 = headers[0].indexOf(check2person)
  if (check1 < 0) {
    Logger.log(`シート「${checkSheetName}」の1行目にセル「${check1person}」が見つかりませんでした。`);
    return
  }
  if (check2 < 0) {
    Logger.log(`シート「${checkSheetName}」の1行目にセル「${check2person}」が見つかりませんでした。`);
    return
  }
  
  const check1column = check1 + 1;
  const check2column = check2 + 1;

  // スプレッドシート上で編集（入力）された値を格納
  const val = e.range.getValue();
  // 「確認者」列に「OK」を入れていない場合は戻る。
  if (e.range.getColumn() != check2column || (val != 'OK' && val != 'ＯＫ')) return;
  const sheet = e.source.getSheetByName(checkSheetName);
  if (sheet == null){
    Logger.log(`シート「${checkSheetName}」が見つかりません。`);
    return
  }
  // 「発行者」列に「済」が入っていない場合は戻る。
  if (sheet.getRange(e.range.getRow(), check1column).getValue() != '済') return;

  // 履歴シートに、チェックが入った行を転記する。
  const values = sheet.getRange(e.range.getRow(),1,1,check2column).getValues();
  if (values == null || values[0] == null){
    Logger.log("引数valuesに渡された値が正しくありません。")
    return;
  }
  const ss = SpreadsheetApp.getActive().getSheetByName(rirekiSheetName);
  if (ss==null){
    Logger.log(`「${rirekiSheetName}」という名前のシートはアクティブなスプレッドシート上に存在しません。`)
    return;
  }
  const lastRow = ss.getLastRow();
  ss.getRange(lastRow+1, 1, 1, values[0].length).setValues(values);
}

　
※２．コードを入力・保存した後、トリガー設定画面から、「確認入力チェック」関数が編集時に実行されるように設定してください。（画面参照）

Comments

[qxg8725]

qnoir 様

お返事が遅くなり、大変申し訳ございません。
また、ご回答ありがとうございます！

ご提案頂いた内容で作成したところ、問題なく使用でき、感動しております！！
本当にありがとうございます！！

また質問をする際は課題点を明確にして、お伝えする様にする事も教えて頂き、ありがとうございます。

今後ともお力をお貸し頂けると幸いです。
本当にありがとうございました。