Pull the Google spreadsheet data and create a dynamic dashboard with looping charts using Google app script and HTML

[Dhayalni]

Current spreadsheet
Output of one (chart)

I am trying to pull the Google spreadsheet data and create a dynamic dashboard using Google app script and HTML for the spreadsheet data.

Where I am able to read one column data in the spreadsheet and able to create the chart for that specific column data. But I am unable to read when spreadsheet has multiple column data and create separate dynamic chats for each column.

For example
i. if column one contains data means (only Column A), it needs to produce one chart in the frond-end.
ii. if column one (Column A), two (Column B) contains data means it needs to produce 2 charts in the frond-end.
iii. Whereas charts data need to be changed as per the spreadsheet data.
iv. Need to create the charts in loop format.

Example - Sheet name-"Dashboard"

Column A	Column B
Proactive Lens Gleam	Gleaming annoying
Proactive Lens Gleam	Gleaming annoying
Sentiment on gleam	Gleaming annoying
Sentiment on gleam	Gleaming annoying
Sentiment on gleam	Gleaming annoying
Sentiment on gleam	Gleaming annoying
Sentiment on gleam	Gleaming annoying
Sentiment	Gleaming annoying
Sentiment on gleam	Gleaming annoying
Sentiment	Gleaming annoying

I do need help / advise on how can I update or produce dynamic view of my HTML dashboard (web app) any time there's an update on my spreadsheet.

Please find the code.gs and output.html code (Consider the data from the above example-sheet name "Dashboard")
`- Code.gs

function doGet(e) {
return HtmlService.createHtmlOutputFromFile('output');

}

var sheet = SpreadsheetApp.getActiveSpreadsheet()
var sheetDump = sheet.getSheetByName('Dump')
var sheetReviews = sheet.getSheetByName('Reviews')
var dashboardSheet = sheet.getSheetByName("Dashboard");
var dashboardSheetLastRow = dashboardSheet.getLastRow()

function totalCategories() {

var count = dashboardSheet.getLastColumn() -5
return count

}

function getCountCategory() {

dashboardSheetLastRow = dashboardSheet.getLastRow()
dashboardSheetLastCol = dashboardSheet.getLastColumn()

console.log(dashboardSheetLastCol)

var data = dashboardSheet.getRange(2, 6, dashboardSheetLastRow, 1).getValues();
var dataSorted = data.sort()
var dataFinal = []
var values = []

for (const element of dataSorted) {
if(element != ''){
if (dataFinal[element]) {
dataFinal[element] += 1;
} else {
dataFinal[element] = 1;
}
}
}

for (let x in dataFinal) {
var value = []
value.push(x,dataFinal[x])
values.push(value)
}

// console.log(values);

return values

}`

`- output.html

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script> <script src="https://cdn.tailwindcss.com/"></script>

<script> let categoriesCount = 0 // Get total number of categories from the data sheet google.script.run.withSuccessHandler(categories).totalCategories(); function categories(item) { categoriesCount = item var loopCount = 0 var ctx = [] for(var i=1; i<= categoriesCount; i++){ google.script.run.withSuccessHandler(function(ar) { console.log(ar); var label = []; var data = []; var title = 'Chart1'; ar.forEach(function(item, index) { label.push("'" + item[0] + "'"); data.push(item[1]); }); loopCount += 1 var canvasID1 = ' ' var canvasID3 = "<script>var ctx" + loopCount + ";var chartid = 'myChart" + loopCount + "';console.log(chartid);" var canvasID4 = "ctx" + loopCount + " = document.getElementById(chartid);" var canvasID5 = "new Chart(ctx" + loopCount + ", {type: 'bar',data: {labels: [" + label + "],datasets: [{label: '" + title + "',data: [" + data + "],borderWidth: 1}]},options: {scales: {y: {beginAtZero: true}}}});" var canvasID6 = "" var canvasID = canvasID1+canvasID3+canvasID4+canvasID5+canvasID6 document.getElementById('content').innerHTML += canvasID }).getCountCategory(); } }; </script>`

I do need help / advise on how can I update my HTML dashboard (web app) any time there's an update on my spreadsheet. Please help me to create multiple charts with a loop as per dynamic data. (Means if data is available then need to create chart and should not create chart if doesn't have data in the column)