特定の最終列を取得し、取得した内容を別のシートへ転記する

Question

実現したいこと

自動更新されるデータベースに記載される会員番号を取得し、繰り返し別のシートのセルに転記したい。

前提

自動で領収書を発行するシステムを作っています。

①Googleフォームで情報入力→②Googleスプレッドシートで集計→③PDF化＋メール送信（GASにて）

上記の流れの仕組みで、②の部分で苦戦しております。
スプレッドシートには、発行元データ・発行PDF・会員リストがあります。Googleフォームの集計から発行元データにフィルター関数を使って一旦、情報整理しています。（ここまではスプレッドシート内に関数入れています）

この発行元データに新たに情報が記載されると、GASで会員番号を基に自動で名前・日付・金額などを別のシートに転記するように作成しております。

ただ、最後のところ、新たに発行元データに記載があったらその会員番号をGASで取得し発行PDFシートへ転記させたいのですが、ここがうまくいきません。

値の取得はできているのですが、転記が上手くいっていません。ここまで作ったので何とか最後まで完成させて実用化させたいのです。

現在は、発行PDFシート内に会員番号を手打ちすれば、日付や但書、金額の情報がGASで反映・PDF化してくれる状態です。会員情報がキーになるので、これが先に発行PDFシートへ転記されないと日付等の情報が反映されないのです。

この点、ご教示いただけると幸いです。

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

エラーメッセージ
エラーメッセはありません。ただ反映されません。

### 該当のソースコード

function myFunction(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('＃発行元データ');
  var sh2 = ss.getSheetByName('#発行PDF');
  //var sh3 = ss.getSheetByName('会員リスト');

  
  var targetuserId = sh2.getRange('D2').getValue();
  var values = getmyFunction(targetuserId);
  //console.log(targetuserId);//D2

  if(values.length > 0){
    sh2.getRange('A17:Z17').clearContent();//#発行PDFのA17～Z17の内容を削除
    sh2.getRange(17,1,values.length,values[0].length).setValues(values);
    //console.log(sh2.getRange('A16:Z16'));
 }
}

function getmyFunction(targetuserId){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('＃発行元データ');
  var lastRow = sh1.getRange(sh1.getMaxRows(),6).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  var values = sh1.getRange(3,1,lastRow-2,10).getValues();
  console.log(lastRow);
  console.log(values);


  values = values.filter(function(value){
    return value[6] == targetuserId;
  });

  var records = [];
   for(var values of values){
    var code = values[8];//会員番号
    var name = values[9];
    var data = values[5];
    var text = values[4];
    var price = values[3];
    var record = [data,,,,code,,,,text,,,,,,,,,,,price,,,,,,,];
    //var record = [data,,,,text,,,,,,,,,,,,,,,price,,,,,,,];//動かなくなったらコレコピペ
    records.push(record);
  
    
   }

  return records;
  

}

Comments

[YAmaGNZ]

if文の中が実行されているのかなど確認されたのでしょうか？

[10-mo]

コメントいただきありがとうございます。

if文の中は動いていますが、先にコードを入力しないと動いてくれません。

なので、コードを自動で取得したいのです。

[YAmaGNZ]

「コード」と言われてもソースコード上に「コード」と判別できるようなコメントもありませんし、
「先にコードを入力しないと動いてくれません」と言われても「先に入力」とはなんなのか
「コードを自動で取得」とはどのようなことを言っているのか他人にはわかりません。

コメントまで含めると「先にコードを入力」すれば正常動作するというふうにも読めます。
もう少し情報を整理して他人が読んでも分かるように質問されたほうがよろしいかと思います。

[10-mo]

ご指摘いただきありがとうございます！

質問内容を修正しました。
混乱し過ぎていて、まだ不明瞭な点がありましたら申し訳ありません。、

[YAmaGNZ]

＞値の取得はできている
コードで言うところの
var values = getmyFunction(targetuserId);
のvaluesは正常に取得できているということですか？
そして、values.lengthが1（あくまで例です）なのにif文の中が実行されないということですか？
それともif文の中は実行されているのに
sh2.getRange(17,1,values.length,values[0].length).setValues(values);
でシートへの反映がされていないということですか？

[YAmaGNZ]

getmyFunctionという関数がそもそも会員番号を引数にしてその番号にマッチする情報を取得するような作りになっていると思いますが実際は会員番号とは関係なく「＃発行元データ」というシートの最終行のデータを取得したいのでしょうか？

[10-mo]

コメントありがとうございます。

var values = getmyFunction(targetuserId); のvaluesは正常に取得できているということですか？
→console logで確認し、D2の値を取得しています。

values.lengthが1（あくまで例です）なのにif文の中が実行されないということですか？ それともif文の中は実行されているのに sh2.getRange(17,1,values.length,values[0].length).setValues(values); でシートへの反映がされていないということですか？
→if文の作動はしていません。但し、会員番号が反映されていると作動します。（手動で会員番号入力し確認）
会員番号がセットされません。これがセットされれば、日付等の情報が反映されます。

[10-mo]

getmyFunctionという関数がそもそも会員番号を引数にしてその番号にマッチする情報を取得するような作りになっていると思いますが実際は会員番号とは関係なく「＃発行元データ」というシートの最終行のデータを取得したいのでしょうか？
→会員番号を引数に、その番号にマッチする情報を取得したいです。

[YAmaGNZ]

＞if文の作動はしていません。
if文の条件を満たしていないのであれば「情報が反映されない」のは当たり前のことではないですか？
問題は「情報が反映されない」ことではなく「if文の条件を満たさない（values.lengthが1以上ではない）」ということですよね？

＞会員番号を引数に、その番号にマッチする情報を取得したいです
そうなると引数に与える会員番号はどのように決定されるのでしょうか？
提示されたコードを読む限り「#発行PDF」というシートの’D2’にあるデータで検索するようになっていますが
そのデータは「手動で会員番号入力し」と仰っています。
これを変更するには事前に会員番号を決定する必要が出てくるわけですがこれはどのように決定するのですか？
ようは「#発行PDF」というシートの’D2’はどのように決定されるのですかということになるかと思います。

[10-mo]

会員番号だけがGASでは現状セットされないので、手動で入力しています。

会員番号は予め決めております。発行元データに入れています。

D2の値を発行元データシートから引っ張りたいのです。現状の上記のプログラムでセットされると思い作成してきたのですが、手詰まりになりご教示いただきたく投稿した次第です。

[YAmaGNZ]

var targetuserId = sh2.getRange('D2').getValue();
var values = getmyFunction(targetuserId);
と「#発行PDF」というシートの’D2’にあるデータを取得するコードしかありません。

また「会員番号は予め決めております。発行元データに入れています。」とありますが、そこから取得するのであれば
var targetuserId = sh1.getRange('そのデータのある場所').getValue();
とすべきなのでは？
そして
sh2.getRange('D2').setValue(targetuserId);
とセットすればよろしいかと思います。

[10-mo]

遅い時間にありがとうございます！

アドバイスいただいたようにコード変更してみたところ確かに反映されました！ありがとうございます。

【修正版】ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
var targetuserId = sh1.getRange("I5").getValue();　//ここでI列の会員番号を取得
var values = getmyFunction(targetuserId);
console.log(targetuserId);//D2に入っている値

if(values.length > 0){
sh2.getRange('A17:Z17').clearContent();//#発行PDFのA17～Z17(日付・会員番号・但書・金額)を削除
//A17:Z17にあるデータがそれぞれ領収書テンプレートに挿入される。
//発行PDFシート D2にはE17(会員番号)
//J3には=arrayformula(iferror(vlookup(D2,'＃発行元データ'!$I$3:$J,2,false),"")) (名前)←D2の会員番号が入ると出てくる。
//J8にはI17（但書）/ I9にはA17(日付)
sh2.getRange(17,1,values.length,values[0].length).setValues(values);
//console.log(sh2.getRange('A17:Z17'));
}
sh2.getRange('D2').setValue(targetuserId);//I列で取得した会員番号を発行PDFのD2=会員ナンバーセルに入れる
}
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

[10-mo]

もう一点、ご教示いただきたいのですが
var targetuserId = sh1.getRange("I5").getValue();　//ここでI列の会員番号を取得

の（"I5")の部分、ここを列の最終行を繰り返し取得したい場合はどのようにすればよいのでしょうか？

[YAmaGNZ]

＞ここを列の最終行を繰り返し取得したい場合
"最終行"という言い方をすると最後の1行を示すことになるので「繰り返し」という言葉は合いません。
多分、「最後に処理した後に追加された行を繰り返し取得したい」ということかと思います。
その場合、「ここまで処理した」といった識別ができるようにしなくてはなりません。

また、最後の行から会員番号を取得し、その会員番号を含んだものをその最後の行を含んだデータから取得するというよくわからない状況にならないですか？
例）
最終行が10行目だったとして"I10"から会員コード（1とする）を取得します。
その後getmyFunction関数にて3行目～10行目のデータから該当会員コードのデータを取得するという処理をおこなう
次に11行目が追加されたとき”I11”から会員コード（1とする）を取得し、
その後getmyFunction関数にて3行目～10行目のデータから該当会員コードのデータを取得するとなると、少なくとも1回目で10行目を取得し、2回目で10行目と11行目を取得するということになりませんか？

こういったことを考えると本当に欲しいデータがなんなのかが私には理解できていません。

[10-mo]

拙い説明を汲んでいただき有難うございます。
確かにご指摘の通り処理の識別が必要になってしまい、複雑になってしまいます。
例で挙げられている通り、こちらのご指摘もごもっとでして、難しく考えてしまっております。。。

実現したいのは、会員番号を取得し別シートへ転記したいのです。
Googleフォームから送信されたら必ずこの会員番号が追加されるので、新しく追加されたデータ（会員番号、名前、日付、金額、但書）を取得し発行PDFへ転記するのがゴールです。会員番号がキーになっているので、Googleフォームから追加されたら新たに会員番号を取得し転記できれば良いのですが、この会員番号の取得に苦戦しております。

どうすればこの会員番号を扱えるのでしょうか？

[YAmaGNZ]

この処理のゴールは
①　Googleフォームにてデータが入力され、それがスプレッドシートの”＃発行元データ”というシートに1件１行として追加される
②　追加されたデータ１行を１つの領収書としてPDFを出力する
③　一度領収書を発行したのであれば２度目は発行しない
ということで合っていますか？

[10-mo]

①Googleフォーム「送信」→集計シート→#発行元データに１件１行として追加
（フォーム内で会員選択の項目で「会員番号＋名前」になっているので、集計シートから#発行元データにフィルター関数で纏めて会員番号と名前をLeft・Right関数で分割しています）
②１行＝１つの領収書としてPDF出力
③一度発行したら２度目の出力は行いません。

Answer 1

”#発行元データ”シートの最後の列を処理済みかどうかを格納する列とし
下記のように処理した行に”済”とセットするようにしてはどうでしょうか？

function myFunction() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh1 = ss.getSheetByName('#発行元データ');

  // データを取得する
  let lastRow = sh1.getLastRow();
  //　最後の列を”処理済み”を格納する列とし、そこまで取得する
  //  提示ソースが10列目となっていたので11列としています
  let values = sh1.getRange(1,1,lastRow,11).getValues();

  // ３行目からスタート
  for (let lineindex = 3;lineindex <= lastRow; lineindex++){
    // 対象行の11列目（K列）が”済”ではないものを未処理とする
    if (values[lineindex-1][10] != '済') {
      // PDF出力以降の処理をする
      // 引数として対象行のデータを与える
      OutputPDF(values[lineindex-1]);
      // 現在の行を処理済みとする
      sh1.getRange('K' + lineindex.toString()).setValue('済')
    }
  }
}

function OutputPDF(linedata) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh2 = ss.getSheetByName('#発行PDF');

  //　セルをクリアする
  sh2.getRange('A17:Z17').clearContent();//#発行PDFのA17～Z17の内容を削除

  // 行データを領収書出力用シートに書き込む
  sh2.getRange(17,1,1,linedata.length).setValues([linedata]);

  // PDF化＋メール送信
 
}

Comments

[10-mo]

上記のご提案ありがとうございます！
確かにこちらの方が、分かりやすいです。

早速試してみたのですが、K列の「済」が最終行の1,000列まで全て記入されてしまいます。。。。

[YAmaGNZ]

そうなるとlastRowの取得が正常にできていなくて1000となっているのでしょうから、そのあたりを改善しましょう

[10-mo]

ご指摘いただいように確認したところ、2001となっていました。。。
let lastRow = sh1.getRange(sh1.getMaxRows(),6).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
で最終行の取得ができるようになりました。

また新たな問題が起こっておりまして、#発行ＰＤＦのA17~Z17に日付・コード・但書・金額のデータが反映されません。これはどのように解決すれば良いのでしょうか？

[YAmaGNZ]

エディタで実行されているでしょうから、ブレイクポイントなど設定して実行を中断して変数の中身を確認したりステップ実行するなりしてデバッグしましょう。

[10-mo]

分かりました！

細かいご指摘からご相談にのっていただきありがとうございました！
ご提案いただいたコードを元に動かしてみます。

本当にありがとうございました！