QualityForwardとGoogleスプレッドシートでテスト結果を可視化するサンプルコード

code.js

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data")
  var apiKey = sheet.getRange(1, 2).getValue();
  var qf = new QualityForward.Client(apiKey);
  
  var resultSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TestResult")
  var today = new Date;
  var testPhaseId = sheet.getRange(2, 2).getValue();;
  var testSuiteAssignmentId = sheet.getRange(3, 2).getValue();;
  var testCycleId = sheet.getRange(4, 2).getValue();;
  
  
  var testPhase = qf.getTestPhases().filter(t => t.id === testPhaseId)[0];
  var testSuiteAssignment = testPhase.test_suite_assignments.filter(t => t.id === testSuiteAssignmentId)[0];
  var testCycle = testSuiteAssignment.getTestCycles().filter(t => t.id === testCycleId)[0];
  var results = testCycle.getTestResults();
  var res = {};
  for (let l in results) {
    const testResult = results[l];
    const d = testResult.executed_at;
    const date = `${d.getFullYear()}/${(d.getMonth() + 1)}/${d.getDate()}`;
    if (!res[date]) res[date] = {};
    if (!res[date][testResult.result]) res[date][testResult.result] = 0;
    res[date][testResult.result]++;
  }
  
  let d = new Date(testCycle.start_on);
  const endDate = new Date(testCycle.end_on);
  let lastRow = 2;
  
  // 残タスク数
  sheet.getRange(5, 2).setValue(testCycle.target_test_case_no_list.length);
  // 日数
  let dateCount = parseInt((endDate - d) / 86400000);
  
  while (d <= endDate) {
    const date = `${d.getFullYear()}/${(d.getMonth() + 1)}/${d.getDate()}`;
    resultSheet.getRange(lastRow, 1).setValue(date);
    if (!res[date]) res[date] = {};
    resultSheet.getRange(lastRow, 2).setValue(res[date].pass || 0);
    resultSheet.getRange(lastRow, 3).setValue(res[date].fail || 0);
    resultSheet.getRange(lastRow, 4).setValue(res[date].skip || 0);
    resultSheet.getRange(lastRow, 5).setValue(res[date].cut || 0);
    resultSheet.getRange(lastRow, 6).setValue(res[date].block || 0);
    resultSheet.getRange(lastRow, 7).setValue(res[date].na || 0);
    resultSheet.getRange(lastRow, 8).setValue(res[date].qa || 0);      
    // Fomula
    resultSheet.getRange(lastRow, 9).setValue(`=IFERROR(VLOOKUP(A${lastRow},'休祭日'!A:C, 3, false), "")`);
    resultSheet.getRange(lastRow, 10).setValue(`=SUM(B${lastRow}:H${lastRow})`);
    if (lastRow === 2) {
      resultSheet.getRange(lastRow, 11).setValue(`=Data!B5-J2`);
      resultSheet.getRange(lastRow, 12).setValue(`=B2`);
      resultSheet.getRange(lastRow, 13).setValue(`=C2`);
      resultSheet.getRange(lastRow, 14).setValue(`=D2`);
      resultSheet.getRange(lastRow, 15).setValue(`=E2`);
      resultSheet.getRange(lastRow, 16).setValue(`=F2`);
      resultSheet.getRange(lastRow, 17).setValue(`=G2`);
      resultSheet.getRange(lastRow, 18).setValue(`=H2`);
      
    } else {
      resultSheet.getRange(lastRow, 11).setValue(`=K${lastRow - 1}-J${lastRow}`);
      
      // total count each result
      resultSheet.getRange(lastRow, 12).setValue(`=B${lastRow}+L${lastRow - 1}`);
      resultSheet.getRange(lastRow, 13).setValue(`=C${lastRow}+M${lastRow - 1}`);
      resultSheet.getRange(lastRow, 14).setValue(`=D${lastRow}+N${lastRow - 1}`);
      resultSheet.getRange(lastRow, 15).setValue(`=E${lastRow}+O${lastRow - 1}`);
      resultSheet.getRange(lastRow, 16).setValue(`=F${lastRow}+P${lastRow - 1}`);
      resultSheet.getRange(lastRow, 17).setValue(`=G${lastRow}+Q${lastRow - 1}`);
      resultSheet.getRange(lastRow, 18).setValue(`=H${lastRow}+R${lastRow - 1}`);
    }
    
    // total task
    resultSheet.getRange(lastRow, 19).setValue(`=SUM(L${lastRow}:R${lastRow})`);
    
    // 残り営業日
    if (lastRow + 1 > dateCount + 2) {
      // 最後の行
      resultSheet.getRange(lastRow, 20).setValue(0);
    } else {
      resultSheet.getRange(lastRow, 20).setValue(`=countif(I${lastRow + 1}:I${dateCount + 2}, "")`);
    }
    resultSheet.getRange(lastRow, 21).setValue(`=IFERROR(K${lastRow}/T${lastRow}, 0)`);
    
    if (toDay(date)) {
      resultSheet.getRange(lastRow, 1, lastRow, 21).setBackgroundRGB(240, 180, 180);
    } else {
      resultSheet.getRange(lastRow, 1, lastRow, 21).setBackgroundRGB(255, 255, 255);
    }
    
    d.setDate(d.getDate() + 1);
    lastRow++;
  }
  
}

function toDay(date) {
  const d = new Date();
  const str = `${d.getFullYear()}/${(d.getMonth() + 1)}/${d.getDate()}`;
  return str === date;
}

function holiday_main() {
  // 今年の1/1から
  var startDate = new Date();
  startDate.setMonth(0, 1);
  startDate.setHours(0, 0, 0, 0);

  // 来年の12/31まで
  var endDate = new Date();
  endDate.setFullYear(endDate.getFullYear() + 1, 11, 31);
  endDate.setHours(0, 0, 0, 0);  

  var sheet = getholidaysheet();
  var holidays = getHoliday(startDate, endDate);

  var lastRow = sheet.getLastRow();
  var startRow = 1;

  // シートが空白で無いとき、取得した祝日配列の先頭の日付と一致するカラムの位置を探索
  sheet.clear();

  sheet.getRange(startRow, 1, holidays.length, holidays[0].length).setValues(holidays);
}

/**
 * SHEET_URLで指定したドキュメント内のSHEET_NAMEのシートを取得する
 * SHEET_NAMEのシートが存在しない時は、シートを作成する
 */
function getholidaysheet(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('休祭日');

  if(sheet == null) {
    sheet = ss.insertSheet('休祭日');
  }

  return sheet;
}

/**
 * startDate〜endDateまでの祝日をgoogleカレンダーの「日本の祝日」から取得
 * [日付,祝日名]の多次元配列にした上で返す
 */
function getHoliday(startDate, endDate) {
  var cal = CalendarApp.getCalendarById("ja.japanese#[email protected]");

  var holidays = cal.getEvents(startDate, endDate);
  var values = [];

  for(var i = 0; i < holidays.length; i++) {
    values.push([holidays[i].getStartTime(), holidays[i].getTitle()]);
  }
  
  let d;
  d = startDate;
  while (d <= endDate) {
    if ([0, 6].indexOf(d.getDay()) > -1) {
      values.push([new Date(d.getTime()), "休日"]);
    }
    Logger.log(d);
    d.setDate(d.getDate() + 1);
  }
  values.sort((a, b) => {
    return a[0] > b[0] ? 1 : -1;
  });
  return values;
}