小贝子编程

将Google工作表作为HTML表发布到Google网站



我有一张谷歌表，里面有大约600行数据。我希望能够将这个电子表格显示和过滤为HTML表格，并将其显示在谷歌网站上。我在网上找到了这段代码以及我使用过的"显示"HTML。

然而，当我作为web应用程序发布时，我只看到表的轮廓，但在行中没有数据。我的表的轮廓看起来是正确的，并且包含了我需要的搜索过滤器，但是表中没有显示任何数据。

下面的代码有问题吗？

function myFunction() {
}function doGet(e){
var SHEET_ID=e.parameter.sheet_id
var html=HtmlService.createTemplateFromFile('Display')
var ss=SpreadsheetApp.openById('My Google Sheet ID')
var sheet=ss.getSheetByName('Sheet2')
html.data=getRowsData(sheet,sheet.getDataRange(),1)
return html.evaluate().setTitle('Log')
Logger.log(getRowsData(sheet,sheet.getDataRange(),1))
}

function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent()
}


function getRowsData(sheet, range, columnHeadersRowIndex){
columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex()-1;
var numColumns = range.getEndColumn() - range.getColumn() + 1;
var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, 
numColumns);
var headers = headersRange.getValues()[0];
return getObjects(range.getValues(),normalizeHeaders(headers));
}

function getObjects(data, keys) {
var objects = [];
for (var i = 0; i < data.length; ++i) {
var object = {};
var hasData = false;
for (var j = 0; j < data[i].length; ++j) {
var cellData = data[i][j];
if (isCellEmpty(cellData)) {
continue;
}
object[keys[j]] = cellData;
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}

function normalizeHeaders(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) {
var key = normalizeHeader(headers[i]);
if (key.length > 0) {
keys.push(key);
}
}
return keys;
}
function normalizeHeader(header) {
var key ='';
var upperCase = false;
for (var i = 0; i < header.length; ++i) {
var letter = header[i];
if (letter == '' && key.length > 0) {
upperCase = true;
continue;
}
if (!isAlnum(letter)) {
continue;
}
if (key.length == 0 && isDigit(letter)) {
continue; // first character must be a letter
}
if (upperCase) {
upperCase = false;
key += letter.toUpperCase();
} else {
key += letter.toLowerCase();
}
}
return key;
}

function isAlnum(char) {
return char >= 'A' && char <= 'Z' ||
char >= 'a' && char <= 'z' ||
isDigit(char);
}

function isDigit(char) {
return char >= '0' && char <= '9';
}

function isCellEmpty(cellData) {
return typeof(cellData) == 'string' && cellData == '';
}

Display.html

<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!=include('Style')?>
</head>
<body>
<p></p>
<table id="dataTable" border="1" cellpadding="2" cellspacing="0">
<th width="10%">Employee Number</th>
<th width="15%">Name</th>
<th width="15%">Surname</th>
<th width="15%">EmpNo</th>
<th width="10%">Title</th>
<th width="15%">Div</th>
<th width="10%">Office</th>
<th width="10%">Form</th>
<tr>
<td bgcolor="#eb8c00" align="center"><input type="text" 
id="EmployeeNumberSearchBox" onkeyup="SearchFunc()" 
placeholder="EmployeeNumber..." width="30"></td>
<td bgcolor="#eb8c00" align="center"><input type="text" 
id="NameSearchBox" 
onkeyup="SearchFunc()" placeholder="Name..." width="35"></td>
<td bgcolor="#eb8c00" align="center"><input type="text" id="SurnameSearchBox" 
onkeyup="SearchFunc()" placeholder="Surname..." width="35"></td>
<td bgcolor="#eb8c00" align="center"><input type="text" id="EmpNoSearchBox" 
onkeyup="SearchFunc()" placeholder="EmpNo..." width="35"></td>
<td bgcolor="#eb8c00" align="center"><input type="text" id="TitleSearchBox" 
onkeyup="SearchFunc()" placeholder="Title..." width="35"></td>
<td bgcolor="#eb8c00" align="center">
<select id="Div" onchange="SearchFunc();">
<option value="">Div...</option>
<option value="DOO">DOO</option>
<option value="PIT">PIT</option>
<option value="FID">FID</option>
<option value="FIS">FIS</option>
<option value="General">General</option>
<option value="Other">Other</option>
<option value="IS">IS</option>
<option value="SAS">SAS</option>
</select>
</td>
<td bgcolor="#eb8c00" align="center">
<select id="Office" onchange="SearchFunc();">
<option value="">Office...</option>
<option value="London">London</option>
<option value="New York">New York</option>
<option value="Berlin">Berlin</option>
<option value="Eindhoven">Eindhoven</option>
<option value="Lille">Lille</option>
<option value="Vienna">Vienna</option>
<option value="Copenhagen">Copenhagen</option>
</select>
</td>
<td bgcolor="#eb8c00" align="center"></td>

</tr>
<? for (var i=1;i<data.length;i++) {?>
<tr style="height:15px;">
<td align="center"><?=data[i]['Employee Number']?> </td>
<td align="left"><?=data[i]['Div']?> </td>
<td align="center"><?=data[i]['EmpNo']?> </td>
<td align="center"><?=data[i]['Name']?> </td>
<td align="center"><?=data[i]['Surname']?> </td>
<td align="center"><?=data[i]['Title']?> </td>
<td align="center"><?=data[i]['Office']?> </td>
<td align="center">
<a href="<?=data[i]['=ARRAYFORMULA(HYPERLINK(K1:K616, "Click here to complete 
Access Form"))']?>" target="_blank"><button>Open Form</button> 
</a>
</td>
</tr>
<? } ?>
</table>
</body>
</html>

样式.Html

<style>
input[type=text] {
font-size: 12px; /* Increase font-size */
padding: 12px 12px 12px 12px; /* Add some padding */
border: 1px solid #ddd; /* Add a grey border */
margin-bottom: 3px; /* Add some space below the input */
margin-top: 2px;
}
#EmployeeNumberSearchBox, #EmpNoSearchBox, #NameSearchBox, 
#SurnameSearchBox, #OfficeSearchBox, #TitleSearchBox {
text-align:center;
width:50%; 
}
#DivSearchBox{
text-align:center;
width:85%;
}

#dataTable {
border-collapse: collapse; /* Collapse borders */
width: 100%; /* Full-width */
border: 1px solid #ddd; /* Add a grey border */
font-size: 14px; /* Increase font-size */
table-layout: fixed;
}
#dataTable th{
text-align: center; /* Centre-align text */
background-color: #d04a02;
color: #ffffff
}
#dataTable td {
padding: 3px 5px 2px 10px; 
}
#dataTable tr {
/* Add a bottom border to all table rows */
border-bottom: 1px solid #ddd;
}

button {
background-color: #464646;
border:none;
padding: 3px 5px 2px;
text-align: center;
display: inline-block;
font-size: 14px;
font-family: Helvetica Neue;
font-style: Bold;
color: white;
}
button:hover {
background-color: #eb8c00; /* Green */
color: white;
cursor:pointer;
}

select {
border: 0;
width: 100%;
height:35px;
text-align-last:center;
}
</style>

Java.html

<script>

function SearchFunc() {
var EmployeeNumberInput, DivInput, EmpNoInput, NameInput, 
SurnameInput, OfficeInput, TitleInput,
EmployeeNumberFilter, DivFilter, EmpNoFilter, NameFilter, 
SurnameFilter, OfficeFilter, TitleFilter,
table, tr, i,
EmployeeNumberValue, DivValue, EmpNoValue, NameValue, 
SurnameValue, OfficeValue, TitleValue;
EmployeeNumberInput = document.getElementById("Employee Number");
DivInput =  document.getElementById("Div");
EmpNoInput =  document.getElementById("EmpNo");
NameInput =  document.getElementById("Name");
SurnameInput =  document.getElementById("Surname");
TitleInput =  document.getElementById("Title"); 
OfficeInput = document.getElementById("Office");
EmployeeNumberFilter = EmployeeNumberInput.value.toUpperCase();
DivFilter = DivInput.value.toUpperCase();
EmpNoFilter = EmpNoInput.value.toUpperCase();
NameFilter = NameInput.value.toUpperCase();
SurnameFilter = SurnameInput.value.toUpperCase();
TitleFilter = TitleInput.value.toUpperCase();
OfficeFilter = OfficeInput.value.toUpperCase();

table = document.getElementById("dataTable");
tr = table.getElementsByTagName("tr");

for (i = 2; i < tr.length; i++) {
EmployeeNumberValue = tr[i].getElementsByTagName("td")[0];
DivValue = tr[i].getElementsByTagName("td")[1];
EmpNoValue = tr[i].getElementsByTagName("td")[2];
NameValue = tr[i].getElementsByTagName("td")[3];
SurnameValue = tr[i].getElementsByTagName("td")[4];
TitleValue = tr[i].getElementsByTagName("td")[5];
OfficeValue = tr[i].getElementsByTagName("td")[6];

if (EmployeeNumberValue) {
if (EmployeeNumberValue.textContent.toUpperCase().indexOf
(EmployeeNumberFilter) > -1 
&& 
DivValue.textContent.toUpperCase().indexOf(DivFilter) > -1 
&& 
EmpNoValue.textContent.toUpperCase().indexOf(EmpNoFilter) > -1
&& 
NameValue.textContent.toUpperCase().indexOf(NameFilter) > 
-1
&& 
SurnameValue.textContent.toUpperCase().indexOf(SurnameFilter) > -1
&& 
TitleValue.textContent.toUpperCase().indexOf(TitleFilter) > -1)
&& 
OfficeValue.textContent.toUpperCase().indexOf(OfficeFilter) > -1) {

tr[i].style.display = "";
} else {
tr[i].style.display = "none";
}
}
}
}

function sortTable() {
var table, rows, switching, i, x, y, shouldSwitch;
table = document.getElementById("dataTable");
switching = true;
/* Make a loop that will continue until
no switching has been done: */
while (switching) {
// Start by saying: no switching is done:
switching = false;
rows = table.rows;
/* Loop through all table rows (except the
first, which contains table headers): */
for (i = 2; i < (rows.length - 1); i++) {
// Start by saying there should be no switching:
shouldSwitch = false;
/* Get the two elements you want to compare,
one from current row and one from the next: */
x = rows[i].getElementsByTagName("TD")[0];
y = rows[i + 1].getElementsByTagName("TD")[0];
// Check if the two rows should switch place:
if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
// If so, mark as a switch and break the loop:
shouldSwitch = true;
break;
}
}
if (shouldSwitch) {
/* If a switch has been marked, make the switch
and mark that a switch has been done: */
rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
switching = true;
}
}
}


</script>

使用<?!=而不是<?=

发件人https://developers.google.com/apps-script/guides/html/templates#force-printing_scriptlets

强制打印scriptlets

强制打印scriptlet使用语法<?!= ... ?>，与打印scriptlet类似，只是它们避免上下文转义。

如果脚本允许不受信任的用户输入，上下文转义非常重要。相比之下，如果scriptlet的输出有意包含HTML或您希望完全按照指定插入的脚本，则需要强制打印。

一般来说，除非您知道需要不更改地打印HTML或JavaScript，否则请使用打印Scriptlet而不是强制打印Scriptlet。

相关

  • 如何在谷歌应用程序脚本的HTMLOutput中使用scriptlet

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium