LANG SELRCT

コードを書く場所

2019年6月22日土曜日

複数のスプレッドシートをひとつにまとめたい(シート1)


同一のカラム構成で複数のスプレッドシートがひとつのフォルダに入っていて、
それらをひとつのシートにまとめたくて書いたコードです。



例として以下のような2つのスプレッドシートがあって


1つ目のスプレッドシートのシート1(edit#gid=0)
ABC
1IDTITLEDATE
2axxxxxxTITLE-1DATE-1
3bxxxxxxTITLE-2DATE-2
4cxxxxxxTITLE-3DATE-3
5dxxxxxxTITLE-4DATE-4
6exxxxxxTITLE-5DATE-5


2つ目のスプレッドシートのシート1(edit#gid=0)
ABC
1IDTITLEDATE
2fxxxxxxTITLE-6DATE-6
3gxxxxxxTITLE-7DATE-7
4hxxxxxxTITLE-8DATE-8
5ixxxxxxTITLE-9DATE-9
6jxxxxxxTITLE-10DATE-10
7kxxxxxxTITLE-11DATE-11
8lxxxxxxTITLE-12DATE-12



その2つのシートをまとめてこういう3つ目のシートを作りたい
ABC
1IDTITLEDATE
2axxxxxxTITLE-1DATE-1
3bxxxxxxTITLE-2DATE-2
4cxxxxxxTITLE-3DATE-3
5dxxxxxxTITLE-4DATE-4
6exxxxxxTITLE-5DATE-5
7fxxxxxxTITLE-6DATE-6
8gxxxxxxTITLE-7DATE-7
9hxxxxxxTITLE-8DATE-8
10ixxxxxxTITLE-9DATE-9
11jxxxxxxTITLE-10DATE-10
12kxxxxxxTITLE-11DATE-11
13lxxxxxxTITLE-12DATE-12


やりたいことを簡単に言うと
1つ目のシートと2つ目のシートの2行目から最終行までのデータを取得して
3つ目のシートにまとめる
そして1列目を昇順で並べ替える



FOLDER_ID と SPREADSHEET_URL を設定して
runGetAndSetValues() を実行する

コード.gs
var targetFolderId = 'FOLDER_ID';// 複数のシートが入っているフォルダID
var masterSheetUrl = 'SPREADSHEET_URL';// まとめるスプレッドシートのURL

var targetSpreadsheets = getFiles(targetFolderId);

/************************************
これを実行する
************************************/
function runGetAndSetValues() {
  var ssIds = getSsIds(targetSpreadsheets);
  setHeaderValues(ssIds);
  for(var i = 0; i < ssIds.length; i++) {
    var sheetValues = getSheetValues(ssIds[i]);
    var sheet = SpreadsheetApp.openByUrl(masterSheetUrl).getSheets()[0];
    setSheetValues(sheet, sheetValues);
  }
  sort(sheet);
}

/************************************
一行目に見出しの値を入れる
************************************/
function setHeaderValues(ssIds) {
  var headerValues = getHeaderValues(ssIds);
  Logger.log(headerValues)
  var ss = SpreadsheetApp.openByUrl(masterSheetUrl);
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange(1, 1, 1, headerValues[0].length);
  range.setValues(headerValues);  
}

/************************************
一行目に入れる見出しの値を取得する
************************************/
function getHeaderValues(ssIds) {
  var ssId = ssIds[0];
  var ss = SpreadsheetApp.openById(ssId);
  var sheet = ss.getSheets()[0];
  var last_col = sheet.getLastColumn();
  var range = sheet.getRange(1, 1, 1, last_col);
  var headerValues = range.getValues();
  return headerValues;
}

/************************************
対象のスプレッドシートのIDを取得して返す
************************************/
function getSsIds(targetSpreadsheets) {
  var ssIds = [];
  for(var i = 0; i < targetSpreadsheets.length; i++) {
    ssIds.push(targetSpreadsheets[i]['id']);
  }
  return ssIds;
}

/************************************
シートの値を取得して返す
************************************/
function getSheetValues(ssId) {
  var ss = SpreadsheetApp.openById(ssId);
  var sheet = ss.getSheets()[0];
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var range = sheet.getRange(2, 1, lastRow, lastCol);// 一行目はヘッダなので2行目から取得する
  var values = range.getValues();
  return values;
}

/************************************
シートに値を一気に入力する
************************************/
function setSheetValues(sheet, array){
  var lastRow = sheet.getLastRow();
  var startRow = lastRow + 1;
  var startCol = 1;
  var numRows = array.length;
  var numCols = array[0].length;
  var range = sheet.getRange(startRow, startCol, numRows, numCols);
  range.setValues(array); 
}

function sort(sheet){  
  var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
  range.sort([{column: 1, ascending: true}]);
}

/************************************
フォルダ内のファイルを取得して
昇順にして返す
************************************/
function getFiles(targetFolderId) {
  var fileProps = [];
  var folder = DriveApp.getFolderById(targetFolderId);
  var contents = folder.getFiles();
  while(contents.hasNext()) {
    var file = contents.next();
    var name = file.getName();
    var id = file.getId();
    var obj = {};
    obj["name"] = name;
    obj["id"] = id;
    fileProps.push(obj);
  }
  var sorted = objSort(fileProps);
  return fileProps;
}

/************************************
並べ替える
************************************/
function objSort(fileProps) {
  var ascending = fileProps.sort(asc);
  return ascending;
}

/************************************
nameで昇順に並べ替える
************************************/
function asc(a, b){
  var A = a.name;
  var B = b.name;
  if(A > B){
    return 1;
  }else if(A < B ){
    return -1;
  }else{
   return 0;
  }
}



関連記事

シートの1行目(ヘッダ行)の値を取得する
ヘッダの文字列を用意してシートの一行目に自動で入力したい
setValuesを使う話
Googleドライブ内のフォルダをフォルダ名で並べ替えて取得したい
シートのデータを昇順・降順でソートする