質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.50%

  • Google Apps Script

    1264questions

    Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

  • Google

    826questions

    Googleは、アメリカ合衆国に位置する、インターネット関連のサービスや製品を提供している企業です。検索エンジンからアプリケーションの提供まで、多岐にわたるサービスを提供しています。

  • Google API

    673questions

    Googleは多種多様なAPIを提供していて、その多くはウェブ開発者向けのAPIです。それらのAPIは消費者に人気なGoogleのサービス(Google Maps, Google Earth, AdSense, Adwords, Google Apps,YouTube等)に基づいています。

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 516

Miyabi_Kanda

score 1

 前提・実現したいこと

スプレッドシート上で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);       //合計を代入



      }
    }

}
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hot-lemoned

    2018/08/08 09:59

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

    キャンセル

  • Miyabi_Kanda

    2018/08/08 11:33

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

    キャンセル

回答 1

checkベストアンサー

+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('--処理終了--');
}

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/08/09 15:15

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

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

    キャンセル

  • 2018/08/09 15:22

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

    キャンセル

  • 2018/08/09 20:26

    回答を追記しました。

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

    キャンセル

  • 2018/08/09 20:28

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

    キャンセル

  • 2018/08/10 14:03

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

    キャンセル

同じタグがついた質問を見る

  • Google Apps Script

    1264questions

    Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

  • Google

    826questions

    Googleは、アメリカ合衆国に位置する、インターネット関連のサービスや製品を提供している企業です。検索エンジンからアプリケーションの提供まで、多岐にわたるサービスを提供しています。

  • Google API

    673questions

    Googleは多種多様なAPIを提供していて、その多くはウェブ開発者向けのAPIです。それらのAPIは消費者に人気なGoogleのサービス(Google Maps, Google Earth, AdSense, Adwords, Google Apps,YouTube等)に基づいています。

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