Ingave velden

Het gebruik van een dashboard laat je toe om heel wat data interactief te tonen.
Echter in veel gevallen zal je nood hebben aan het instellen van bepaalde waardes, settings en het doorgeven van bepaalde gegevens aan de backend om de data te vergaren.

In de voorbeeldapplicatie is deze optie noodzakelijk om een selectie te maken in de WSPR database, die online beheerd wordt.
De query zal data halen uit deze database aan de hand van de door jou opgegegeven parameters.
De backend moet de verkregen data verwerken tot de correcte vorm waarna deze kan getoond worden in het dashboard.

We gebruiken een externe lib Tabulator, die ons toe laat om heel wat acties te automatiseren

Momenteel gebeurt deze setting in het tabblad Settings van de SS, maar we wensen deze velden aan te passen vanuit de web interface.
We gaan dus een read-modify-write actie moeten toepassen op data uit een sheet, waarna we een add-on functie in apps script gaan aanroepen om de verwerking van de data te starten.

Zie full video: Web App Example w/ Google Sheets, Tabulator, JavaScript, Apps Script, HTML, CSS Tutorial

Stappenplan

Om deze lib te kunnen gebruiken dienen we gegevens toe te voegen aan de CSS, Index en aan de JAVASCRIPT van de frontend van ons programma.

Het integreren door rechtstreeks de libs aan te roepen in je code kan je terug vinden op hun website:

Documentation/QuickStart/CDN UNPKG

Tabulator - Interactive JavaScript TablesCreate interactive data tables in seconds with Tabulator. A free, fully featured JavaScript table generation library.

Aanpassen files (voorbeeld)

Stylesheet.html.

Voeg de link bovenaan toe..

Javascript.html

Voeg de script gedeelte bovenaan toe aan deze file, boven het stuk code wat je zelf gaat aanpassen.
De luxon lib is nodig wanneer je met datum formaten wil werken binnen Tabular, die afwijken van de standaard google formaten.

Index.html

Voorzie nu een div tag met de juiste omschrijving in je index code.
In de documentatie Example Setup zie je de verschillende stappen die je dient te gebruiken in je index file.
Vermits wij enkel de index file gebruiken voor de plaatshouder en grove structuur van onze site, zullen de javascript onderdelen in de javascript.html file terecht komen (zie voorgaande)

Je dient een plaatshouder te definiëren in index.html, volgens het principe van een grid, zoals voorheen aangehaald:
<div class="item3">
<div id="setting-table">
</div>
</div>
Je dient de data klaar te maken.
In ons geval zal dit in het javascript.html deel komen.
Je kan dus de voorbeelddata copieren en in je javascript plakken als Const (var hoeft niet)
//define some sample data
const tabledata = [
{id:1, name:"Oli Bob", age:"12", col:"red", dob:""},
{id:2, name:"Mary May", age:"1", col:"blue", dob:"14/05/1982"},
{id:3, name:"Christine Lobowski", age:"42", col:"green", dob:"22/05/1982"},
{id:4, name:"Brendon Philips", age:"125", col:"orange", dob:"01/08/1980"},
{id:5, name:"Margret Marmajuke", age:"16", col:"yellow", dob:"31/01/1999"},
]
Vorm de data om in een tabel door het doorgeven van een constructor object aan de jQuery widget.
//create Tabulator on DOM element with id "example-table"
var table = new Tabulator("#setting-table", {
height:205, // set height of table (in CSS or here), this enables the Virtual DOM and improves render speed dramatically (can be any valid css height value)
data:tabledata, //assign data to table
layout:"fitColumns", //fit columns to width of table (optional)
columns:[ //Define Table Columns
{title:"Name", field:"name", width:150},
{title:"Age", field:"age", hozAlign:"left", formatter:"progress"},
{title:"Favourite Color", field:"col"},
{title:"Date Of Birth", field:"dob", sorter:"date", hozAlign:"center"},
],
})
//trigger an alert message when the row is clicked
table.on("rowClick", function(e, row){
alert("Row " + row.getData().id + " Clicked!!!!");
})

Vanaf nu kan je de tabel reeds zien en als je op een cel klikt zal er een popup komen met "clicked" .
Dit omdat de lib de method on bevat.
We zien ook dat de data voor deze tabel bestaat uit een array van objecten.
De id property bevat de row index, waar de andere de gegeven bevatten.
We hebben dit gebruikt om in het voorbeeld de geklikte rij weer te geven: row.getData().id

Klaarmaken data in Apps Script

De data die we nodig hebben heeft een specifiek formaat, zijnde een array van objects.
Het is de bedoeling om de data uit de sheet in dit formaat, met de correcte header/property info, aan te bieden aan de frontend.

Hiervoor gebruiken we een functie die ons toe laat om een sheet met header row naar dit formaat om te zetten in Apps Script.
(zie video)

/**

* creates an array of objects from a sheet.

* The header row defines the properties

* @param {string} sheet - name of sheet

* @return {array} jsData - array of objects

function getObjData(sheet) {

const ss = SpreadsheetApp.getActiveSpreadsheet()

const ws = ss.getSheetByName(sheet)

const dataRange = ws.getRange("A1").getDataRegion()

const data = dataRange.getDisplayValues()

// Header array

const headers = data.shift()

//convert to array of objects

const jsData = data.map(r => { //map all rows in data (without headers)

const tempObject = {} // create for each row a new object

headers.forEach((header, i) => { //loop through the headers and use de index of the column (generated by de foreach) to find the element

tempObject[header] = r[i] //the opbject property by name [header] will be assigned to the value of the row (r) at column i.

})

return tempObject

})

return jsData

//console.log(jsData)

}// end getData function

Colums aanpassen in Javascript.html

Tevens zullen we in Javascript.html de colums definitie moeten aanpassen, in overeenstemming met de aangeboden data.

Vermits Tabular toe laat om cellen editeerbaar te maken in de tabel, geven we hier een voorbeeld waarbij een datum veld wordt gemnaakt en waar we dmv de luxom lib het formaat kunnen aanpassen, alvorens het aan de backend wordt terug gegeven.

Je kan ook list velden maken, met vaste waardes (zie band veld) maar ook met lijsten waarnaar gerefereerd wordt.

columns:[ //Define Table Columns

{title:"Start_timedate", field:"Start_timedate", width:150, editor:"datetime", editorParams:{

format:"yyyy-MM-dd hh:mm:ss", // the format of the date value stored in the cell

elementAttributes:{

title:"click icon to choose date-time" // custom tooltip

}}},

{title:"End_timedate", field:"End_timedate", width:150,editor:"datetime", editorParams:{

format:"yyyy-MM-dd hh:mm:ss", // the format of the date value stored in the cell

elementAttributes:{

title:"click icon to choose date-time" // custom tooltip

}}},

{title:"band", field:"band", editor:"list", editorParams:{

values: [ 'LF', 'MF', '160m', '80m', '60m', '40m', '30m', '20m', '17m', '15m', '12m', '10m', '6m', '4m', '2m', '70cm', '23cm'],

verticalNavigation:"hybrid",

}},

{title:"call1", field:"call1"},

......

Verdere functies

Editors in de Tabular

Je kan in Tabular bepalen welk soort editing je kan doen in een tabel en dit per kolom etc.
Zie documentation/Data manipulation/Editing
Voor het gebruik van DateTime in een speciaal formaat moet je ook de luxon.js implementeren, terug te vinden op de volgende site: luxon CDN (copy HTML+SRI) en plaats deze bovenaan in je javascript.html file.

Het is natuurlijk belangrijk om na de aanpassing van een waarde, de waarde terug te schrijven naar de sheet.
Hiervoor heb je een "Event" listener nodig in de tabel, die aangeeft dat je wat hebt gewijzigd.
Zie Getting Started/Events

Met onderstaande code kan je achterhalen hoe de data in Tabular wordt weergegeven om daarna verdere verwerking mogelijk te maken.

We kiezen voor de editing events en specifiek voor cell edited.
table.on("cellEdited", function(cell){

//cell - cell component

console.log(cell)

console.log(cell._cell.column.field)

console.log(cell._cell.value)

});

Via de console log kunnen we nu achterhalen hoe de data wordt aangepast en waar we de nieuwe data en referentie kunnen terug vinden.
Zie video

Events in Tabular

Uit de achterhaalde code weten we welke velden de juiste data bezitten.

Deze kunnen we nu aanbieden aan de backend om zo de sheet aan te passen.
In dit voorbeeld gebruiken we de ID, die uniek is, de kolom naam, welke de field waarde is en de aangepaste waarde om terug te geven als een object.

Dmv de if. includes functie kan je bepalen welke velden wel en niet worden gebruikt om de backend te updaten.
In de array defineer je de veldnamen die moeten doorgegeven worden.
if (["Gender","Name"].includes(field)){}

Het doorgeven gebeurt door de aanroep van het script met het doorgeven van de parameters als een object.
.editCell({id:id,val:val,field:field})

De rest van de functie dient om je gebruiker te informeren over de lopende actie via een melding op de webpagina.

table.on("cellEdited", function(cell){

const id = cell._cell.row.data.Id

const field = cell._cell.column.field

const val = cell._cell.value

//look for field in provided list

if (["Gender","Name"].includes(field)){

elements.alerts.textcontent ="Saving Changes...."

google.script.run

.withSuccessHandler(()=>{

elements.alerts.textcontent = "Changes Saved"

clearAlerts(elements.alerts) //custom function to clear popup

})

.withFailureHandler (()=>{

elements.alerts.textcontent = "Error Saving Changes"

clearAlerts(elements.alerts)

})

.editCell({id:id,val:val,field:field})

}

})

Callback

Om bij het aanpassen van de data ervoor te zorgen dat de backend eveneens wordt aangepast, is het nodig om een functie te triggeren die de data transporteerd en in Apps Script, de data in de sheet aanpast.
De data wordt doorgegeven via een object.
zie video topic

Generieke functie

Indien je er voor zorgt dat het aanpassen van een cel in de frontend, gebeurt door de row te voorzien van een specifieke unieke ID, dan kan je deze ID gebruiken om in de backend de row op te zoeken en zo de overeenkomstige cel(kolom) aan te passen.
Het zoeken kan mbv createTextFinder.
Op identieke wijze kan je ook de kolom terug vinden, doordat je de field property gebruik.
Hierna kan je de betreffende cell aanpassen.

Indien je geen specifieke id hebt, kan je ook gebruik maken van de row nummer.
Deze is terug te vinden in:
const id = cell._cell.row.position

en kan je gebruiken in de functie voor het verwijzen naar de row.
const recordRowNumber = props.id + 1
Vermits Tabular de header rij niet mee telt, dien je de row met 1 te verhogen.

/**

* change Gender data

* @param{object} props, object of properties to pass, minimal ID to find corresponding row to edit cell.

function editCell(props){

const ss = SpreadsheetApp.getActiveSpreadsheet()

const ws = ss.getSheetByName("Customers")

//search ID in first column

const idCellMatched = ws.getRange("A2:A").createTextFinder(props.id).matchEntireCell(true).matchCase(true).findNext()

// search column in first row

const columnCellMatched = ws.getRange("1:1").createTextFinder(props.field).matchEntireCell(true).matchCase(true).findNext()

if (idCellMatched ===null) throw new Error ("No Matching Record")

if (columnCellMatched ===null) throw new Error ("No Matching Field")

const recordRowNumber = idCellMatched.getRow()

const recordColumnNumber = columnCellMatched.getColumn()

// write value to sheet

ws.getRange(recordRowNumber, recordColumnNumber).setValue(props.val)

}

Sequencial execution

Zie video: google.script.run replaced with JavaScript Promise API, Async & Await in a Web App

Standaard in JS wordt alles parallel uitgevoerd owv een Event loop.
Echter in sommige gevallen is het noodzakelijk dat je wacht op het resultaat van een vorige bewerking om verder te gaan met je uitvoering.

Hiervoor worden de principes Async, await en promise gebruikt.

Generieke functie runGoogleScript(serverFunctionName)

Deze functie wordt gebruikt voor het aanroepen van de GAS scripts binnen een async functie.
Er kan gewacht worden op de promise die wordt terug gegeven alvorens een volgende actie aan te vatten.

/**

* GAS script run, promise

* @param{string} serverFunctionName, name of GAS script

* @return{promise}

function runGoogleScript(serverFunctionName){

return new Promise((resolve,reject)=>{

google.script.run.withSuccessHandler(data =>{

resolve(data)

}).withFailureHandler(er =>{

reject(er)

})[serverFunctionName]()

});

}

Time delay functie

Deze kan gebruikt worden om tijdens de uitvoering de volgende actie te laten wachten echter zonder echt de Event-loop te onderbreken.
Dit kan handig zijn in situaties waar de browser tijd nodig heeft om de pagina te tekenen, maar de info reeds intern verwerkt is, zoals wanneer er een tabel wordt getekend met duizenden waardes.

/**

* waittime to use in async functions of sequencial nature

async function waitTime(t) {

return new Promise(res => {

setTimeout(res,t);

})

}

Andere functies binnen async

Ook normale functies, die intern worden uitgevoerd en geen extere query nodig hebben kunnen in een sequenciele uitvoering worden gestoken, door de aanroep te laten vooraf gaan door een await binnen een async functie.
Beter is nog om in zulke functies , die al dan niet een return waarde hebben, toch een promise te maken, om zo de reguliere werking als een hybride systeem te evenaren.
Wat er als resolve wordt gegeven is onbelangrijk.

return new Promise((resolve,reject) => {

resolve("Settings")

})

Voorbeeld page loading

Hierin vind je verschillende stappen terug:

Via de eventlistener kan je een js functie laten uitvoeren nadat de DOMcontent geladen is. De domcontent is terug te vinden in je index.html.
Binnen de pageLoad() functie kan je bepaalde elementen van je pagina gebruiken om zo dynamische meldingen te kunnen weergeven.
Wij gebruiken het element "alerts"
de loadpage() functie is een asynchrone functie, waardoor we de squenciele afloop dmv await kunnen gebruiken.
1. Standaard voorzie je deze van een try.catch zodat eventuele fouten kunnen opgevangen worden
2. De data die terug komt van de GAS scripts wordt toegekend aan een variabele die in het volgende stuk nodig is.
  De data van beide delen wordt "sequencieel" binnen gehaald, waardoor deze aanwezig is als de volgende functie wordt uitgevoerd, nl de functie die die data nodig heeft.
3. Vermits we een melding willen geven bij het einde van deze update, moeten we het einde van de uitvoering van beide functies eveneens afwachten.
  Vandaar ook de await voor de drawSettings en drawDashboard functies.
4. De extra tijd is nodig om de browser de tijd te laten om de tabel te renderen.

// Load the Visualization API and the controls package.

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

const elements ={} // elements object

// Execute sequece of getting multiple data sources on pageload

document.addEventListener("DOMContentLoaded",pageLoad)

......

/**

* Pageload function

function pageLoad(){

elements.alerts = document.getElementById('alerts')

loadPage()

}

async function loadPage(){

try{

// sequential actions

elements.alerts.innerText = "Loading"

const jsData = await runGoogleScript("getObjData") //fetch settings data

const data_ret = await runGoogleScript("getSpreadsheetData") //fetch table data

await drawSettings(jsData)

await drawDashboard(data_ret)

await waitTime(2000) //give PC time to draw table

elements.alerts.innerText = "Ready"