如何在谷歌表格中通过POST API从Sigma-Aldrich获取化学品价格？

这个需求完全可行，核心是正确处理Sigma-Aldrich的API请求，以及解决参数填充和JSON格式问题，以下是具体解决方案：

1. 修复Payload的"Invalid JSON"错误 #

出现这个问题大多是因为复制的Payload格式不规范，或者包含了多余的内容：

• 确保所有键名和字符串值使用双引号（JSON不允许单引号），删除末尾多余的逗号。
• 只保留API必需的字段，比如搜索CAS号的核心Payload结构示例（以DevTools抓取的实际结构为准）：

  {
    "query": "7732-18-5",
    "start": 0,
    "rows": 3,
    "sort": "relevance",
    "filters": {
      "catalogs": ["ALDRICH"]
    }
  }
  

• 复制DevTools中的Payload后，建议用在线JSON校验工具（比如直接在浏览器控制台输入JSON.parse(你的Payload字符串)）验证格式是否正确。

2. 用表格单元格内容填充API参数 #

通过自定义Google Apps Script函数，直接将单元格中的CAS号作为参数传入API请求：

1. 打开谷歌表格，点击「扩展程序」→「Apps Script」
2. 替换默认代码为以下示例（根据实际API地址和返回结构调整）：

function GET_SIGMA_PRICE(casNumber) {
  // 替换为DevTools中抓取的Sigma实际API地址
  const apiUrl = "https://www.sigmaaldrich.com/api/sitecore/Search/GetProducts";
  // 构造包含CAS号的Payload
  const payload = {
    "query": casNumber,
    "start": 0,
    "rows": 3,
    "sort": "relevance",
    "filters": {"catalogs": ["ALDRICH"]}
  };
  // 填入DevTools中抓取的必要请求头，缺少关键Header会被API拒绝
  const requestOptions = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    headers: {
      "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36",
      "X-Requested-With": "XMLHttpRequest"
    }
  };

  try {
    const response = UrlFetchApp.fetch(apiUrl, requestOptions);
    const responseData = JSON.parse(response.getContentText());
    // 提取前3个价格，需根据API实际返回的字段路径调整
    const priceList = [];
    for (let i = 0; i < Math.min(3, responseData.Results?.length || 0); i++) {
      priceList.push(responseData.Results[i].Price?.DisplayValue || "无价格");
    }
    return priceList.length > 0 ? priceList : "未找到匹配产品";
  } catch (error) {
    return `请求错误: ${error.toString()}`;
  }
}

3. 保存脚本后，回到表格，在单元格中输入=GET_SIGMA_PRICE(A2)（A2为CAS号所在单元格）即可自动获取结果。

3. 其他网站的处理 #

对于支持IMPORTXML的网站，直接结合单元格参数使用：

// 示例：某网站通过CAS号搜索后提取前3个价格
=INDEX(IMPORTXML("https://example.com/search?cas="&A2, "//div[@class='product-price']"), 1)
=INDEX(IMPORTXML("https://example.com/search?cas="&A2, "//div[@class='product-price']"), 2)
=INDEX(IMPORTXML("https://example.com/search?cas="&A2, "//div[@class='product-price']"), 3)

注意事项 #

• Sigma的API可能存在反爬机制，如果请求被拦截，需要补充DevTools中抓取的其他Header（比如Cookie，但Cookie可能过期，需定期更新）。
• Google Apps Script的UrlFetchApp有请求频率限制，避免短时间内大量请求导致被封禁。

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