Dashboard

Het gebruik van een dashboard laat je toe om op een interactieve manier de data te presenteren op verschillende wijzes en dat door gebruik te maken van dezelfde data.
In het volgende voorbeeld zie je een pie chart en een datatabel, data die komt uit een Sheet en via een webapp wordt getoond.

De controls kunnen gebruikt worden om de chart en/of tabel aan te passen, zodat je een filter kan toepassen op de getoonde waardes.

De stappen voor het maken van zulk een dashboard zijn verandert sinds de afschaffing van de UiApp API en dienen nu gemaakt te worden met de HtmlService.

Info

Building a Charts Dashboard with Google Apps Script

Web App w/ Google Charts, Google Sheets & Apps Script

Convert from Uiapp to Htmlservice Dashboard controls

Vizualisation grammer Vega charts

Concept

In dit concept splitsen we het project op in verschillende onderdelen waardoor het lagenmodel gerespecteerd blijft.

Data layer

We gebruiken een Google Sheet om als data layer te dienen.
Door gebruik te maken van Apps Script, kan je op deze wijze vanuit de sheet de app maken en data bewerken op verschillende niveau's

Business layer

Deze laag bevat de bewerkingen die er dienen te gebeuren.
Vermits de bewerkingen zowel op de sheet (Apps Script) als in de webpagina(Javascript) dienen aangepakt te worden, zal je hier meerdere stukken terug vinden.
Het voordeel van Javascript is dat de taal nagenoeg identiek is voor de programmatie op Apps Script en in een webpagina.

Presentation layer

De presentation layer, vormt de website, die via een webapp wordt aangeboden.
Ook hierin gaan we een splitsing doorvoeren en het gebruik van CSS toepassen in Apps Script, iets wat niet default wordt gebruikt.
Hierdoor kan je de aanpak van je probleem in stukken oplossen, zonder telkens alles te moeten herzien.

Stappenplan

Data layer

De datalayer is een spreadsheet met eventueel verschillende sheets.

Hoe deze gegevens in de sheet terecht komen is onbelangrijk.
Dit kan dmv een form of berekeningen of een combinatie van beide.

De sheet is steeds opgebouwd met kolom headers en de data binnen één kolom(zonder de header) moet van hetzelfde type zijn.

Vermits de App wordt gemaakt in de Apps Script, gebonden aan een sheet is het niet nodig om de sheet te "publiceren" of publiek open te zetten.

Koppelingen naar data die "vreemd" is aan de app, maken het vergaren van de data complexer(AOuth), maar laten je toe om dmv jquery sQL achtige queries uit te voeren op een externe database.

Businness layer op Apps Script

doGet()/doPut()

Een Webapp vereist steeds een doGet in Apps Script.
Deze functie creëert een webpagina aan de hand van een html file "Index" en zal aangeroepen worden bij het laden van de webapp.
Wanneer je ook via de URL data/cmd wil doorsturen naar de backend, dien je de functie doPut() te maken(zie later)

function doGet() {

var template = HtmlService.createTemplateFromFile('Index')

return template.evaluate()

.setTitle('WSPRchart')

.setSandboxMode(HtmlService.SandboxMode.IFRAME)

}

getSpreadsheetData()

In deze functie, die aangeroepen wordt na het laden van de webpagina, wordt de data klaar gemaakt en doorgegeven aan de javascript functies van de webpagina.
Door gebruik te maken van de getValues() maak je een 2D array aan, het formaat wat ook nodig is om nadien in de website de data te kunnen gebruiken.

function getSpreadsheetData() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calc2")

var data = ss.getDataRange().getValues();

return (data.length > 0) ? data : null

}

Presentation layer

De presentation layer bestaat uit een aantal onderdelen, die samen de webpagina vormen:

Index.html, welke de hoofdpagina vormt
Stylesheet.html, welkde de stijlen definieerd
JavaScript.html, waarin we de JS code vinden

Index.html

In deze file gaan we bovenaan de Stylesheet binnen halen, die in een aparte html file steekt met naam Stylesheet.html:
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>
In de body definieren we de grove layout van het dashboard.
We maken hier gebruik van een CSS grid Property waarbij we de pagina indelen in een aantal kolommen waarin we verschillende items kunnen plaatsen.
In ons voorbeeld plaatsen we telkens één onderdeel per item.
Als laatste onderdeel importeren we het javascript gedeelte, eveneens terug te vinden in een aparte html file met naam JavaScript.html.
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<!DOCTYPE html>

<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<html>

<head>

</head>

<body>

<div id="main-heading">Charts...

</div>

</div>

</div>

</div>

</div>

</div>

</div>

</div>

</div>

</body>

</html>

<!-- Store data passed to template here, so it is available to the

imported JavaScript. -->

</script>

<?!= HtmlService.createHtmlOutputFromFile('JavaScript').getContent(); ?>

2. Stylesheet.html

Deze stylesheet maakt gebruik van Google styling die je kan terug vinden in de css add-ons1.css file.
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
Je kan deze dus best vooraan inladen.

Tevens laden we hier een stylesheet in van de bibliotheek voor het tekenen van tabellen (zie later Ingave velden )

Verder kan je de styles aanpassen, waardoor je layout en uitzicht verandert, zonder in de index pagina wat te hoeven aan te passen.

Het spreekt voor zich dat de verwijzingen moeten overeen komen met deze in de index pagina.

Vermits we gebruik maken van een CSS grid Property, definiëren we eerst de grid area's.
In ons voorbeeld hebben we 10 verschillende velden voorzien.

.item1 { grid-area: header; }

.item2 { grid-area: info; }

...

Eens de grid items gedefineerd zijn kan je de layout bepalen .
We gebruiken hier een vaste kolom breedte van 30% van de totale beeldschermbreedte, dus 3 kolommen (totaal <=100%)
display: grid;

grid-auto-columns: 30%;
Dmv de grid-template-areas: definieer je per rij welke items waar komen en hoeveel kolommen deze zullen innemen.
grid-template-areas:

'header info info'

'settings settings settings'

'slider select select'

'chart radar radar'

'error error hidden'

'table table table'

;

In het voorgaande voorbeeld zie dat op de eerste rij, de eerste kolom gebruikt wordt voor het header item(item1), waardat op rij 2 de ganse rij gebruikt wordt voor het settings item(item3), door dit 3 x te vermelden.
De layout wordt hiermee bepaald.

Verdere settings zoals de lijndikte, aftand, achtergrondkleur, font size etc kunnen eveneens hier gedefinieerd worden.

<style>

.result-display {

margin-left: 15px;

font-size: 125%;

padding: 1px;

}

.google-visualization-controls-label {

margin-left: 20px;

}

.item1 { grid-area: header; }

.item2 { grid-area: info; }

.item3 { grid-area: settings; }

.item4 { grid-area: slider; }

.item5 { grid-area: select; }

.item6 { grid-area: chart; }

.item7 { grid-area: radar; }

.item8 { grid-area: table; }

.item9 { grid-area: error; }

.item10 { grid-area: hidden; }

.grid-container {

display: grid;

grid-auto-columns: 30%;

grid-template-areas:

'header info info'

'settings settings settings'

'slider select select'

'chart radar radar'

'error error hidden'

'table table table'

;

gap: 1px;

background-color: #2196F3;

padding: 1px;

}

.grid-container > div {

background-color: rgba(255, 255, 255, 0.8);

text-align: center;

padding: 20px 0;

font-size: 30px;

}

</style>

3. JavaScript.html

Deze javascript code schrijven we in Apps Script en een aparte html file en wordt in de Index.html gebonden.
Zij vormt de Business layer op de webpagina zijde.

De Javascript code wordt in de <head> sectie gezet.
We binden hierin de standaard bib om charts in webpagina's aan te maken (www.gstatic.com/charts/loader.js), zodat heel wat functionaliteit reeds aanwezig is en halen van deze bib de nodige pakketten binnen:
google.charts.load('current', {'packages':['corechart', 'controls']});
Met de functie aanroep: google.charts.setOnLoadCallback(sendQuery);
bepalen we welke functie dient te worden uitgevoerd nadat de pagina geladen is.

De functie sendQuery() voert een script uit dat we aangemaakt hebben in Apps Script, nl het getSpreadsheetData() script.
Door de extra controle of het ophalen van de data lukt of niet, heb je ook in je webpagina info als er wat fout loopt.

Het resultaat van deze aanroep is het terug krijgen van data, die als paramater wordt doorgegeven aan de functie die wordt aangeroepen als de aanroep succesvol was: drawDashboard(data)

Deze functie moet een aantal dingen doen:

Aanmaken van een Dashboard layout.
Deze wordt gekoppeld aan de div tag op de index pagina zijnde dashboard_div
Het aanmaken van een aantal control types, zoals de NumberRangeFiler, wat een slider is en de CategoryFilter, wat een selector lijst is.
Beide worden aangemaakt met de google.visualization.ControlWrapper method van de vizualisatie api. Je vindt een aantal voorbeelden op: ContolWrapper
Het aanmaken van een aantal grafieken of tabellen, zoals de PieChart en de Table, wat een datatabel is
Beide worden aangemaakt met de google.visualization.ChartWrapper method van de vizualisatie api.

Vermits al deze elementen nog niet verbonden zijn met de data, kunnen ze nog niet getoond worden en geef je enkel de opties mee die noodzakelijk zijn.
De visuele onderdelen zijn telkens gekoppeld aan een specifieke <div> html tag, zodat ook de plaats op de pagina wordt bepaald.
We binden de data aan de verschillende onderdelen:
Je hebt de ChartWrapper delen, die de visualisatie in een grafiek of tabel laten zien en je het de ControlWrapper delen, die een filter vormen voor de data die aangeboden wordt aan de ChartWrapper.
Met de
dashboard.bind(donutRangeSlider, [pieChart,table]);
instructie wordt de filter donutRangeSlider, gebruikt voor de data die aangeboden wordt aan zowel de pieChart als de table. Op deze wijze kan je de filter toepassen op meerdere voorstellingen.
Als laatste onderdeel visualiseren we het dashboard dmv de method draw en binden we de data aan het dashboard.
dashboard.draw(data);
Hier zie je dat slechts 1 dataset kan meegegeven worden!

<html>

<head>

// Load the Visualization API and the controls package.

google.charts.load('current', {'packages':['corechart', 'controls']});

// Set a callback to run when the Google Visualization API is loaded.

// google.charts.setOnLoadCallback(drawDashboard);

google.charts.setOnLoadCallback(sendQuery);

// Callback that creates and populates a data table,

// instantiates a dashboard, a range slider and a pie chart,

// passes in the data and draws it.

/**

* Issue asynchronous request for spreadsheet data.

* From gist.github.com/mogsdad/60dcc4116ed74fceb5f9

function sendQuery() {

google.script.run

.withSuccessHandler(drawDashboard)

.withFailureHandler(function(msg) {

// Respond to failure conditions here.

$('#main-heading').text(msg);

$('#main-heading').addClass("error");

$('#error-message').show();

})

.getSpreadsheetData();

}

function drawDashboard(data) {

// Create a dashboard.

var dashboard = new google.visualization.Dashboard(

document.getElementById('dashboard_div'));

// Create a range slider, passing some options

var donutRangeSlider = new google.visualization.ControlWrapper({

'controlType': 'NumberRangeFilter',

'containerId': 'slider-div',

'options': {

'filterColumnLabel': 'AZflip'

}

});

// Create filter selector

var genderPicker = new google.visualization.ControlWrapper({

'controlType': 'CategoryFilter',

'containerId': 'selector-div',

'options': {

'filterColumnLabel': 'LOC'

}

});

// Create a pie chart, passing some options

var pieChart = new google.visualization.ChartWrapper({

'chartType': 'PieChart',

'containerId': 'piechart-div',

'view': {columns : [0,5]},

// 'options': {

// 'width': 300,

// 'height': 300,

// 'pieSliceText': 'value',

// 'legend': 'right'

// }

});

// Create

var table = new google.visualization.ChartWrapper({

'chartType': 'Table',

'containerId': 'table-div',

});

// Establish dependencies, declaring that 'filter' drives 'pieChart',

// so that the pie chart will only display entries that are let through

// given the chosen slider range.

dashboard.bind(donutRangeSlider, [pieChart,table]);

dashboard.bind(genderPicker,[pieChart,table]);

// Draw the dashboard.

dashboard.draw(data);

}

</script>

</head>

Deploy

je webapp kan je nadien testen via de Test deployments, nadat hij één maal als webapp gediployed geweest is.

Deze Test deployments geven een link met /dev achteraan en zijn rechtstreeks verbonden aan de code die je maakt.
https://script.google.com/a/macros/myuba.be/s/.....rbNhw/dev

Je hoeft dus slechts een refresh te doen van de pagina met bovenstaande link om je aanpassingen in de code direct te kunnen zien.

Eens tevreden kan je een nieuwe versie van je app deployen, zodat ze nadien kan beschikbaar gemaakt worden voor anderen.

Extra's

Multiple sheets

Wanneer je meer dan één datatabel of sheet wil doorgeven, dan kan je dit doen door het doorgeven van een JSON string tussen de backend en de frontend.
Zie ook video
Het voorbeeld hieronder zal je tonen hoe dit gaat:

In de Apps script breng je beide data stukken samen in één object.
Hierna vorm je dit object om tot een JSON string die wordt gehaald door de frontend javascript.

Binnen het frontend javascript dien je nu de JSON string terug te parsen en de onderdelen toe te kennen aan lokale objecten.

Op deze wijze kan je twee of meer sheets doorgeven als data, waardoor je een dashboard kan maken met data uit verschillende sheets.

Apps script:

/**

* callback function of web page to get data

* Data must be 2d array so google.visualization.arrayToDataTable() can be used.

function getSpreadsheetData() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calc2")

var data = ss.getDataRange().getValues();

var ss2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calc1")

var data2= ss2.getDataRange().getValues()

return JSON.stringify({dataA:data,dataB:data2})

}

javascript.html:

function drawDashboard(data_ret) {

const parseData=JSON.parse(data_ret);

//alert(data_ret);

//alert(parseData);

const data=parseData.dataA;

const data2=parseData.dataB;

....

Vega Charts

Het gebruik van Vega Charts of de taal Vega om allerhande mappen en grafieken te tekenen, geeft je heel wat mogelijkheden die niet vervat zitten in de Google standaard.
Een specifieke chart, die je toe laat om heel wat data visueel voor te stellen is de Radar chart.
Het specifieke aan een RADAR chart is dat je meerdere grafieken als een vlak kan definiëreen, waarbij de spaken de positie bepalen en de afstand vanaf het center de waarde.
In het voorbeeld zie je een azimuth chart, waar je 360 sleutels, uit 3 data sets in één figuur weergeeft.
Het hoveren met je muis over een datapunt, geeft je extra informatie en is heel wat overzichtelijker dan een voorstelling in een line/bar chart of in tabel vorm.

Je zou deze voorstelling kunnen gebruikt voor het weergeven van cursusdata, waarbij je de spaken gebruikt voor alle mogelijke opdrachten en testen en de datasets voor de differentiatie tussen beginniveau bepaling, formatieve testen en evaluaties.

De "foto" die zo ontstaat geeft je in één oogopslag de vordering alsook de competentie van een persoon binnen de opleiding.

Een in detail uitwerking van de code vind je op de volgende pagina: VegaChart koppelen aan sheetdata

Web App als Editor Add-on menu

Wanneer je het script wil gebruiken om als editor-add on vrij te geven, dien je in de add-on menu een knop te voorzien om de webapp te openen, zodat bij de eerste aanmaakt de link kan gevonden worden van de webapp.

In principe voorziet google het volgende script om de webapp URL te achterhalen:
ScriptApp.getService().getUrl(), echter we zien dat dit niet (meer) werkt naar behoren.
De url verwijst absoluut niet naar de gekoppelde webapp.
De enige oplossing is om bij de eerste deploy van het script dit als web-app te doen en de URL, hard coded in de functie te steken.
Hierna dien je de app terug te deployen maar als een nieuwe versie (Manage deployments/new version)

Hierna zal de menu blijven werken, telkens je de app als een managed/new version deployed.
Doe je een deployment als New Deployment, verandert de link... (branch wordt nieuw)

/**

* Get published web app url and make addon menu from within GAS

function OpenWebApp(){

let en=ScriptApp.getService().isEnabled()

let url=ScriptApp.getService().getUrl()

// openTab("https://script.google.com/a/macros/myuba.be/s/AKfycbzawwbbUue6o3EAjDZrh2sVFAfXokToeGPyWUycswQn-MlY1hFoFZERnh0hWmThb_Se/exec")

openTab(url)

}

function openTab(url) {

var html = '<script>window.open("'+url+'");google.script.host.close();</script>;'

var userInterface = HtmlService.createHtmlOutput(html);

SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tab');

}

Work around Editor Add-on HTML dialog

Zoals aangehaald is het niet mogelijk om de gepubliceerde URL van de webapp te achterhalen.
Een andere oplossing is het gebruik van de Dialoog boxen en sidebars in de sheet editor om content te tonen.

De voorwaarde om HTML content te tonen op deze wijze is, dat alle data "in een keer" dient geladen te worden.
Dit houd dus in dat het laden van de Stylecheet en JavaScript vanuit de Index pagina niet gebruikt kan worden.
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<?!= HtmlService.createHtmlOutputFromFile('JavaScript').getContent(); ?>
Je dient zelf de code samen te brengen tot één geheel:
Het samenbrengen kan je dmv de getContent() nadat je de vorige laad commando's uit je index.html hebt gehaald.
De volgorde is nog steeds: Stylesheet, Index, JavaScript.

Hierna kan je met de volgende functie de dialoog open en de interactie met je data starten.
Je kan dit als menu item toevoegen aan je sheet (editor add-on) waardoor je het script als Editor add-on kan publiceren en iedereen het kan gebruiken.

/**

* Open webDialog to interact

* This dialog needs an "all in one" html file.(Index/Stylesheet/javascript) *

function OpenHTMLDialog(){

var html= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent()+HtmlService.createHtmlOutputFromFile('Index').getContent()+HtmlService.createHtmlOutputFromFile('JavaScript').getContent()

var userInterface=HtmlService.createHtmlOutput(html)

.setWidth(1920)

.setHeight(1080)

SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tab');

}

Tab sidebar menu

Wanneer je in de sidebare een aantal extra dingen wil doen, kan je dit normaal maar met één scherm.

Het gebruik van wat code laat je toe om een menu structuur op te zetten voor de sidebar.

Custom tooltips bij ChartWrapper

De chartwrapper gebruikt standaard een aantal kolommen als tooltip, echter wanneer deze niet voldoen aan jouw eisen dien je een "custom tooltip" te voorzien.

Meer uitleg vind je op de volgende pagina:Custom Tooltip chartwrapper

Page updated

Report abuse

Dashboard

Dashboard

Info

Concept

Data layer

Business layer

Presentation layer

Stappenplan

Data layer

Businness layer op Apps Script

doGet()/doPut()

getSpreadsheetData()

Presentation layer

Index.html

2. Stylesheet.html

3. JavaScript.html

Deploy

Extra's

Multiple sheets

Apps script:

javascript.html:

Vega Charts

Web App als Editor Add-on menu

Work around Editor Add-on HTML dialog

Tab sidebar menu

Custom tooltips bij ChartWrapper

When all fails, Ham radio to the rescue!