backlog-burnup.gs

function myFunction() {
  var issues = [];
  var offset = 0;
  do {
    var fetchedIssues = fetchIssues(offset);
    Array.prototype.push.apply(issues, fetchedIssues);
    offset += 100;
  } while (fetchedIssues.length == 100);
  
  writeHeader();
  
  for (var i = 0; i < issues.length; i++) {
    var issue = issues[i];
    write(i + 2, issue);
  }
}

function fetchIssues(offset) {
  const PJ_ID = '';
  const API_KEY = '';
  const MILESTONE_ID = '';
  
  const response = UrlFetchApp.fetch("https://******.backlog.jp/api/v2/issues?apiKey=" + API_KEY + "&projectId[]=" + PJ_ID + "&milestoneId[]=" + MILESTONE_ID + "&sort=created&order=desc&count=100&offset=" + offset);
  
  if (response.getResponseCode() != 200) {
    throw new Error()
  }
  
  return JSON.parse(response.getContentText());
}

function write(row, issue) {
  const issueKey = issue.issueKey;
  const summary = issue.summary;
  const status = issue.status.name;
  const assignee = issue.assignee.name;
  const created = issue.created != null ? new Date(issue.created) : null;
  const dueDate = issue.dueDate != null ? new Date(issue.dueDate) : null;
  const dueWeek = issue.dueDate != null ? calcMonday(new Date(issue.dueDate)) : null;
  const estimatedHours = issue.estimatedHours;
  const actualHours = issue.actualHours;
  
  const startRow  = row,
      startColumn = 1,
      numOfRow    = 1,
      numOfColumn = 9;
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(startRow, startColumn, numOfRow, numOfColumn).setValues([ [ issueKey, summary, status, assignee, created, dueDate, dueWeek, estimatedHours, actualHours ] ]);
}

function writeHeader(row, issue) {
  const issueKey = '課題ID';
  const summary = 'タイトル';
  const status = '状態';
  const assignee = '担当者';
  const created = '作成日時';
  const dueDate = '完了日時';
  const dueWeek = '完了週';
  const estimatedHours = '予定時間';
  const actualHours = '実績時間';
  
  const startRow  = 1,
      startColumn = 1,
      numOfRow    = 1,
      numOfColumn = 9;
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(startRow, startColumn, numOfRow, numOfColumn).setValues([ [ issueKey, summary, status, assignee, created, dueDate, dueWeek, estimatedHours, actualHours ] ]);
}

function calcMonday(date) {
  const newDate = new Date(date.getTime());
  newDate.setDate(newDate.getDate() - (newDate.getDay() - 1));
  return newDate;
}