특정 열 값을 기준으로 범위를 새 통합 문서의 여러 탭에 복사

올바르게 이해했다면 해당 데이터가 포함 된 세 개의 시트 (Open, Hold 및 Future)가 포함 된 새 스프레드 시트를 원합니다.

이것이 내가 시도한 것입니다.

function createSpreadsheets() {

  var StatusInternal = ["Open","Hold","Future"];

  //Create the new file
  var newSprsheet = SpreadsheetApp.create("Status Internal");

  for (var i = 0; i < StatusInternal.length; i++){
    //Create the new Sheets with the Status Internal names
    newSprsheet.insertSheet(StatusInternal[i]);
  }
  //Activates the first sheet (Sheet1), to delete it, as it is not necessary
  newSprsheet.getSheets()[0].activate();
  newSprsheet.deleteActiveSheet();

  splitData(newSprsheet, StatusInternal);


}

function splitData(newSprsheet, StatusInternal){

  var mainSprsheet = SpreadsheetApp.openById("Id of your main Spreadsheet");
  var mainSheet = mainSprsheet.getActiveSheet();

  //Gets the values of the first row
  var headcolumns = mainSheet.getRange("A1:L1").getValues(); 
  //Gets the values of the Status Internal Column
  var StatusValues = mainSheet.getRange("C2:C").getValues();

  var lastRow = mainSheet.getLastRow();

  for (var i = 0; i < 3; i++){
    //Copies the first row values to each new Sheet
    newSprsheet.getSheets()[i].appendRow(headcolumns[0]);
  }

  for (var i = 2; i <= lastRow; i++){
    //Gets all the values of the row
    var rowContents = mainSheet.getRange("A"+i+":L"+i).getValues();
    //Appends a new row to the Sheet depending of the value in Column C.
    switch(StatusValues[i-2].toString()){

      case StatusInternal[0]:
        newSprsheet.getSheetByName(StatusInternal[0]).appendRow(rowContents[0]);
        break;
      case StatusInternal[1]:
        newSprsheet.getSheetByName(StatusInternal[1]).appendRow(rowContents[0]);
        break;
      case StatusInternal[2]:
        newSprsheet.getSheetByName(StatusInternal[2]).appendRow(rowContents[0]);
        break;
    }

  }
}

참조 :

• 범위 등급
• AppendRow 메서드
• SpreadSheet 클래스