I currently have the code in google scripts to create a data table based on my google spreadsheet. My goal however is to create multiple unordered lists in HTML with values pulled from each row of the spreadsheet. This is theoretically what it would look like where the values in quotations would be the column names of the spreadsheet
<ul>
<li><b>Vendor Type:</b> "Vendor Type" </li>
<li><b>Location:</b> "Location"</li>
<li><b>Pronouns:</b> "Pronouns "</li>
<li><b>LGBTQ Status:</b> "LGBTQ Status"</li>
</ul>
Below is my current code, which is just outputting it as a data table. This was the last working version so it's what I'm sharing, but I have tried several other methods. All resulting in a blank screen once I deploy so it's hard to troubleshoot.
HTML
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">
<?!= include('JavaScript'); ?> <!--INCLUDE JavaScript.html FILE-->
</head>
<body>
<div class="container">
<br>
<div class="row">
<table id="data-table" class="table table-striped table-sm table-hover table-bordered">
<!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
</table>
</div>
</div>
</body>
</html>
Javascript
<script>
/*
*THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE,
*AND PASS RETURNED DATA TO showData() FUNCTION
*/
google.script.run.withSuccessHandler(showData).getData();
//THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
function showData(dataArray){
$(document).ready(function(){
$('#data-table').DataTable({
data: dataArray,
//CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
columns: [
{"title":"Business Name"},
{"title":"Vendor Type"},
{"title":"Location"},
{"title":"Pronouns"},
{"title":"Website URL"},
{"title":"Facebook URL"},
{"title":"Instagram @"},
{"title":"TikTok URL"},
{"title":"Pinterest URL"},
{"title":"Short Bio"},
{"title":"LGBTQ Status"},
{"title":"Email Address"}
]
});
});
}
</script>
Code.gs
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
//GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
function getData(){
var spreadSheetId = ""; //CHANGE
var dataRange = "Form Responses 1!B2:M"; //CHANGE
var range = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange);
var values = range.values;
return values;
}
//INCLUDE JAVASCRIPT AND CSS FILES
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
My javascript skills are not the strongest (I'm usually a SAS/R coder) and I'm finding it hard to verbalize things using the proper terminology so that a search engine can understand what I mean. If there is a useful article or another similar question already that I missed, please let me know!
I have tried several different versions of code similar to this (which is from chatgpt so I don't think it knew what I was asking really). But all result in a blank page when I deploy the script.
function generateHTML(row) {
return `
<li><b>Vendor Type:</b> ${row[0]}</li>
<li><b>Location:</b> ${row[1]}</li>
<li><b>Pronouns:</b> ${row[2]}</li>
<li><b>LGBTQ Status:</b> ${row[3]}</li>
`;
}
function generateHTMLList() {
const spreadsheetId = 'your_spreadsheet_id';
const sheetName = 'Sheet1';
const range = `${sheetName}!A2:D`;
const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
const data = sheet.getRange(range).getValues();
if (data.length > 0) {
const htmlListItems = data.map(row => generateHTML(row)).join('');
const htmlOutput = `<ul>${htmlListItems}</ul>`;
Logger.log(htmlOutput);
// You can use the HTML output as needed, e.g., to display it in a dialog or send it as an email
} else {
Logger.log('No data found.');
}
}
