求助：如何在Google Sheets中用JS实现订舱截关日期自动爬取

我需要用JavaScript结合XPath实现每日多次自动查询出口订舱的截关日期，并将信息记录到Google Sheets。操作流程是：登录铁路公司网站，导航后输入订舱号、从下拉菜单选择客户，提交后获取截关日期。我已经用Chrome扩展XPath Finder获取了元素路径，但编写的代码始终无法运行；试过部分爬虫工具，但大多针对亚马逊类网页，不符合需求。希望用免费的JavaScript实现该功能，方便后续扩展获取承运商网站的其他订舱信息。

以下是我编写的代码：

function updateBookingDates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var lastRow = sheet.getLastRow();

  for (var i = 2; i <= lastRow; i++) {
    var bookingNumber = sheet.getRange(i, 2).getValue();

    var url = "https://access.nwcontainer.com/";

    // Login to the website

    var loginPage = UrlFetchApp.fetch(url);

    var loginHtml = loginPage.getContentText();

    var loginDoc = XmlService.parse(loginHtml);

    var loginRoot = loginDoc.getRootElement();

    var username = loginRoot
      .getElement("input", { id: "ContentPlaceHolder1_pnlLogin_UserName" })
      .getAttribute("value")
      .getValue();

    var password = loginRoot
      .getElement("input", { id: "ContentPlaceHolder1_pnlLogin_Password" })
      .getAttribute("value")
      .getValue();

    var formData = {
      ctl00$ContentPlaceHolder1$pnlLogin$UserName: username,

      ctl00$ContentPlaceHolder1$pnlLogin$Password: password,

      __VIEWSTATE: loginRoot
        .getElement("input", { id: "__VIEWSTATE" })
        .getAttribute("value")
        .getValue(),

      __EVENTVALIDATION: loginRoot
        .getElement("input", { id: "__EVENTVALIDATION" })
        .getAttribute("value")
        .getValue(),

      __VIEWSTATEGENERATOR: loginRoot
        .getElement("input", { id: "__VIEWSTATEGENERATOR" })
        .getAttribute("value")
        .getValue(),

      ctl00$ContentPlaceHolder1$pnlLogin$btnLogin: "Log In",
    };

    var options = {
      method: "post",

      payload: formData,

      followRedirects: false,
    };

    var loginResponse = UrlFetchApp.fetch(url, options);

    // Search for the booking

    var searchUrl = "https://access.nwcontainer.com/LookupBooking.aspx";

    var searchPayload = {
      ctl00$ContentPlaceHolder1$txtBooking: bookingNumber,

      ctl00$ContentPlaceHolder1$ddlCustomer: "",

      ctl00$ContentPlaceHolder1$btnSearch: "Search",
    };

    var searchOptions = {
      method: "post",

      payload: searchPayload,

      followRedirects: false,

      muteHttpExceptions: true,
    };

    var searchResponse = UrlFetchApp.fetch(searchUrl, searchOptions);

    var searchHtml = searchResponse.getContentText();

    // Parse the search results to find the cut-off date

    var searchDoc = XmlService.parse(searchHtml);

    var searchRoot = searchDoc.getRootElement();

    var cutoff = searchRoot
      .getElement("span", { id: "ContentPlaceHolder1_lblOffHireDate" })
      .getText();

    // Update the sheet with the cutoff date

    sheet.getRange(i, 6).setValue(cutoff);
  }
}

你的代码存在几个核心问题：

• XmlService无法解析HTML：XmlService是用于XML解析的，网页HTML存在非闭合标签等不规范结构，无法被正常解析。
• 登录逻辑错误：代码试图从登录页面读取账号密码的value属性，这完全错误——账号密码应该是你自己的登录信息，页面输入框初始value为空。
• 未保持会话状态：登录后需要传递Cookie才能维持登录状态，原代码没有处理Cookie。
• 元素查找方式错误：XmlService的getElement方法不支持按id属性查找元素，需要用正则或HTML解析工具提取参数。
• 客户下拉菜单未赋值：搜索时ddlCustomer设为空，不符合网站提交要求，需要填入实际客户选项的value值。

修复后的代码如下：

function updateBookingDates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  // 替换为你的实际登录信息
  const USERNAME = "你的登录账号";
  const PASSWORD = "你的登录密码";
  // 替换为客户下拉菜单对应的value值（可通过浏览器开发者工具查看）
  const CUSTOMER_VALUE = "客户选项value";
  let sessionCookie = "";

  // 1. 获取登录页面的表单参数
  const loginPageUrl = "https://access.nwcontainer.com/";
  const loginPageRes = UrlFetchApp.fetch(loginPageUrl);
  const loginHtml = loginPageRes.getContentText();
  
  const viewState = extractInputValue(loginHtml, "__VIEWSTATE");
  const eventValidation = extractInputValue(loginHtml, "__EVENTVALIDATION");
  const viewStateGenerator = extractInputValue(loginHtml, "__VIEWSTATEGENERATOR");

  // 2. 提交登录请求，获取会话Cookie
  const loginFormData = {
    "ctl00$ContentPlaceHolder1$pnlLogin$UserName": USERNAME,
    "ctl00$ContentPlaceHolder1$pnlLogin$Password": PASSWORD,
    "__VIEWSTATE": viewState,
    "__EVENTVALIDATION": eventValidation,
    "__VIEWSTATEGENERATOR": viewStateGenerator,
    "ctl00$ContentPlaceHolder1$pnlLogin$btnLogin": "Log In"
  };

  const loginOptions = {
    method: "post",
    payload: loginFormData,
    followRedirects: false,
    muteHttpExceptions: true
  };

  const loginRes = UrlFetchApp.fetch(loginPageUrl, loginOptions);
  const headers = loginRes.getAllHeaders();
  if (headers["Set-Cookie"]) {
    sessionCookie = headers["Set-Cookie"].split(";")[0];
  }

  if (!sessionCookie) {
    sheet.getRange(2, 7).setValue("登录失败：未获取会话Cookie");
    return;
  }

  // 3. 遍历表格查询订舱信息
  for (var i = 2; i <= lastRow; i++) {
    var bookingNumber = sheet.getRange(i, 2).getValue();
    if (!bookingNumber) continue;

    // 获取搜索页面的表单参数（ASP.NET页面每次请求需更新参数）
    const searchPageUrl = "https://access.nwcontainer.com/LookupBooking.aspx";
    const searchPageOptions = {
      headers: { "Cookie": sessionCookie },
      muteHttpExceptions: true
    };
    const searchPageRes = UrlFetchApp.fetch(searchPageUrl, searchPageOptions);
    const searchHtml = searchPageRes.getContentText();
    
    const searchViewState = extractInputValue(searchHtml, "__VIEWSTATE");
    const searchEventValidation = extractInputValue(searchHtml, "__EVENTVALIDATION");
    const searchViewStateGenerator = extractInputValue(searchHtml, "__VIEWSTATEGENERATOR");

    // 提交搜索请求
    const searchPayload = {
      "ctl00$ContentPlaceHolder1$txtBooking": bookingNumber,
      "ctl00$ContentPlaceHolder1$ddlCustomer": CUSTOMER_VALUE,
      "__VIEWSTATE": searchViewState,
      "__EVENTVALIDATION": searchEventValidation,
      "__VIEWSTATEGENERATOR": searchViewStateGenerator,
      "ctl00$ContentPlaceHolder1$btnSearch": "Search"
    };

    const searchOptions = {
      method: "post",
      payload: searchPayload,
      headers: { "Cookie": sessionCookie },
      muteHttpExceptions: true
    };

    const searchRes = UrlFetchApp.fetch(searchPageUrl, searchOptions);
    const resultHtml = searchRes.getContentText();

    // 提取截关日期
    const cutoffDate = extractSpanText(resultHtml, "ContentPlaceHolder1_lblOffHireDate");
    
    // 更新表格
    sheet.getRange(i, 6).setValue(cutoffDate || "未找到截关日期");
    // 添加延迟避免请求过于频繁被封禁
    Utilities.sleep(1000);
  }
}

// 辅助函数：提取指定id的input标签value值
function extractInputValue(html, inputId) {
  const regex = new RegExp(`<input[^>]+id=["']${inputId}["'][^>]+value=["']([^"']+)["']`, "i");
  const match = html.match(regex);
  return match ? match[1] : "";
}

// 辅助函数：提取指定id的span标签文本内容
function extractSpanText(html, spanId) {
  const regex = new RegExp(`<span[^>]+id=["']${spanId}["'][^>]*>([^<]+)</span>`, "i");
  const match = html.match(regex);
  return match ? match[1].trim() : "";
}

1. 替换代码中的你的登录账号、你的登录密码为实际信息。
2. 通过浏览器开发者工具查看客户下拉菜单的option标签，获取对应的value值，替换客户选项value。
3. 代码添加了1秒延迟，避免请求频率过高被网站限制。
4. 若运行报错，可通过Google Apps Script的日志查看具体错误信息。

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