Apps Script公式リファレンス: Apps Script Reference |障害・課題追跡: IssueTracker |Google Workspace: Status Dashboard - Summary

2019年6月23日日曜日

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


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

上記2つで書いたコードをマージして値とノートを一箇所に集めるコード



コード.gs
var folderId = 'FOLDER_ID';// 複数のシートが入っているフォルダID
var spreadsheets = getFiles(folderId);
var masterSheetUrl = 'SPREADSHEET_URL';// まとめるスプレッドシートのID
var noteCol = 'E';// E列

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

/************************************
対象のスプレッドシートのIDを取得して返す
************************************/
function getSsIds(spreadsheets) {
  var ssIds = [];
  for(var i = 0; i < spreadsheets.length; i++) {
    ssIds.push(spreadsheets[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 getSheetNotes(ssId) {
  var ss = SpreadsheetApp.openById(ssId);
  var sheet = ss.getSheets()[0];
  var startRow = 2;
  var lastRow = sheet.getLastRow();
  var notes = sheet.getRange(noteCol + startRow + ':' + noteCol + lastRow).getNotes();
  return notes;
}

/************************************
シートにノートを一気に入力する
************************************/
function setSheetNotes(sheet, array){
  var lastRow = getLastNoteRow(sheet, noteCol);
  var startRow = lastRow + 1;
  var numRows = lastRow + array.length;
  var range = sheet.getRange(noteCol + startRow + ':' + noteCol + numRows);
  range.setNotes(array);
}

/************************************
入力されているノートの最終行を取得して返す
************************************/
function getLastNoteRow(sheet, col) {
  var lastCols = sheet.getLastColumn();
  var notes = sheet.getRange(col + ":" + col).getNotes();
  for (var i = notes.length - 1; i >= 0; i--) {
    if (notes[i] != "") {
      break;
    }
  }
  var lastNoteRow = i + 1;
  return lastNoteRow;
}

/************************************
フォルダ内のファイルを取得して
昇順にして返す
************************************/
function getFiles(folderId) {
  var fileProps = [];
  var folder = DriveApp.getFolderById(folderId);
  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)
複数のスプレッドシートのノートをひとつにまとめたい(シート1)

Latest post

Google Apps Scriptの障害時はIssueTrackerを見てみる - Incidents for Apps Script are reported on Issue Tracker

IssueTracker > Apps Script issues https://issuetracker.google.com/savedsearches/566234 Google Apps Scriptの障害時は IssueTracker に課題が上がっていることが...