スプレッドシート上でGoogle Apps Script(GAS)を動かして条件に応じて転記をしたい。同じ名称はまとめて転記をしたい。

Question

前提・実現したいこと

スプレッドシート上でGoogle Apps Script(GAS)を動かして条件に応じて転記をしたい。
転記をする際に同じ名称のものは同じシートに転記したい。

###条件

顧客名と商品名が羅列されたスプレッドシートAから、
請求書を発行するGASを作っています。

ifで条件をかけて、発行するものと、しないもので分けて1シート1行で転記をすることはできました。
しかし、同一名称のもの（下記A名前が同一のもの）は請求書の明細に羅列して転記をすることができません。

スプレッドシートAは以下のようになってます。(説明のため簡略化してます)

[シートA]
┌----------┬----------┬----------┬----------┬----------┐
| A名前 |B郵便番号 | C住所 | D商品名 | D値段 |
|〇〇〇〇〇 |△△△-△△|東京都… | にんじん |　¥100 |
├----------┼----------┼----------┼----------┼----------┤
|〇〇〇〇〇 |△△△-△△|東京都… |じゃがいも |　¥80 |
├----------┼----------┼----------┼----------┼----------┤
|〇〇〇〇〇 |△△△-△△|東京都… |さつまいも |　¥150 |
├----------┼----------┼----------┼----------┼----------┤
・
・

ここの名前の部分が同一のものを、転記をするときに明細のように下側に羅列をしたいです。
よろしくお願いします。

[シートB]

郵便番号 △△△-△△
住所　　東京都…
名前　〇〇〇〇〇 　様

商品名　　　単価　　　個数　　　小計
にんじん　　100　　　　1　　　　100
じゃがいも　80　　　　 1　　　　 80
さつまいも　150　　　　1　　　　150

作ったGAS

function myfunction() {  
  
/*基本設定*/
  var today = new Date();//現在の日付を取得
  var tmplId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";　　　//テンプレートIDの定義（シートBのテンプレ）
  var dstDirId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";　　//保存先IDの定義
  
 
/*コピー処理*/
  var tmpl = DriveApp.getFileById(tmplId);　　　　//テンプレートIDからデータを取得
  var dstDir = DriveApp.getFolderById(dstDirId); //IDからフォルダーの場所を取得
  var ss_act = SpreadsheetApp.getActiveSpreadsheet();    //アクティブなシートを取得
  var ss_copyFrom = ss_act.getSheetByName('シートA');　　　//アクティブなシート内の「合算」タブ内を参照
  var LR_ssf = ss_copyFrom.getRange('B2:B').getLastRow();  //アクティブなシート内の「合算」タブ内の最終行を取得＊つもり
  var Data =ss_copyFrom.getDataRange().getValues(); 
  var n = 1;
  
/*格納作業*/
  for(var i = 0; i<= LR_ssf; i++){                       
      if(Data[i][6] === '発行'){ 　 //条件分岐用：明細発行有無 
        
        var s_value01 = Data[i][1];　 //顧客名
        var s_value02 = Data[i][2];   //郵便番号
        var s_value03 = Data[i][3];   //住所
        var s_value04 = Data[i][4];   //建物名等
        var s_value05 = Data[i][7];   //敬称
        
        var s_value06 = Data[i][18];   //内容
        var s_value07 = Data[i][19];   //単価
        var s_value08 = Data[i][20];   //数量
        var s_value09 = Data[i][21];   //合計
 
/*データ発行処理*/
      var invoice = tmpl.makeCopy(s_value01,dstDir);  　　//指定フォルダにデータを作成し名称をB2から↓(i,2)の値とする。
  
/*転記シート処理*/
      var sst = SpreadsheetApp.open(invoice);　　　　　　　  //作成済みデータ開く
      var t_sheets = sst.getSheets();　　　　　　　　　　　　　//sheetsの値を定義
      var t_sheet = t_sheets[0];　　　　　　　　　　　　　　　　//作成済みファイルは1シートしかないので0番目を取得
  
/*代入処理*/
      t_sheet.getRange('B1').setValue('〒'+s_value02);　//郵便番号　を代入
      t_sheet.getRange('B2').setValue(s_value03);　　　　　 //住所を代入
      t_sheet.getRange('B3').setValue(s_value04);　　　　　 //建物名等を代入
      t_sheet.getRange('D5').setValue(s_value05);　　　　　 //敬称を代入
      t_sheet.getRange('B5').setValue(s_value01);　　　　　 //送付先名称を代入
      t_sheet.getRange('I1').setValue("001");　　　　　     //請求IDを代入*ここどうしようか悩んでます。
    
      t_sheet.getRange('I2').setValue(today);             //本日を代入
      t_sheet.getRange('C21').setValue(s_value01);　　　　 //件名を代入
      t_sheet.getRange('B27').setValue(s_value06);　　　　 //内容を代入
      t_sheet.getRange('F27').setValue(s_value07);　　　　 //単価を代入
      t_sheet.getRange('H27').setValue(s_value08);　　　　 //数量を代入
      t_sheet.getRange('I27').setValue(s_value09);       //合計を代入
        
        
          
      }
    }
      
}

Comments

[hot-lemoned]

合算シート？シートA？　には名前「○○○○○」ごとに商品名が記載されているのでしょうか？　例えば１行目は名前：○○○○○　で商品名：にんじん、２行目は名前：○○○○○　で商品名：じゃがいも、３行目は名前：△△△△△　で商品名：にんじん　とか。。。

[Miyabi_Kanda]

わかりにくくて申し訳ありません！その認識で間違いありません！

Answer 1

すいません、説明が下手なのでコードにしてみました。
修正した点、考慮した点を簡単に列記します。

• 合算シートの最終行が取得できていなさそうだったのでgetLastRow()を使用しています
• もともとのソースですと合算シートの行ごとにスプレッドシートを作成する流れになっていたため、

　「発行」というキーワードがあり、一番初めに取得した顧客名を基準にスプレッドシートを作成しています。
逆に言うと制限として「発行」というキーワードが複数の顧客にまたがっているとスプレッドシートは作成されません。
合算シートがどの程度のボリュームかが検討がつかなかったので、1顧客ずつ作成する流れにしてしまいました。

• 1つの顧客情報の中に配列で内容？商品名？を格納するようにしました。
• 1つの項目ごとにsetValue()で記載していたのを、setValues()を使用して、

　配列でまとめて記載する流れに変えました。
都度、GASにアクセスする流れは遅くなるので、なるべく一度に記載したほうがよいと思いました。

function myfunction() {
  Logger.log('--処理開始--');
  
/*基本設定*/
  var tmplId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";　　　//テンプレートIDの定義
  var dstDirId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";　　//保存先IDの定義

/*コピー処理*/
  var tmpl = DriveApp.getFileById(tmplId);　　　　//テンプレートIDからデータを取得
  var dstDir = DriveApp.getFolderById(dstDirId); //IDからフォルダの場所を取得
  var ss_act = SpreadsheetApp.getActiveSpreadsheet();    //アクティブなシートを取得
  
  var ss_copyFrom = ss_act.getSheetByName('合算');　　　//アクティブなシート内の「合算」タブ内を参照
  var LR_ssf = ss_copyFrom.getLastRow();
  var Data =ss_copyFrom.getDataRange().getValues();
  var n = 1;

/*格納作業*/
  var strage_userinfo = [];
  for(var i=1; i<LR_ssf; i++){
      if(Data[i][6] === '発行'){
        //条件分岐用：明細発行有無
        
        if(strage_userinfo[0] == undefined){
          strage_userinfo.push(Data[i][1]); //顧客名
          strage_userinfo.push(Data[i][2]); //郵便番号
          strage_userinfo.push(Data[i][3]); //住所
          strage_userinfo.push(Data[i][4]); //建物名等
          strage_userinfo.push(Data[i][7]); //敬称
          strage_userinfo[5] = [];
        }
        
        var strage_data = [];
        if(strage_userinfo[0] === Data[i][1]){
          strage_data.push(Data[i][18]); //内容
          strage_data.push(Data[i][19]); //単価
          strage_data.push(Data[i][20]); //数量
          strage_data.push(Data[i][21]); //合計
        }
        strage_userinfo[5].push(strage_data);
      } 
  }
  //Logger.log(strage_userinfo);

/*データ発行処理*/
  var invoice = tmpl.makeCopy(strage_userinfo[0],dstDir);  　　//指定フォルダにデータを作成し名称をB2から↓(i,2)の値とする。
  
/*転記シート処理*/
  var sst = SpreadsheetApp.open(invoice);　　　　　　　  //作成済みデータ開く
  var t_sheets = sst.getSheets();　　　　　　　　　　　　　//sheetsの値を定義
  var t_sheet = t_sheets[0];　　　　　　　　　　　　　　　　//作成済みファイルは1シートしかないので0番目を取得

  //顧客情報
  var invoice_inforange = t_sheet.getRange('A1:I21');
  var invoice_infoset = invoice_inforange.getValues();
  
  invoice_infoset[0][1] = ('〒'+strage_userinfo[1]); //B1:郵便番号　を代入
  invoice_infoset[1][1] = (strage_userinfo[2]); //B2:住所を代入
  invoice_infoset[2][1] = (strage_userinfo[3]); //B3:建物名等を代入
  invoice_infoset[4][1] = (strage_userinfo[0]); //B5:送付先名称を代入
  invoice_infoset[4][3] = (strage_userinfo[4]); //D5:敬称を代入
  invoice_infoset[0][8] = ("001"); //I1:請求IDを代入*ここどうしようか悩んでます。
  invoice_infoset[20][2] = (strage_userinfo[0]); //C21:件名を代入
  
  /*
  var dt = new Date(); //現在の日付を取得
  var y = dt.getFullYear();
  var m = ("00" + (dt.getMonth()+1)).slice(-2);
  var d = ("00" + dt.getDate()).slice(-2);
  var today = y + "/" + m + "/" + d;
  */
  var today = new Date();
  invoice_infoset[1][8] = (today); //I2:本日を代入
  
  //Logger.log(invoice_infoset);
  
  //出力データ
  var invoice_datarange = t_sheet.getRange(27, 2, strage_userinfo[5].length, 8);
  var invoice_dataset = invoice_datarange.getValues();
  for(var t=0; t < invoice_dataset.length; t++){
    invoice_dataset[t][0] = (strage_userinfo[5][t][0]); //B27:内容を代入
    invoice_dataset[t][4] = (strage_userinfo[5][t][1]); //F27:単価を代入
    invoice_dataset[t][6] = (strage_userinfo[5][t][2]); //H27:数量を代入
    invoice_dataset[t][7] = (strage_userinfo[5][t][3]); //I27:合計を代入
  }
  //Logger.log(invoice_dataset);
  
  //顧客情報を転記シートに記入
  invoice_inforange.setValues(invoice_infoset);
  //出力データを転記シートに記入
  invoice_datarange.setValues(invoice_dataset);
  
  Logger.log('--処理終了--');
}

1顧客ずつスプレッドシートを作成するのではなく、
まとめて作成する場合にはさらに合算シート内でフラグを設けておいたほうがよいかと思いました。
あ…　それとも月末とかにまとめて請求書発行するのであれば、別にフラグ関係なかったか…orz

いかがでしょうか？
まだ勉強中の身のため説明が下手ですいません。。。

---

追記（2018/08/09）

複数のスプレッドシートが作成できるようにしてみました。

格納作業のところで、顧客名ごとにデータをまとめる作業をしました。
もう少し効率のよい書き方があると思います。。。

データ発行処理のところで、顧客名ごとにスプレッドシートを作成しています。
GASに問い合わせする回数を減らすのがいずれもポイントなんだと思います。

簡単なテストしかしていないので適宜修正してください。

function myfunction() {
  Logger.log('--処理開始--');
  
/*基本設定*/
  var tmplId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";　　　//テンプレートIDの定義
  var dstDirId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";　　//保存先IDの定義

/*コピー処理*/
  var tmpl = DriveApp.getFileById(tmplId);　　　　//テンプレートIDからデータを取得
  var dstDir = DriveApp.getFolderById(dstDirId); //IDからフォルダの場所を取得
  var ss_act = SpreadsheetApp.getActiveSpreadsheet();    //アクティブなシートを取得
  
  var ss_copyFrom = ss_act.getSheetByName('合算');　　　//アクティブなシート内の「合算」タブ内を参照
  var LR_ssf = ss_copyFrom.getLastRow();
  var Data = ss_copyFrom.getDataRange().getValues();
  var n = 1;

  //Logger.log(Data);

/*格納作業*/
  var strage_userinfo = [];  
  for(var i=1; i < LR_ssf; i++){
    if(Data[i][6] == '発行'){
      //条件分岐用：明細発行有無
      if(strage_userinfo.length > 0){
        //配列に1つ以上顧客情報がある
        for(var a=0; a < strage_userinfo.length; a++){
          var search_name = strage_userinfo[a].indexOf(Data[i][1]);
          if(search_name >=0){
            //顧客名が見つかった
            var strage_data = [
              Data[i][18], //内容
              Data[i][19], //単価
              Data[i][20], //数量
              Data[i][21]  //合計
            ];
            strage_userinfo[a][5].push(strage_data);
            break;
          
          }else if(search_name == -1){
            //顧客名見つからない
            if(a+1 == strage_userinfo.length){
              //配列内には顧客名なし
              var user_array =[
                Data[i][1], //顧客名
                Data[i][2], //郵便番号
                Data[i][3], //住所
                Data[i][4], //建物名等
                Data[i][7],  //敬称
                []
              ];
              var strage_data = [
                Data[i][18], //内容
                Data[i][19], //単価
                Data[i][20], //数量
                Data[i][21]  //合計
              ];
              user_array[5].push(strage_data);
              strage_userinfo.push(user_array);
              break;
            }
          }
        }
      
      }else{
        Logger.log("配列なし");
        //配列の中身が存在していない
        var user_array =[
          Data[i][1], //顧客名
          Data[i][2], //郵便番号
          Data[i][3], //住所
          Data[i][4], //建物名等
          Data[i][7],  //敬称
          []
        ];
        var strage_data = [
          Data[i][18], //内容
          Data[i][19], //単価
          Data[i][20], //数量
          Data[i][21]  //合計
        ];
        user_array[5].push(strage_data);
        strage_userinfo.push(user_array);
      }
    }
  }
  //Logger.log(strage_userinfo);
  
/*データ発行処理*/
  for(var v=0; v < strage_userinfo.length; v++){
    Logger.log("--" + (v+1) +"枚目の処理--");
    //スプレッドシートのコピー
    var invoice = tmpl.makeCopy(strage_userinfo[v][0],dstDir);  　　//指定フォルダにデータを作成し名称をB2から↓(i,2)の値とする。
    
    //転記シート処理
    var sst = SpreadsheetApp.open(invoice);　　　　　　　  //作成済みデータ開く
    var t_sheets = sst.getSheets();　　　　　　　　　　　　　//sheetsの値を定義
    var t_sheet = t_sheets[0];　　　　　　　　　　　　　　　　//作成済みファイルは1シートしかないので0番目を取得
    
    //顧客情報
    var invoice_inforange = t_sheet.getRange('A1:I21');
    var invoice_infoset = invoice_inforange.getValues();
    
    invoice_infoset[0][1] = ('〒'+strage_userinfo[v][1]); //B1:郵便番号　を代入
    invoice_infoset[1][1] = (strage_userinfo[v][2]); //B2:住所を代入
    invoice_infoset[2][1] = (strage_userinfo[v][3]); //B3:建物名等を代入
    invoice_infoset[4][1] = (strage_userinfo[v][0]); //B5:送付先名称を代入
    invoice_infoset[4][3] = (strage_userinfo[v][4]); //D5:敬称を代入
    invoice_infoset[0][8] = ("001"); //I1:請求IDを代入*ここどうしようか悩んでます。
    invoice_infoset[20][2] = (strage_userinfo[v][0]); //C21:件名を代入
    
    /*
    var dt = new Date(); //現在の日付を取得
    var y = dt.getFullYear();
    var m = ("00" + (dt.getMonth()+1)).slice(-2);
    var d = ("00" + dt.getDate()).slice(-2);
    var today = y + "/" + m + "/" + d;
    */
    var today = new Date();
    invoice_infoset[1][8] = (today); //I2:本日を代入
    
    //出力データ
    var invoice_datarange = t_sheet.getRange(27, 2, strage_userinfo[v][5].length, 8);
    var invoice_dataset = invoice_datarange.getValues();
    for(var t=0; t < invoice_dataset.length; t++){
      invoice_dataset[t][0] = (strage_userinfo[v][5][t][0]); //B27:内容を代入
      invoice_dataset[t][4] = (strage_userinfo[v][5][t][1]); //F27:単価を代入
      invoice_dataset[t][6] = (strage_userinfo[v][5][t][2]); //H27:数量を代入
      invoice_dataset[t][7] = (strage_userinfo[v][5][t][3]); //I27:合計を代入
    }
    //Logger.log(invoice_dataset);
    
    //顧客情報を転記シートに記入
    invoice_inforange.setValues(invoice_infoset);
    //出力データを転記シートに記入
    invoice_datarange.setValues(invoice_dataset);
    Logger.log("--" + (v+1) +"枚目の処理終了--");
  }
  
  Logger.log('--処理終了--');
}

Comments

[Miyabi_Kanda]

ありがとうございます！
試してみたところ、1シートに複数の顧客が混在している状況だったのでundefinedですごい量明細ができてしまいました(笑)
ただやりたかったことはほとんどできました！ありがとうございます！

ちなみに複数の顧客を1つのシートに混在した状態でやるとすると、loopのところを書き換えれば行けそうですかね

[Miyabi_Kanda]

ちなみに顧客は100を超えているのでできれば1つずつやるのは避けたいのです（汗）

[hot-lemoned]

回答を追記しました。

注意点としては、
・スプレッドシートの出力が100以上必要なのであればタイムオーバーが考えられますので、
　時間を考慮して出力するコードに変えたほうがいいと思います。
　1つのスプレッドシートを作成するので2秒程度かかっていました。
・上記に関連しますが、出力したかを確認するフラグか列を合算シート内に追加したほうがよいと思います。
　でないと、実行するごとに新しいスプレッドシートが作成されてしまいます。
という感じでしょうか。

[hot-lemoned]

あとコード見ればわかっていると思いますが、合算シートの1行目をタイトル行としています。
格納作業で　var i=1　にしていますので必要に応じて修正してください。

[Miyabi_Kanda]

動きました！ありがとうございます！