Merged Cells not working correctly making Web Page

46 views

Skip to first unread message

Tegan Jones

unread,

Feb 11, 2025, 5:13:31 AM2/11/25

to Google Apps Script Community

I am trying to get the cells that are merged in Google Sheets to show correctly using Apps Scripts. With the attached screenshots, you can see that I have almost got it except cells B4-D4 are not merging correctly which I think makes cells F4:G4 not show up at all and not merging as well. I have included in the script to exclude column E. I will paste the script coding that I am currently on above the screenshots. And I am willing to make a copy of this if someone can help me out on it. I would also want to fix the issue with the Main header (B2:K2) being overextended in the web.

Below is also a sample spreadsheet:

https://docs.google.com/spreadsheets/d/1mIgMJ5gPBli0LLgMtQyFEwK2Rl4j9-DL7vVeBhed_EE/edit?usp=sharing

CODE:

function doGet() {

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

const sheets = spreadsheet.getSheets().filter(sheet => !sheet.isSheetHidden());

let html = `

<html>

<head>

<style>

body { font-family: 'Russo One', Arial, sans-serif; margin: 10px; }

h1 { text-align: center; font-size: 24px; }

table { border-collapse: collapse; width: 100%; margin-top: 20px; }

th, td { border: 1px solid #dddddd; text-align: left; padding: 8px; vertical-align: middle; }

th { background-color: #f2f2f2; }

.hidden { display: none; }

table tbody { display: block; overflow: auto; height: 1000px; }

thead tr:nth-child(1) { position: sticky; top: 0; background-color: #f2f2f2; }

</style>

function showSheet(sheetId) {

const sheets = document.querySelectorAll(".sheet");

sheets.forEach(sheet => sheet.classList.add("hidden"));

document.getElementById(sheetId).classList.remove("hidden");

}

</script>

</head>

<body>

<h1>${spreadsheet.getName()}</h1>

<label for="sheetSelector">Choose a sheet:</label>

${sheets.map((sheet, index) => `<option value="sheet${index}">${sheet.getName()}</option>`).join('')}

</select>

sheets.forEach((sheet, index) => {

const sheetName = sheet.getName();

console.log(`Checking sheet: ${sheetName}`);

const range = sheet.getRange(2, 2, 49, 10); // B2:K50

const values = range.getValues().map(row => row.filter((_, colIndex) => colIndex !== 3)); // Exclude Column E

const backgrounds = range.getBackgrounds().map(row => row.filter((_, colIndex) => colIndex !== 3));

const fontColors = range.getFontColors().map(row => row.filter((_, colIndex) => colIndex !== 3));

const fontFamilies = range.getFontFamilies().map(row => row.filter((_, colIndex) => colIndex !== 3));

const bolds = range.getFontWeights().map(row => row.filter((_, colIndex) => colIndex !== 3));

const alignments = range.getHorizontalAlignments().map(row => row.filter((_, colIndex) => colIndex !== 3));

const verticalAlignments = range.getVerticalAlignments().map(row => row.filter((_, colIndex) => colIndex !== 3));

const numberFormats = range.getNumberFormats().map(row => row.filter((_, colIndex) => colIndex !== 3));

// Get merged cell ranges

const mergedRanges = range.getMergedRanges();

let mergedMap = new Map();

mergedRanges.forEach(range => {

const row = range.getRow() - 2; // Ensure ATL is in row 2

let col = range.getColumn() - 1;

if (col >= 4) col -= 0; // Skip Column E

const numRows = range.getNumRows();

const numCols = range.getNumColumns();

console.log(`Sheet: ${sheetName} - Merged Cell Found: Row ${row}, Col ${col}, Rows ${numRows}, Cols ${numCols}`);

mergedMap.set(`${row},${col}`, { rowspan: numRows, colspan: numCols });

for (let r = 0; r < numRows; r++) {

for (let c = 0; c < numCols; c++) {

if (!(r === 0 && c === 0)) {

mergedMap.set(`${row + r},${col + c}`, "skip");

}

});

html += `

<h2>${sheetName}</h2>

<table>

<thead>

<tr>

${values[0].map((cell, colIndex) => { // Shifted to row 2

const mergedCell = mergedMap.get(`0,${colIndex}`);

console.log(`Rendering Header Row 2: Col ${colIndex}, Cell: ${cell}`);

const style = `

background-color: ${backgrounds[0][colIndex]};

color: ${fontColors[0][colIndex]};

font-family: ${fontFamilies[0][colIndex] || 'Arial, sans-serif'};

font-weight: ${bolds[0][colIndex]};

text-align: ${alignments[0][colIndex]};

vertical-align: ${verticalAlignments[0][colIndex]};

if (mergedCell) {

return `<th style="${style}" colspan="${mergedCell.colspan}" rowspan="${mergedCell.rowspan}">${cell}</th>`;

}

return `<th style="${style}">${cell}</th>`;

}).join('')}

</tr>

</thead>

<tbody>

${values.slice(2).map((row, rowIndex) => {

return `

<tr>

${row.map((cell, colIndex) => {

const rowKey = `${rowIndex + 2},${colIndex}`; // Adjusted for skipped rows

const mergedCell = mergedMap.get(rowKey);

if (mergedCell === "skip") return ""; // Skip merged cells

const style = `

background-color: ${backgrounds[rowIndex + 2][colIndex]};

color: ${fontColors[rowIndex + 2][colIndex]};

font-family: ${fontFamilies[rowIndex + 2][colIndex] || 'Arial, sans-serif'};

font-weight: ${bolds[rowIndex + 2][colIndex]};

text-align: ${alignments[rowIndex + 2][colIndex]};

vertical-align: ${verticalAlignments[rowIndex + 2][colIndex]};

const formattedValue = formatCellValue(cell, numberFormats[rowIndex + 2][colIndex]);

if (mergedCell) {

return `<td style="${style}" colspan="${mergedCell.colspan || 1}" rowspan="${mergedCell.rowspan || 1}">${formattedValue}</td>`;

}

return `<td style="${style}">${formattedValue}</td>`;

}).join("")}

</tr>

}).join("")}

</tbody>

</table>

</div>

});

html += `</body></html>`;

return HtmlService.createHtmlOutput(html)

.setTitle("CFRL Career Track Wins")

.setWidth(800)

.setHeight(600);

}

// ✅ Fix - Define `formatCellValue`

function formatCellValue(value, format) {

if (!value || format === "@") return value;

if (typeof value === "number") {

let decimalPlaces = 0;

if (format && /0+$/g.test(format)) {

decimalPlaces = (format.match(/0+$/) || [""])[0].length;

}

return new Intl.NumberFormat("en-US", {

minimumFractionDigits: decimalPlaces,

maximumFractionDigits: decimalPlaces,

}).format(value);

}

return value;

}

George Ghanem

unread,

Feb 11, 2025, 1:43:15 PM2/11/25

to google-apps-sc...@googlegroups.com

Just a question, why are you doing this rather than just embedding the spreadsheet in your website?

Embedding will show up with all the proper formatting of the spreadsheet.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/0d8772c8-357c-4e1f-a7e3-59748882444dn%40googlegroups.com.

Reply all

Reply to author

Forward

0 new messages