请求排查Google Script代码：实现指定网站关键词搜索结果写入Google Sheets

Let's break down the issues in your original code and fix them step by step:

Key Issues in the Original Code #

1. Incorrect SitesApp.getSite() Usage: You passed the full URL to getSite(), but this method requires the domain and site name as separate arguments.
2. Looping Over the Wrong Object: You tried to iterate over the parent Site object instead of the search results iterator returned by site.search().
3. Broken Single Quote Escaping: Using a backslash to escape single quotes in the search query doesn't work for Google's query syntax—you need to double the single quotes instead.
4. Duplicate Menu Entry: Both menu items had the same name, making it hard to distinguish between Drive and Sites searches.

Corrected Code #

Fixed websearch() Function #

function websearch() {
  // Prompt user for search term
  var websearchTerm = Browser.inputBox("Enter the keyword to search for:");
  if (!websearchTerm) {
    Browser.msgBox("No search term entered. Exiting.");
    return;
  }

  // Get active spreadsheet and sheet
  var wss = SpreadsheetApp.getActiveSpreadsheet();
  var wsheet = wss.getActiveSheet();

  // Set up results headers
  var theaders = [["Name", "Title", "Web Address"]];
  wsheet.clear();
  wsheet.getRange("A1:C1").setValues(theaders);

  try {
    // Correctly fetch the UMich site (domain + site name)
    var site = SitesApp.getSite("umich.edu", "hb-ua");
    if (!site) {
      Browser.msgBox("Could not access the specified site. Check permissions or site URL.");
      return;
    }

    // Fix single quote escaping for search query
    var escapedTerm = websearchTerm.replace(/'/g, "''");
    var matches = site.search("fullText contains '" + escapedTerm + "'");

    var woutput = [];

    // Loop through search results (not the site itself)
    while (matches.hasNext()) {
      var page = matches.next();
      var wname = page.getName();
      var wtitle = page.getTitle();
      var wurl = page.getUrl();
      woutput.push([wname, wtitle, wurl]);
    }

    // Write results to sheet
    if (woutput.length > 0) {
      wsheet.getRange(2, 1, woutput.length, 3).setValues(woutput);
      Browser.msgBox("Search completed! Found " + woutput.length + " matching pages.");
    } else {
      Browser.msgBox("No pages found containing the keyword.");
    }
  } catch (e) {
    Browser.msgBox("Error occurred: " + e.message);
    console.error(e);
  }
}

Fixed Menu Function #

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Drive search menu
  var searchMenuEntries = [
    {name: "Search in Google Drive", functionName: "search"}
  ];
  
  // Sites search menu (distinct name)
  var websearchMenuEntries = [
    {name: "Search UMich HB-UA Site", functionName: "websearch"}
  ];
  
  ss.addMenu("Search Google Drive", searchMenuEntries);
  ss.addMenu("Search UMich Site", websearchMenuEntries);
}

What Changed? #

• Site Access: SitesApp.getSite("umich.edu", "hb-ua") correctly targets your site using the domain (umich.edu) and site name (hb-ua).
• Search Loop: Now iterates over matches.hasNext() and matches.next() to process actual search results.
• Quote Escaping: replace(/'/g, "''") properly escapes single quotes in the search term to avoid query errors.
• Error Handling: Added try/catch blocks and checks for missing search terms, inaccessible sites, and empty results to provide clear feedback.
• Menu Clarity: Renamed menu entries to avoid confusion between Drive and Sites searches.

Important Notes #

• Ensure you're logged into your UMich Google account when running the script.
• Verify you have view access to the https://sites.google.com/a/umich.edu/hb-ua/ site.
• This script works with classic Google Sites (the version supported by SitesApp). If your site is a new Google Site, SitesApp won't work—you'd need to use the Sites API instead, but that requires additional setup.

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