Skip to content

Instantly share code, notes, and snippets.

@duynhm

duynhm/DrawOrgchartByGoogleChart.gs

Last active November 28, 2023 04:01
Show Gist options
  • Save duynhm/d677897b882bf440ef8406add95b3782 to your computer and use it in GitHub Desktop.
Save duynhm/d677897b882bf440ef8406add95b3782 to your computer and use it in GitHub Desktop.
Download ZIP
Draw Orgchart using google charts and custom data store in google sheet
Raw
DrawOrgchartByGoogleChart.gs
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.createMenu('OrgChart')
// .addItem('Dialog', 'openDialog')
.addItem('Dialog', 'openDialog2')
.addItem('Slidebar', 'openSlideBar')
.addItem('OrgChart by JobTitle', 'openDialog3')
.addToUi();
}
function openDialog2() {
var html = HtmlService.createHtmlOutputFromFile('index');
var htmlOutput = HtmlService
.createHtmlOutput(html)
.setWidth(1000)
.setHeight(800);
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, 'Org Chart');
// SpreadsheetApp.getUi().showSidebar(htmlOutput);
}
function openDialog3() {
var html = HtmlService.createHtmlOutputFromFile('indexJobTitle');
var htmlOutput = HtmlService
.createHtmlOutput(html)
.setWidth(1000)
.setHeight(800);
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, 'Org Chart');
// SpreadsheetApp.getUi().showSidebar(htmlOutput);
}
function openSlideBar() {
var html = HtmlService.createHtmlOutputFromFile('index');
var htmlOutput = HtmlService
.createHtmlOutput(html)
.setWidth(1000)
.setHeight(800);
SpreadsheetApp.getUi().showSidebar(htmlOutput);
}
function doGet() {
return HtmlService.createHtmlOutputFromFile('index');
}
function getStyle(status, name, vacant){
var text = '';
switch(status){
case 1: //manage out
if(name !=='')
text+='<div class="red">' + name + '</div>';
//if(vacant != 0)
//text+='<div class="blue">' + vacant + '</div>';
break;
case 2: //new
if(name !=='')
text+='<div>' + name + '</div>';
if(vacant != 0)
text+='<div class="blue">' + vacant + ' vacant</div>';
break;
case 3: //promote
if(name !=='')
text+='<div class="green">' + name + '</div>';
//if(vacant != 0)
//text+='<div class="blue">' + vacant + '</div>';
break;
default:
text+= '<div>' + name + '</div>';
if(vacant != 0)
text+='<div class="blue">' + vacant + ' vacant</div>';
}
return text;
}
//function getData(){
// if(type == "OrgChart")
// return getDataOrgChart();
// return getDataJobTitle();
//}
function getDataOrgChart(){
var sheet = SpreadsheetApp.getActive().getSheetByName("OrgChart");
//var count = sheet.getRange("A8").getValue();
var range = sheet.getRange("A2:F");
var dataRaw = range.getValues();
var orgChartValues = [];
var collabIndex = [];
//collabIndex
var i;
for(i = 0; i < dataRaw.length; i++)
{
var row = dataRaw[i];
var parent = row[5];
orgChartValues.push([{v:row[2],f:'<div class="blue">' + row[4] + '</div>' + row[1] + '<div class="bold">' + row[0] +'</div>' }, parent, row[10]]);
}
var allData = {};
allData.orgchart = {values: orgChartValues, index: collabIndex};
return allData;
}
function getDataJobTitle(){
var sheet = SpreadsheetApp.getActive().getSheetByName("OrgChartbyJobTitle");
//var count = sheet.getRange("A8").getValue();
var range = sheet.getRange("A2:F");
var dataRaw = range.getValues();
var orgChartValues = [];
var collabIndex = [];
//collabIndex
var i;
for(i = 0; i < dataRaw.length; i++)
{
var row = dataRaw[i];
var parent = row[5];
orgChartValues.push([{v:row[2],f:'<div class="blue">' + row[1] + '</div>' + row[3] + '<div class="bold">' + row[0] +'</div>' }, parent, row[10]]);
}
var allData = {};
allData.orgchart = {values: orgChartValues, index: collabIndex};
return allData;
}
function formatNum(num)
{
//var matches = format.match(/\.(0*?)%/);
//var fract = matches ? matches[1].length : 0; // Fractional part
return Utilities.formatString("%.2f%", 100*num);
}
Raw
index.html
<html>
<head>
<style>
div.red{
font-size: small;
font-style:italic;
font-weight:bold;
color: #f00;
width: auto;
}
div.green{
font-size: small;
font-style:italic;
font-weight:bold;
color: #00b050;
width: auto;
}
div.blue{
font-size: small;
font-style:italic;
font-weight:bold;
color: #00f;
width: auto;
}
div.bold{
font-size: small;
<!--color: #1aa;-->
width: auto;
font-weight:bold;
}
</style>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
var temp;
function refesh(){
google.script.run.withSuccessHandler(onSuccess).getDataOrgChart();//mặc định khi load lên gọi đến hàm getData() bên Code, nếu thành công gọi hàm onSuccess
}
function onSuccess(data) {
//drawChart(numUnread);
temp = data;
google.charts.load('current', {packages:["orgchart"]});
google.charts.setOnLoadCallback(drawChart);
google.charts.load('current', {packages:["table"]});
google.charts.setOnLoadCallback(drawTable);
}
google.script.run.withSuccessHandler(onSuccess).getDataOrgChart();//mặc định khi load lên gọi đến hàm getData() bên Code, nếu thành công gọi hàm onSuccess
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn('string', 'Name');
data.addColumn('string', 'Manager');
data.addColumn('string', 'ToolTip');
data.addRows(temp.orgchart.values);
temp.orgchart.index.forEach(function setProperty(index){
data.setRowProperty(index, 'style', 'background-color:#f1fca9;background-image:none');
});
// Create the chart.
var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));
// Draw the chart, setting the allowHtml option to true for the tooltips.
chart.draw(data, {allowHtml:true, allowCollapse:true});
}
function drawTable()
{
var dataTable = new google.visualization.DataTable();
dataTable.addColumn('string', 'Content');//content
dataTable.addColumn('number', 'Indirect');//number
dataTable.addColumn('number', 'Direct');//number
dataTable.addColumn('number', 'All');//number
dataTable.addRows(temp.table.values);
// dataTable.addRows([
// ["name", {v: 10000, f: '$10,000'}],
// ["name2", {v:8000, f: '$8,000'}],
// ["name3", {v: 12500, f: '$12,500'}],
// ["name4", {v: 7000, f: '$7,000'}]
// ]);
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(dataTable, {showRowNumber: false, /*width: '100%',*/ height: '100%'});
}
</script>
</head>
<body>
<div class="input-append">
<button type="button" onclick='refesh();' id="go">Refesh!</button>
</div>
<div id="chart_div"></div>
</body>
</html>
Raw
indexJobTitle.html
<html>
<head>
<style>
div.red{
font-size: small;
font-style:italic;
font-weight:bold;
color: #f00;
width: auto;
}
div.green{
font-size: small;
font-style:italic;
font-weight:bold;
color: #00b050;
width: auto;
}
div.blue{
font-size: small;
font-style:italic;
font-weight:bold;
color: #00f;
width: auto;
}
div.bold{
font-size: small;
<!--color: #1aa;-->
width: auto;
font-weight:bold;
}
</style>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
var temp;
function refesh(){
google.script.run.withSuccessHandler(onSuccess).getDataJobTitle();//mặc định khi load lên gọi đến hàm getData() bên Code, nếu thành công gọi hàm onSuccess
}
function onSuccess(data) {
//drawChart(numUnread);
temp = data;
google.charts.load('current', {packages:["orgchart"]});
google.charts.setOnLoadCallback(drawChart);
google.charts.load('current', {packages:["table"]});
google.charts.setOnLoadCallback(drawTable);
}
google.script.run.withSuccessHandler(onSuccess).getDataJobTitle();//mặc định khi load lên gọi đến hàm getData() bên Code, nếu thành công gọi hàm onSuccess
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn('string', 'Name');
data.addColumn('string', 'Manager');
data.addColumn('string', 'ToolTip');
data.addRows(temp.orgchart.values);
temp.orgchart.index.forEach(function setProperty(index){
data.setRowProperty(index, 'style', 'background-color:#f1fca9;background-image:none');
});
// Create the chart.
var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));
// Draw the chart, setting the allowHtml option to true for the tooltips.
chart.draw(data, {allowHtml:true, allowCollapse:true});
}
function drawTable()
{
var dataTable = new google.visualization.DataTable();
dataTable.addColumn('string', 'Content');//content
dataTable.addColumn('number', 'Indirect');//number
dataTable.addColumn('number', 'Direct');//number
dataTable.addColumn('number', 'All');//number
dataTable.addRows(temp.table.values);
// dataTable.addRows([
// ["name", {v: 10000, f: '$10,000'}],
// ["name2", {v:8000, f: '$8,000'}],
// ["name3", {v: 12500, f: '$12,500'}],
// ["name4", {v: 7000, f: '$7,000'}]
// ]);
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(dataTable, {showRowNumber: false, /*width: '100%',*/ height: '100%'});
}
</script>
</head>
<body>
<div class="input-append">
<button type="button" onclick='refesh();' id="go">Refesh!</button>
</div>
<div id="chart_div"></div>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment