trelloと連携する

GASとTrelloを連携してシート作った。
https://techblog.gmo-ap.jp/2019/12/05/trello_to_spreadsheet/

function main() {

  /////// 以下を設定 ///////
 
  // ボードのURLから取得できます
  var trelloBoard = "";
 
  // スプレッドシートのURLから取得できます
  var sheetId = "";
 
  // trelloKeyはこちらで取得できます→https://trello.com/app-key
  var trelloKey   = "";

  // https://trello.com/1/authorize?key=<上で取得したKey>&name=&expiration=never&response_type=token&scope=read,write
  var trelloToken = "";
 
  // Trelloの「プロフィールと公開範囲」に設定されている「ユーザー名」
  var userName = "";
 
  /////// ↑ここまで設定　///////

  setCards(trelloKey, trelloToken, trelloBoard, sheetId, userName); 
  setBoards(trelloKey, trelloToken, trelloBoard, sheetId, userName);
  setLists(trelloKey, trelloToken, trelloBoard, sheetId);
  setLabels(trelloKey, trelloToken, trelloBoard, sheetId);
  setCheckLists(trelloKey, trelloToken, trelloBoard, sheetId);
  setMembers(trelloKey, trelloToken, trelloBoard, sheetId);
}

function writeSheet(url, sheetId, sheetName, cardItem, isCards) {
  //　カード情報をjson形式で取得
  var cardRes = UrlFetchApp.fetch(url, {'method':'get'}); 
  var cardJson = JSON.parse(cardRes.getContentText());
  var startRow = 2;
  if (isCards) {
    startRow = 3;
  }

  // シート情報を取得
  var spreadSheet = SpreadsheetApp.openById(sheetId);
  var sheet = spreadSheet.getSheetByName(sheetName);

  // 見出し出力
  for(var i = 0; i < cardItem.length; i++) {
    var columnName = '';
    if (cardItem[i] == '') {
      continue;
    } else if (isCards && cardItem[i] == 'name') {
      columnName = 'カード'
    } else if (isCards && cardItem[i] == 'desc') {
      columnName = '内容'
    }　else {
      columnName = cardItem[i];
    }
   
    sheet.getRange(1,2+i).setValue(columnName);
  }

  // カード情報取得 
  for(var i = 0; i < cardJson.length; i++){
    var writeRow = 1;
    // id設定
    sheet.getRange(startRow + i,1).setValue((i+1));

    // 各カラム設定
    for(var j = 0; j < cardItem.length; j++) {
      writeRow++;
      if (cardItem[j] == '') {
        continue;
      }
      var getElement = cardJson[i][cardItem[j]];
      sheet.getRange(startRow + i,writeRow).setValue(getElement);
    }
  }
}

function setCards(trelloKey, trelloToken, trelloBoard, sheetId, sheetName, userName) {
  var sheetName = "trello";
  var cardItem = ['idBoard'
                  ,''
                  ,'idList'
                  ,''
                  ,'name'
                  ,'idLabels'
                  ,''
                  ,'desc'
                  ,'idChecklists'
                  ,''
                  ,'idMembers'
                  ,''
            ];         
                 
  //　カード情報をjson形式で取得
  var url = "https://trello.com/1/boards/"+trelloBoard+"/cards?key="+trelloKey+"&token="+trelloToken+"&fields=all";
  writeSheet(url, sheetId, sheetName, cardItem, true);
}

function setBoards(trelloKey, trelloToken, trelloBoard, sheetId, userName) {
  var sheetName = "boards";
  var cardItem = ['id','name'];
  var url = 'https://trello.com/1/members/' + userName + '/boards?key=' + trelloKey + '&token=' + trelloToken + '&fields=name';
  writeSheet(url, sheetId, sheetName, cardItem);
}

function setLists(trelloKey, trelloToken, trelloBoard, sheetId) {
  var sheetName = "lists";
  var cardItem = ['id','name'];
  var url = 'https://trello.com/1/boards/' + trelloBoard + '/lists?key=' + trelloKey + '&token=' + trelloToken + '&fields=name';
  writeSheet(url, sheetId, sheetName, cardItem);
}

function setLabels(trelloKey, trelloToken, trelloBoard, sheetId) {
  var sheetName = "labels";
  var cardItem = ['id','name'];
  var url = 'https://trello.com/1/boards/' + trelloBoard + '/labels?key=' + trelloKey + '&token=' + trelloToken + '&fields=name';
  writeSheet(url, sheetId, sheetName, cardItem);
}

function setCheckLists(trelloKey, trelloToken, trelloBoard, sheetId) {
  var sheetName = "checklists";
  var cardItem = ['id','name'];
  var url = 'https://trello.com/1/boards/' + trelloBoard + '/checklists?key=' + trelloKey + '&token=' + trelloToken + '&fields=name';
  writeSheet(url, sheetId, sheetName, cardItem);
}

function setMembers(trelloKey, trelloToken, trelloBoard, sheetId) {
  var sheetName = "members";
  var cardItem = ['id','fullName'];
  var url = 'https://trello.com/1/boards/' + trelloBoard + '/members?key=' + trelloKey + '&token=' + trelloToken + '&fields=fullName';
  writeSheet(url, sheetId, sheetName, cardItem);
}