Google Script+HTML横向标签页获取谷歌表格数据失败求助

阿华AIGC实验室

2026-5-11

Let's break down the issues in your code and fix them step by step to get your data fetching working correctly:

Key Problems in Your Current Code

1. Google Apps Script (`fetchRushIv` Function)

Early return breaks loop execution: Your else { return 0; } line stops the loop immediately when it hits a row that doesn't match the office name. This means if the first data row isn't your target office, the function quits without checking the rest of the sheet.
Incorrect HTML entity usage: Using < and > instead of < and > will render text characters, not actual HTML tags, so your table won't display properly.
Invalid range calculation: getRange(5, 1, currSheet.getLastRow(), ...) includes all rows from row 5 to the last row, which adds empty rows at the end. You need to subtract the first 4 rows from the total row count.
No error handling: There's no check for missing sheets or headers, which makes debugging hard when things go wrong.

2. HTML/Client-Side Code

Syntax error in onclick handlers: You're missing semicolons between the getData and google.script.run calls in your button onclick events. This causes JavaScript to fail silently.
Mismatched tab content structure: Your getData function tries to show a div with the city name as its ID, but those divs don't exist in your HTML.
Unparameterized initial API call: The initial google.script.run.fetchRushIv() call sends no office name, which triggers an unnecessary failure.
Invalid script tag: <script> src = "main.js"; </script> is not the correct way to load an external script (and can be removed if you don't have a main.js file).

Fixed Code

Modified Google Apps Script

function doGet(){ 
  return HtmlService.createHtmlOutputFromFile('index'); 
}

function fetchRushIv(officeName){
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const currSheet = spreadSheet.getSheetByName('OG_Database');
  
  // Handle missing sheet
  if (!currSheet) {
    return "<p>Error: Sheet 'OG_Database' not found!</p>";
  }
  
  // Get data starting from row 5, correct range size
  const totalRows = currSheet.getLastRow() - 4;
  const data = currSheet.getRange(5, 1, totalRows, currSheet.getLastColumn()).getValues();
  
  let table = "<table id='RushIV' border='1' style='width:100%; padding:10px;'>";
  let headerFound = false;

  for(let i = 0; i < data.length; i++){
    const rowValue = data[i][0];
    
    // Add header row when we find "Office Name"
    if(rowValue === "Office Name"){
      table += '<tr>';
      data[i].forEach(cell => table += `<th>${cell}</th>`);
      table += '</tr>';
      headerFound = true;
    }
    // Add data row only after header is found and matches target office
    else if(headerFound && rowValue === officeName){
      table += '<tr>';
      data[i].forEach(cell => table += `<td>${cell}</td>`);
      table += '</tr>';
    }
  }

  // Handle edge cases (no header, no data found)
  if (!headerFound) {
    table = "<p>Error: Header row 'Office Name' not found!</p>";
  } else if (table === "<table id='RushIV' border='1' style='width:100%; padding:10px;'>") {
    table = `<p>No data found for office: ${officeName}</p>`;
  } else {
    table += "</table>";
  }

  return table;
}

Modified HTML Code

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<base target="_top" />
<style>
body { font-family: Arial; }
/* Tab styles */
.tab {
 overflow: hidden;
 border: 1px solid #ccc;
 background-color: #f1f1f1;
}
.tab button {
 background-color: inherit;
 float: left;
 border: none;
 outline: none;
 cursor: pointer;
 padding: 14px 16px;
 transition: 0.3s;
 font-size: 17px;
}
.tab button:hover { background-color: #ddd; }
.tab button.active { background-color: #ccc; }
/* Tab content styles */
.tabcontent {
 display: none;
 padding: 6px 12px;
 -webkit-animation: fadeEffect 1s;
 animation: fadeEffect 1s;
}
@-webkit-keyframes fadeEffect {
 from { opacity: 0; }
 to { opacity: 1; }
}
@keyframes fadeEffect {
 from { opacity: 0; }
 to { opacity: 1; }
}
/* Layout styles */
* { box-sizing: border-box; }
body { font-family: Arial, Helvetica, sans-serif; }
header {
 background-color: #008080;
 padding: 30px;
 text-align: center;
 font-size: 35px;
 color: white;
}
.dataArea {
 float: left;
 background-color: #fff8dc;
 width: 80%;
 padding: 20px;
 color: #000;
}
footer {
 float: left;
 width: 100%;
 background-color: #008080;
 padding: 15px;
 text-align: center;
 color: white;
}
@media (max-width: 600px) {
 nav, .dataArea { width: 100%; height: auto; }
}
</style>
<script>
// Load default office data on page load
window.onload = () => {
 document.querySelector('.tablinks').click();
};

function getData(evt, cityName) {
 // Hide all tab content
 const tabcontent = document.getElementsByClassName("tabcontent");
 for (let i = 0; i < tabcontent.length; i++) {
 tabcontent[i].style.display = "none";
 }
 // Remove active class from all buttons
 const tablinks = document.getElementsByClassName("tablinks");
 for (let i = 0; i < tablinks.length; i++) {
 tablinks[i].className = tablinks[i].className.replace(" active", "");
 }
 // Show active content and mark button as active
 document.getElementById("content").style.display = "block";
 evt.currentTarget.className += " active";
}

function displayData(table) {
 document.getElementById("customers").innerHTML = table;
}

function failed(e) {
 alert(`Error: ${e.message || e}`);
}
</script>
</head>
<body>
<header><h4>Sample Data Fetching</h4></header>
<div class="tab">
<button class="tablinks" onclick="getData(event,'Beaumont'); google.script.run.withFailureHandler(failed).withSuccessHandler(displayData).fetchRushIv('Beaumont')">
 Beaumont
</button>
<button class="tablinks" onclick="getData(event,'Crosby'); google.script.run.withFailureHandler(failed).withSuccessHandler(displayData).fetchRushIv('Crosby')">
 Crosby
</button>
<button class="tablinks" onclick="getData(event,'Splendora'); google.script.run.withFailureHandler(failed).withSuccessHandler(displayData).fetchRushIv('Splendora')">
 Splendora
</button>
</div>
<div id="content" class="tabcontent">
<h3>The data Table for the Selected Office</h3>
<table id="customers" style="width: 100%;"></table>
</div>
</body>
</html>

What Changed?

GAS: Fixed loop logic to keep searching for data, added error handling for missing sheets/headers, corrected range calculation, and used proper HTML tags instead of entities.
HTML: Fixed onclick syntax errors, simplified tab content to use a single display area, added default data load on page load, and cleaned up invalid tags/styles.

内容的提问来源于stack exchange，提问作者Dewan Arun Singh