如何在Google Apps Script中获取HTML表格数量并返回至Google Sheets

Hey there! I see you've already nailed the browser console part—great start. The catch with Google Apps Script is that it runs on Google's servers, not a browser, so we can't use the document object directly like we do in Chrome DevTools. Let's walk through how to adapt your logic to work in Apps Script and get that table count into your sheet.

Step 1: Fetch the Webpage HTML #

First, we'll use UrlFetchApp to grab the static HTML content of the page, just like your existing code starts to do.

Step 2: Count the Tables #

Since we don't have a browser DOM environment, we have two reliable ways to count <table> tags:

Option 1: Use HtmlService to Parse the DOM #

Apps Script's HtmlService lets us create a simulated DOM environment to query elements, which is super similar to what you did in the console. Here's the full code:

function countTablesAndWriteToSheet() {
  // Fetch the webpage HTML
  const url = 'http://allqs.saqa.org.za/showUnitStandard.php?id=7743';
  const htmlContent = UrlFetchApp.fetch(url).getContentText();
  
  // Create a simulated DOM document to work with
  const doc = HtmlService.createHtmlOutput(htmlContent).getContent();
  const tableElements = doc.getElementsByTagName('table');
  
  // Count the tables (NodeList has a length property we can use)
  const tableCount = tableElements.length;
  
  // Write the result to your Google Sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Write to cell A1—adjust the range to wherever you want the result
  sheet.getRange('A1').setValue(`Number of tables: ${tableCount}`);
  
  return tableCount; // Optional: return the count if you want to reuse it elsewhere
}

Option 2: Use a Regular Expression #

If you prefer a lighter approach without full DOM parsing, you can use a regex to match all <table> tags (note: this only works for static HTML; if tables are added dynamically via JS, this won't catch them):

function countTablesWithRegex() {
  const url = 'http://allqs.saqa.org.za/showUnitStandard.php?id=7743';
  const htmlContent = UrlFetchApp.fetch(url).getContentText();
  
  // Match all <table> tags (case-insensitive, accounts for possible attributes like class/id)
  const tableMatches = htmlContent.match(/<table\b[^>]*>/gi);
  const tableCount = tableMatches ? tableMatches.length : 0;
  
  // Write to sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setValue(`Number of tables: ${tableCount}`);
  
  return tableCount;
}

Important Notes #

• Dynamic Content: If the tables on the page load after the initial HTML (via JavaScript), UrlFetchApp will only grab the static initial content—so neither method above will count those dynamic tables. For that, you'd need a headless browser tool like Puppeteer, which requires setting up a Google Cloud Function (since Apps Script doesn't support Puppeteer natively).
• Permissions: Some sites block UrlFetchApp requests with anti-scraping measures. If you get an error, try adding a custom user-agent header to mimic a browser request:

  const options = {
    headers: {
      'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
    }
  };
  const htmlContent = UrlFetchApp.fetch(url, options).getContentText();
  

How to Run It #

1. Open your Google Sheet.
2. Go to Extensions > Apps Script to open the script editor.
3. Paste one of the functions above.
4. Click the run button (▶️), and authorize the script when prompted.
5. Check cell A1—you'll see your table count!

内容的提问来源于stack exchange，提问作者Derek Morgan