同一のカラム構成で複数のスプレッドシートがひとつのフォルダに入っていて、
それらをひとつのシートにまとめたくて書いたコードです。
例として以下のような２つのスプレッドシートがあって
１つ目のスプレッドシートのシート1（edit#gid=0）
２つ目のスプレッドシートのシート1（edit#gid=0）
その２つのシートをまとめてこういう３つ目のシートを作りたい
やりたいことを簡単に言うと
１つ目のシートと２つ目のシートの２行目から最終行までのデータを取得して
３つ目のシートにまとめる
そして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;
}
}
|
関連記事
シートの１行目（ヘッダ行）の値を取得する
ヘッダの文字列を用意してシートの一行目に自動で入力したい
setValuesを使う話
Googleドライブ内のフォルダをフォルダ名で並べ替えて取得したい
シートのデータを昇順・降順でソートする
