Issue:
Rows cannot be frozen on published sheets: see How can I freeze cols/rows in a public shared table?.
Workaround #1. Embed spreadsheet itself:
Instead of publishing the file to the web and embedding this published version (URL containing /pubhtml), you could embed the spreadsheet itself, if you don't have problems making this spreadsheet public (shared with Anyone with the link). This way, the spreadsheet would be seen as it is, with the frozen rows on top.
You mentioned in comments, though, that you have noticed that the frozen rows cannot be seen correctly on your mobile device, and this causes this workaround not to be appropriate for your situation.
Workaround #2. Build HTML table:
A rather less direct and more laborious workaround could be to do the following (I'll just give some broad pointers and include references for you to investigate further):
- Retrieve the data from the sheet via getRange(row, column, numRows, numColumns) and getDisplayValues(). Since headers should be frozen (they should have separate styling), I'd suggest to retrieve the headers and the rest of the data separately.
- Using this data, create an HTML template to create a dynamically created HTML table.
- When retrieving the data, you should take into account that some cells are merged. You should get the information about which cells are merged and with which ones, and use that information when creating the HTML table. Range.isPartOfMerge() can be used to check whether cells are merged (see this answer for more pointers about the subject). You should in its turn merge the corresponding table cells, for example, using the
colspan attribute from td element. See, for example, this question.
- You should use CSS or similar, to effectively freeze the headers. There are many questions on StackOverflow that tackle this issue. See Freezing/Fixing the Top Header Row of a table or Freeze the top row for an html table only (Fixed Table Header Scrolling) for starters.
Code sample:
Here I attach the code to retrieve the data from the sheet and use it to create a dynamic table (the styling features –frozen rows, general formatting– and the issue with the merged cells are not included –if you have problems with these issues, I'd suggest you to post questions separately about these–):
Code.gs:
function doGet(e) {
return HtmlService.createTemplateFromFile("index").evaluate();
}
function getHeaders() {
const sheet = SpreadsheetApp.getActive().getSheetByName("출석부");
return sheet.getRange(1, 1, 7, sheet.getLastColumn()).getDisplayValues();
}
function getBody() {
const sheet = SpreadsheetApp.getActive().getSheetByName("출석부");
const firstRow = 8;
const numRows = sheet.getLastRow() - firstRow + 1;
return sheet.getRange(firstRow, 1, numRows, sheet.getLastColumn()).getDisplayValues();
}
index.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<div>
<table>
<thead>
<? const headers = getHeaders();
for (let i = 0; i < headers.length; i++) { ?>
<tr>
<? for (let j = 0; j < headers[0].length; j++) { ?>
<th><?= headers[i][j] ?></th>
<? } ?>
<? } ?>
</thead>
<tbody>
<? const body = getBody();
for (let k = 0; k < body.length; k++) { ?>
<tr>
<? for (let l = 0; l < body[0].length; l++) { ?>
<td><?= body[k][l] ?></td>
<? } ?>
<? } ?>
</tbody>
</table>
</div>
</body>
</html>
Reference:
