GoogleAppsScriptでデータ集計｜スプレッドシートの売上合計を算出（GAS）

Google Apps Script(GAS)を使ってデータ集計をすると、スプレッドシートの作業を効率化できます。

実際、データ算出のプログラムを作成しておけば、あとは週毎や月毎にプログラムを実行するように設定しておけば、自動でデータを集計してくれます。

しかし、GASを使ったことがない人や、GASについて勉強を始めたばかりだと、どのようにプログラムを作ればいいのか分からない人も多いです。

そこで、この記事では以下のようなデータ集計を実行するGASプログラムを解説します。

・GoogleスプレッドシートのデータをGASで集計する方法が分かる

・データ集計の定期的にパソコンに実行させる方法が分かる

・GASの始め方を動画で解説しているので、初心者でも迷わない

以下で動画と実際のプログラムソースを使いながら解説をしていきます。

1 この記事で紹介するプログラムで出来ることを動画で紹介
2 Google Apps Scriptの始め方を動画で解説
3 プログラムのコード全体像
4 プログラムの解説
- 4.1 準備1｜Moment.jsを使うためのライブラリキーで使用可能（*1）
5 Google Apps Scriptをもっと勉強したい人へ
- 5.1 GASで出来ることって何を動画で解説
- 5.2 オススメの書籍を紹介
6 Googleスプレッドシートの仕事を早くしたいなら
- 6.1 Googleスプレッドシートの時短ワザ12
- 6.2 Googleスプレッドシートのショートカットキー基礎24

この記事で紹介するプログラムで出来ることを動画で紹介

現在準備中のため、動画が完成したら更新します。

Google Apps Scriptの始め方を動画で解説

Google Apps Scriptの始め方や使い方は以下の動画で解説しています。

そもそもGoogle Apps Scriptってどのように始めればいいのか分からない人は、最初に以下の動画をご覧ください。

プログラムのコード全体像

はじめに、この記事で紹介すうるプログラムの全体像を説明します。

●GASで作る！　月ごとに自動でデータ集計するツールの全体像

ステップ1｜集計データの最終行を取得して全データを配列に格納
ステップ2｜集計データを月ごとに集計し、結果を配列に格納
ステップ3｜次ごとに集計された配列データを転記

//準備1｜Moment.jsを使うためのライブラリキーで使用可能（*1）
//(*1) MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48

//コード0｜プロシージャ名
function myFunction() {    
  
  //コード1｜スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.openById('1n0ngimPjdZfJzreWZQ5SYM8d175zwEBgzjWlVJOHxqE');
  var sheet1 = spreadsheet.getSheetByName('シート1');
  var sheet2 = spreadsheet.getSheetByName('シート2');
  
  //コード2｜最終行の取得
  var Lastrow1 = sheet1.getLastRow();
  var Lastrow2 = sheet2.getLastRow();
  
  //コード3｜計算結果格納用の配列
  var values = [];
  
  //コード4｜データ取得
  var values1 = sheet1.getDataRange().getValues();
  var values2 = sheet2.getDataRange().getValues();
  
  var count1 = values1.length;
  var count2 = values2.length;

  //コード5｜シート2のデータ取得
  for(var j=1;j<count2;j++){
    var uriage=0;
    var keihi=0;
    var hanbai=0;
    var toiawase=0;

    var d2 = values2[j][0];
    var Start = Moment.moment(d2); //*1
    var End = Start.add(1, 'M').format(); //*1  
    Start = Moment.moment(d2).format(); //*1
  
    //コード6｜シート1とシート2のマッチング結果を取得
    for(var i=1;i<count1;i++){
      
      var Date = Moment.moment(values1[i][0]).format(); //*1
      
      if(Moment.moment(Date).isAfter(Start) || Moment.moment(Date).isSame(Start)){　//*1
        if(Moment.moment(Date).isBefore(End)){　//*1
          
          uriage+=values1[i][1];
          keihi+=values1[i][2];
          hanbai+=values1[i][3];
          toiawase+=values1[i][4];
          
        }
      }      
    }

    //コード7｜マッチング結果を配列に格納
    values[j-1]=[uriage,keihi,hanbai,toiawase];
    
  }

  //コード8｜マッチング結果をまとめてシート2に出力
  sheet2.getRange(2,2,values.length,values[0].length).setValues(values); 
 
}

//準備1｜Moment.jsを使うためのライブラリキーで使用可能（*1）

//(*1) MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48

//コード0｜プロシージャ名

function myFunction() {

//コード1｜スプレッドシートの設定

var spreadsheet = SpreadsheetApp.openById('1n0ngimPjdZfJzreWZQ5SYM8d175zwEBgzjWlVJOHxqE');

var sheet1 = spreadsheet.getSheetByName('シート1');

var sheet2 = spreadsheet.getSheetByName('シート2');

//コード2｜最終行の取得

var Lastrow1 = sheet1.getLastRow();

var Lastrow2 = sheet2.getLastRow();

//コード3｜計算結果格納用の配列

var values = [];

//コード4｜データ取得

var values1 = sheet1.getDataRange().getValues();

var values2 = sheet2.getDataRange().getValues();

var count1 = values1.length;

var count2 = values2.length;

//コード5｜シート2のデータ取得

for(var j=1;j<count2;j++){

var uriage=0;

var keihi=0;

var hanbai=0;

var toiawase=0;

var d2 = values2[j][0];

var Start = Moment.moment(d2); //*1

var End = Start.add(1, 'M').format(); //*1

Start = Moment.moment(d2).format(); //*1

//コード6｜シート1とシート2のマッチング結果を取得

for(var i=1;i<count1;i++){

var Date = Moment.moment(values1[i][0]).format(); //*1

if(Moment.moment(Date).isAfter(Start) || Moment.moment(Date).isSame(Start)){　//*1

if(Moment.moment(Date).isBefore(End)){　//*1

uriage+=values1[i][1];

keihi+=values1[i][2];

hanbai+=values1[i][3];

toiawase+=values1[i][4];

}

//コード7｜マッチング結果を配列に格納

values[j-1]=[uriage,keihi,hanbai,toiawase];

}

//コード8｜マッチング結果をまとめてシート2に出力

sheet2.getRange(2,2,values.length,values[0].length).setValues(values);

}

プログラムの解説

準備1｜Moment.jsを使うためのライブラリキーで使用可能（*1）
コード0｜プロシージャ名
コード1｜スプレッドシートの設定
コード2｜最終行の取得
コード3｜計算結果格納用の配列
コード4｜データ取得
コード5｜シート2のデータ取得
コード6｜シート1とシート2のマッチング結果を取得
コード7｜マッチング結果を配列に格納
コード8｜マッチング結果をまとめてシート2に出力

準備1｜Moment.jsを使うためのライブラリキーで使用可能（*1）

はじめに、Google Apps Scriptで日付・時刻を簡単に扱えるようになる「Moment.js」というライブラリを導入します。

「Moment.js」ライブラリは、下記の手順で登録します。

ステップ1｜「リソース」→「ライブラリ」でライブラリウインドウを開く

ステップ2｜「Moment.js」のライブラリキーを追加する

ステップ1｜「リソース」→「ライブラリ」でライブラリウインドウを開く

画像のように、データ集計ツールを作成するプロジェクト画面を開き「リソース」→「ライブラリ」の順で選択すると、ライブラリウインドウが開きます。

ステップ2｜「Moment.js」のライブラリキーを追加する

ライブラリウインドウが開いたら、画像の「Add a library」欄に「Moment.js」のプロジェクトキー「MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48」を入力し「追加」ボタンを押します。

「追加」ボタンを押すと「Moment.js」が表示されるので、最新バージョンを選択しましょう。

最後に「保存」ボタンを押せばこのプロジェクト上で「Moment.js」が使えるようになります。

コード0｜プロシージャ名

function myFunction() {

1	function myFunction() {

このプログラムが「myFunction」という名前のプロシージャであることを示しており、ここから順に処理が始まります。

コード1｜スプレッドシートの設定

//準備1｜MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48 Moment.jsを使うためのライブラリキーで使用可能（*1）

function myFunction() {    
  
  //コード1｜スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.openById('1n0ngimPjdZfJzreWZQ5SYM8d175zwEBgzjWlVJOHxqE');
  var sheet1 = spreadsheet.getSheetByName('シート1');
  var sheet2 = spreadsheet.getSheetByName('シート2');

//準備1｜MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48 Moment.jsを使うためのライブラリキーで使用可能（*1）

function myFunction() {

//コード1｜スプレッドシートの設定

var spreadsheet = SpreadsheetApp.openById('1n0ngimPjdZfJzreWZQ5SYM8d175zwEBgzjWlVJOHxqE');

var sheet1 = spreadsheet.getSheetByName('シート1');

var sheet2 = spreadsheet.getSheetByName('シート2');

ここではまず、対象となるスプレッドシートを開き、同時に「spreadsheet」という変数に定義しています。

使用する関数は「SpreadsheetApp.openById(‘スプレッドシートID’);」で、IDを指定して特定のスプレッドシートを開くものとなります。

この際指定するIDは、スプレッドシートURLの内、画像の部分となります。

次に「getSheetByName(‘シート名’);」を使って、特定のシートを変数に定義しています。

ここでは、変数「sheet1」に「シート1」、変数「sheet2」に「シート2」を定義しています。

コード2｜最終行の取得

  var Lastrow1 = sheet1.getLastRow();
  var Lastrow2 = sheet2.getLastRow();

1 2	var Lastrow1 = sheet1.getLastRow(); var Lastrow2 = sheet2.getLastRow();

各シートの最終行を変数に定義しています。ここで使用されている「getLastRow();」はシートオブジェクトから指定された範囲の最終行を返すプロパティです。

サンプルコードのようにデータ範囲を指定していない場合は、シートオブジェクト内全体での最終行を返します。

サンプルコードでは、変数「Lastrow1」に「sheet1」の最終行、変数「Lastrow2」に「sheet2」の最終行を定義しています。

コード3｜計算結果格納用の配列

  var values = [];

1	var values = [];

最集計結果をシートに転記する際に使用する配列「values」を宣言します。

コード4｜データ取得

  var values1 = sheet1.getDataRange().getValues();
  var values2 = sheet2.getDataRange().getValues();
  
  var count1 = values1.length;
  var count2 = values2.length;

var values1 = sheet1.getDataRange().getValues();

var values2 = sheet2.getDataRange().getValues();

var count1 = values1.length;

var count2 = values2.length;

各シートのデータと、データの長さをそれぞれ変数に定義しています。
変数「values1」には「sheet1」のデータ、「values2」は「sheet2」のデータを「getValues();」で配列として格納しています。

「getValues();」はRangeオブジェクトの値を配列として格納します。

サンプルコードでは「sheet1.getDataRange()」「sheet2.getDataRange()」が対象となっていますが、これはそれぞれのシート上で値のある連続したデータ範囲を表しています。

次に取得した配列「values1」「values2」の長さを「lengthh;」で取得して、変数「count1」「count2」に定義しています。

コード5｜シート2のデータ取得

  for(var j=1;j<count2;j++){
    var uriage=0;
    var keihi=0;
    var hanbai=0;
    var toiawase=0;

    var d2 = values2[j][0];
    var Start = Moment.moment(d2); //*1
    var End = Start.add(1, 'M').format(); //*1  
    Start = Moment.moment(d2).format(); //*1

for(var j=1;j<count2;j++){

var uriage=0;

var keihi=0;

var hanbai=0;

var toiawase=0;

var d2 = values2[j][0];

var Start = Moment.moment(d2); //*1

var End = Start.add(1, 'M').format(); //*1

Start = Moment.moment(d2).format(); //*1

「for」文を使ってループ処理を行っています。初期値「1」のカウンター「j」が「1」ずつ加算されていき、変数「count2」未満の間、繰り返し処理が行われています。

ループ処理の内部では、集計結果となる4つの変数「uriage」「keihi」「hanbai」「toiawase」を初期値「0」で宣言しています。

続いて、変数「d2」に「シート2」のデータを格納した配列「values2」の「j」番目の「0」番目、つまり「シート2」の「j」行目「1」列目の値を格納しています。

次に「d2」に格納された日付を基に変数「Start」「End」を定義しています。

まず「Start」には、「values2」から取得した文字列「d2」が表す日付を定義します。

続いて「Start」に「add(1, ‘M’).format();」を使用して、1ヶ月後の日付を取得、「End」に定義しています。

さらに「Start」に「format();」を使用して、標準の日付形式に整形しています。

コード6｜シート1とシート2のマッチング結果を取得

    for(var i=1;i<count1;i++){
      
      var Date = Moment.moment(values1[i][0]).format(); //*1
      
      if(Moment.moment(Date).isAfter(Start) || Moment.moment(Date).isSame(Start)){　//*1
        if(Moment.moment(Date).isBefore(End)){　//*1
          
          uriage+=values1[i][1];
          keihi+=values1[i][2];
          hanbai+=values1[i][3];
          toiawase+=values1[i][4];
          
        }
      } 
    }

for(var i=1;i<count1;i++){

var Date = Moment.moment(values1[i][0]).format(); //*1

if(Moment.moment(Date).isAfter(Start) || Moment.moment(Date).isSame(Start)){　//*1

if(Moment.moment(Date).isBefore(End)){　//*1

uriage+=values1[i][1];

keihi+=values1[i][2];

hanbai+=values1[i][3];

toiawase+=values1[i][4];

}

再度「for」文を使ってループ処理を行っています。初期値「1」のカウンター「i」が「1」ずつ加算されていき、変数「count1」未満の間、繰り返し処理が行われています。

まず、先ほどと同じように変数「Date」に「シート1」のデータを格納した配列「values1」から日付文字列を取得し、標準の日付形式に整形しています。

続いて、「if」文を用いて、条件に合致した場合のみ、4つの変数「uriage」「keihi」「hanbai」「toiawase」に「values1」の値を加算しています。

この時の条件は二つの「if」文を使って下記のように指定しています。

if文1：if(Moment.moment(Date).isAfter(Start) || Moment.moment(Date).isSame(Start))
- 変数「Date」が変数「Start」より後の日付、もしくは同じ日付
if文2：if(Moment.moment(Date).isBefore(End))
- 変数「Date」が変数「End」より前の日付

コード7｜マッチング結果を配列に格納

    values[j-1]=[uriage,keihi,hanbai,toiawase];

1	values[j-1]=[uriage,keihi,hanbai,toiawase];

コード3で宣言しておいた、集計結果格納用の配列「values」に4つの変数「uriage」「keihi」「hanbai」「toiawase」を一つの配列として格納します。

コード8｜マッチング結果をまとめてシート2に出力

  sheet2.getRange(2,2,values.length,values[0].length).setValues(values);

1	sheet2.getRange(2,2,values.length,values[0].length).setValues(values);

コード5からコード7までのループ処理が全て終了したあと「.setValues(values); 」を使用して指定範囲に集計結果「values」の値を格納します。

この時、対象となるセル範囲は「sheet2.getRange(2,2,values.length,values[0].length)」となります。これは「シート2」の「2」行目、「2」列目から、下記に示す終了セルまでを指定しています。

終了セル
- 行：「values」の要素数「values.length」
- 列：「values」のインデックス「0」要素の配列「values[0]」の要素数「values[0].length)」

Google Apps Scriptをもっと勉強したい人へ

Google Apps Scriptで仕事を効率化したい人は以下の動画もご覧ください。

GASで出来ることって何を動画で解説

オススメの書籍を紹介

ぜひ興味を持った方は、Google Apps Scriptを勉強を始めてみてください

Googleスプレッドシートの仕事を早くしたいなら

Googleスプレッドシートの仕事を早くしたいなら、以下の動画もぜひご覧ください。

もしかすると、仕事を早くするヒントがあるかもしれません。

GoogleAppsScriptでデータ集計｜スプレッドシートの売上合計を算出（GAS）

この記事で紹介するプログラムで出来ることを動画で紹介

Google Apps Scriptの始め方を動画で解説

プログラムのコード全体像

プログラムの解説

準備1｜Moment.jsを使うためのライブラリキーで使用可能（*1）

ステップ1｜「リソース」→「ライブラリ」でライブラリウインドウを開く

ステップ2｜「Moment.js」のライブラリキーを追加する

コード0｜プロシージャ名

コード1｜スプレッドシートの設定

コード2｜最終行の取得

コード3｜計算結果格納用の配列

コード4｜データ取得

コード5｜シート2のデータ取得

コード6｜シート1とシート2のマッチング結果を取得

コード7｜マッチング結果を配列に格納

コード8｜マッチング結果をまとめてシート2に出力

Google Apps Scriptをもっと勉強したい人へ

GASで出来ることって何を動画で解説

オススメの書籍を紹介

Googleスプレッドシートの仕事を早くしたいなら

Googleスプレッドシートの時短ワザ12

Googleスプレッドシートのショートカットキー基礎24

人気記事

サイト運営情報

パソコンスキル

仕事術

この記事で紹介するプログラムで出来ることを動画で紹介

Google Apps Scriptの始め方を動画で解説

プログラムのコード全体像

プログラムの解説

準備1｜Moment.jsを使うためのライブラリキーで使用可能（*1）

ステップ1｜「リソース」→「ライブラリ」でライブラリウインドウを開く

ステップ2｜「Moment.js」のライブラリキーを追加する

コード0｜プロシージャ名

コード1｜スプレッドシートの設定

コード2｜最終行の取得

コード3｜計算結果格納用の配列

コード4｜データ取得

コード5｜シート2のデータ取得

コード6｜シート1とシート2のマッチング結果を取得

コード7｜マッチング結果を配列に格納

コード8｜マッチング結果をまとめてシート2に出力

Google Apps Scriptをもっと勉強したい人へ

GASで出来ることって何を動画で解説

オススメの書籍を紹介

Googleスプレッドシートの仕事を早くしたいなら

Googleスプレッドシートの時短ワザ12

Googleスプレッドシートのショートカットキー基礎24

Footer

人気記事

サイト運営情報

パソコンスキル

仕事術