【GAS】共通列を持つ複数のシートの統合

2022年8月14日GASmatch,setValues

店舗の売り上げ情報など、定型フォーマットの月次データを毎月あげたりもらったりすること多いですよね。共通列を持っている提携フォーマットの複数のシートを自動で結合してくれるコードを書いてみました。

1. 元データと目標となるアウトプット
2. やることを整理
3. コードを書いていく

元データと目標となるアウトプット

というわけで早速やっていきましょう。

まずダミーデータをつります。ここでは月ごとに店舗の売上集計データを結合したいことにしましょう。

// 売上データ_2020年6月
年月	店舗名	店舗ID	従業員数	売上	来店客数
2020年6月	A	332	6	265372	33
2020年6月	B	383	33	265427	33
2020年6月	C	488	27	331522	21
2020年6月	D	979	1	110193	2

// 売上データ_2020年7月
年月	店舗名	店舗ID	従業員数	売上	来店客数
2020年7月	A	878	33	268141	7
2020年7月	B	670	23	396982	1
2020年7月	C	889	33	312252	13
2020年7月	D	893	13	298151	32

// 売上データ_2020年8月
年月	店舗名	店舗ID	従業員数	売上	来店客数
2020年8月	A	481	36	195515	15
2020年8月	B	470	6	208665	11
2020年8月	C	175	40	154143	13
2020年8月	D	293	38	19102	19

数値は乱数で発生させた超適当架空データです。同じデータで練習したい方は、Gドライブの同じフォルダ内に3か月分のデータを入れておいてください。（名前は「売り上げ_○年○月」にしておくと、後のスクリプトをそのまま使えます。

やることを整理

今回データ元がシート3つなので全部シートIDや名前を指定して一個ずつ取得してくることもできるのですが、同じフォルダ内のスプレッドシートを全部取得して全自動で統合できるようにしましょう。

①指定フォルダ内のスプレッドシートのデータを取得する

（1）フォルダ内の対象となるスプレッドシートのIDを取得する。

参考："Gドライブの同じフォルダに入っている特定条件のシートデータを取得する。"

（2）取得したIDからデータを読み込む。

②同じ配列のデータを統合する

③新しいシートに書き出す

コードを書いていく

//インポートデータのfolderIDを指定
const import_folder_id="インポートとするデータが入っているフォルダのID";

//エクスポートするシートIDを指定
const export_file_id="エクスポートするシートのID";


function myFunction(){

  //指定folder内のデータ元となるスプレッドシートのシート名、シートID、URLを取得
  const spsheets = get_Spsheets_by_Name(import_folder_id);

  //統合して出力
  set_spSheets_Id(spsheets,export_file_id);
}



function get_Spsheets_by_Name(import_folder_id){

  //id指定でフォルダをオブジェクトとして指定
  const folder = DriveApp.getFolderById(import_folder_id);

  //指定したフォルダ内のすべてのファイル
  const files = folder.getFiles();

  //
  const arr = [];

  //2次元配列として追加
  while (files.hasNext()) {

    //すべてのファイルから１つ取り出し
    const file = files.next();
  
      //「売上」が名前に入っているシートのみデータを取得
      if (file.getName().match(/売上/)) {
        //配列にファイルのデータを追加
        //getName：ファイルの名前、getId：ファイルのID、getUrl:ファイルのURL
        arr.push([file.getName(),file.getId(),file.getUrl()]);

    }
  }

  return arr;

}


function set_spSheets_Id(arr,ex_id){

 //エクスポート先のシートのデータをクリア
 SpreadsheetApp.openById(ex_id).getSheets()[0].clear();

  var n=0;

    for(var i=0; i < arr.length; i++){

      //sheet idの読み込み
      var sheet_id =arr[i][1];
      //一応出力
      console.log(sheet_id);

      values = set_values(sheet_id,n);

      export_data(values,ex_id);

      //読み込み回数追加、nが0以外は列削除
      n++;
    }
}


//記述されているところの次の行から入れる
function set_values(im_id,n){
  var import_spredsheet = SpreadsheetApp.openById(im_id);
  var sheet = import_spredsheet.getSheets()[0];
  var values = sheet.getDataRange().getValues(); 

  //最初以外は一行名（列名）を削除
    if(n==0){
      //1回目は何もしない
    }else{
      //2回目以降は先頭行の削除して追加
      values.shift();
    }
  
  return values;
}

 function export_data(arr, ex_id){
  //エクスポートするシートの読み込み
  var export_spredsheet = SpreadsheetApp.openById(ex_id);
  var sheet = export_spredsheet.getSheets()[0];

 //記述開始場所の定義
  var last_row = export_spredsheet.getLastRow();
  var start_row = last_row + 1;
  var start_col = 1;

  var num_rows = arr.length;
  var num_cols = arr[0].length;

　// 記述エリア
  var range = sheet.getRange(start_row, start_col, num_rows, num_cols);

  // 記述
  range.setValues(arr); 
 }

はい、出来ました。

//エクスポート先のシート

年月	店舗名	店舗ID	従業員数	売上	来店客数
2020/06/01	A	332	6	265372	33
2020/06/01	B	383	33	265427	33
2020/06/01	C	488	27	331522	21
2020/06/01	D	979	1	110193	2
2020/07/01	A	878	33	268141	7
2020/07/01	B	670	23	396982	1
2020/07/01	C	889	33	312252	13
2020/07/01	D	893	13	298151	32
2020/08/01	A	481	36	195515	15
2020/08/01	B	470	6	208665	11
2020/08/01	C	175	40	154143	13
2020/08/01	D	293	38	19102	19