Notionを使って業務を効率化(2):GoogleスプレッドシートのデータをNotionで作図する方法

今回利用したスクリプトのコードになります。動画の中で使い方を説明していますのでぜひご活用ください。

function drawGraph() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();

  var pert = "flowchart LR\n";
  var gantt = "gantt\n";
  var back = {};
  for (var i=2; i<=lastRow; i++) {
    var number  = sheet.getRange(i,1).getValue();
    var label   = sheet.getRange(i,2).getValue();
    var minutes = sheet.getRange(i,3).getValue();
    var next    = sheet.getRange(i,4).getDisplayValue();
    var next_numbers = [];

    if(next.match(/,/)) {
      next_numbers = next.split(",");
    } else if(next !== "") {
      next_numbers[0] = next;
    }

    for(var j=0;j<next_numbers.length;j++) {
      var next_number = next_numbers[j];
      if("T"+next_number in back) {
        back["T"+next_number] = back["T"+next_number] + " T" + number; 
      } else {
        back["T"+next_number] = "T" + number; 
      }
    } 
    if(minutes !== "") {
      for(var j=0;j<next_numbers.length;j++) {
          pert = pert + "\tT" + number + "((" + label + "))";
          pert = pert + " -- " + minutes + "分 -->T" + next_numbers[j] + "\n";
      }

      if("T"+number in back) {
        gantt = gantt + "\t" + label + " :T" + number + ", after " + back["T"+number] + ", " + minutes + "m\n";
      } else {
        gantt = gantt + "\t" + label + " :T" + number + ", 2022/07/01, " + minutes + "m\n";
      }
    } else {
      pert = pert + "\tT" + number + "((" + label + "))\n";
    }
  }

  var body1 = {
    code:{
      rich_text: [{
        type: "text",
        text: {
          content: pert
        }
      }],
      language: "mermaid"
    }
  };

  var notion_pert = notionAPI(
    "https://api.notion.com/v1/blocks/<<PERT BLOCK ID>>",
    "PATCH",
    JSON.stringify(body1)
  );

  var body2 = {
    code:{
      rich_text: [{
        type: "text",
        text: {
          content: gantt
        }
      }],
      language: "mermaid"
    }
  };
  var notion_pert = notionAPI(
    "https://api.notion.com/v1/blocks/<<GANTT BLOCK ID>>",
    "PATCH",
    JSON.stringify(body2)
  );
}

function notionAPI(url, method, payload) {
  var accessToken = '<<TOKEN>>';
  var headers = {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer '+ accessToken,
    "Notion-Version": "2022-06-28"
};
  var options = {
    'method': method,    
    'headers': headers,  
    'payload': payload  
  };

  var response = UrlFetchApp.fetch(url, options);
  return response;
}

インストールがうまくいかない！などにつきましては以下のサイトからお気軽にお問い合わせください。

Lightcraft Inc.