求助：构建从内部Google Sites到BigQuery的ETL数据管道

核心思路 #

通过Google Apps Script直接解析Sites页面的嵌入HTML内容，提取目标常量数据后批量写入BigQuery，跳过中间Sheet环节避免导出问题。

步骤1：获取Google Sites页面的嵌入HTML内容 #

根据你使用的是经典版还是新版Google Sites，选择对应方式获取页面内容：

经典Google Sites示例代码

function getClassicSitePageHtml(pageUrl) {
  const site = SitesApp.getSiteByUrl("https://你的公司站点根地址.com");
  const page = site.getChildByUrl(pageUrl);
  const htmlWidgets = page.getHtmlWidgets();
  let targetHtml = "";
  
  for (const widget of htmlWidgets) {
    const htmlContent = widget.getHtml();
    if (htmlContent.includes("const heading1")) {
      targetHtml = htmlContent;
      break;
    }
  }
  return targetHtml;
}

新版Google Sites示例代码

新版Sites无直接API访问页面内容，需先将站点发布为内部可见，再抓取页面HTML解析iframe：

function getNewSitePageHtml(pagePublishedUrl) {
  const authToken = "Bearer " + ScriptApp.getOAuthToken();
  // 获取站点页面HTML
  const pageResponse = UrlFetchApp.fetch(pagePublishedUrl, { headers: { Authorization: authToken } });
  const pageHtml = pageResponse.getContentText();
  
  // 提取嵌入iframe的地址
  const iframeMatch = pageHtml.match(/<iframe[^>]+src="([^"]+)"/);
  if (!iframeMatch) return "";
  
  // 获取iframe内的HTML内容
  const iframeResponse = UrlFetchApp.fetch(iframeMatch[1], { headers: { Authorization: authToken } });
  return iframeResponse.getContentText();
}

步骤2：解析HTML中的JavaScript常量 #

用正则匹配提取heading1和heading2的内容，转换为可处理的JSON格式：

function parseHtmlToData(htmlContent) {
  // 匹配heading1对象
  const heading1Match = htmlContent.match(/const heading1 = ({[^}]+});/s);
  // 匹配heading2数组
  const heading2Match = htmlContent.match(/const heading2 = (\[[^\]]+\]);/s);
  
  if (!heading1Match || !heading2Match) return null;
  
  // 处理转义引号，转为标准JSON格式
  const heading1Str = heading1Match[1].replace(/"/g, '"');
  const heading2Str = heading2Match[1].replace(/"/g, '"');
  
  try {
    return {
      ...JSON.parse(heading1Str),
      heading2: JSON.parse(heading2Str) // 保持数组格式，BigQuery支持重复字段
    };
  } catch (e) {
    console.error("解析失败:", e);
    return null;
  }
}

步骤3：批量写入BigQuery #

使用Apps Script的BigQuery服务将解析后的数据批量插入目标表：

function writeToBigQuery(dataArray) {
  const projectId = "你的BigQuery项目ID";
  const datasetId = "你的数据集ID";
  const tableId = "你的目标表ID";

  // 转换为BigQuery接受的行结构
  const rows = dataArray.map(data => ({
    json: data,
    insertId: Utilities.getUuid() // 避免重复插入
  }));

  // 提交批量写入任务
  BigQuery.Jobs.insert(
    {
      configuration: {
        load: {
          destinationTable: { projectId, datasetId, tableId },
          sourceFormat: "NEWLINE_DELIMITED_JSON",
          writeDisposition: "WRITE_APPEND" // 可选WRITE_TRUNCATE覆盖全表
        }
      }
    },
    projectId,
    Utilities.newBlob(JSON.stringify(rows.map(r => r.json)).split("\n"))
  );
}

步骤4：整合流程并定时执行 #

遍历所有客户页面，批量处理数据并写入BigQuery：

function runETLPipeline() {
  // 获取所有客户页面URL（可从Sheet读取或通过SitesAPI遍历子页面）
  const pageUrls = getAllClientPageUrls();
  const allData = [];

  for (const url of pageUrls) {
    // 根据站点版本选择对应函数
    const html = getClassicSitePageHtml(url); 
    if (!html) continue;
    
    const data = parseHtmlToData(html);
    if (data) allData.push(data);
  }

  if (allData.length > 0) {
    writeToBigQuery(allData);
    console.log(`成功写入${allData.length}条数据`);
  }
}

// 辅助函数：遍历站点获取所有客户页面URL
function getAllClientPageUrls() {
  const site = SitesApp.getSiteByUrl("https://你的公司站点根地址.com");
  const clientFolder = site.getChildByName("客户区域");
  return clientFolder.getChildren().map(page => page.getUrl());
}

注意事项 #

1. 权限配置：在脚本编辑器中授权Sites和BigQuery的访问权限，确保账号有对应资源的操作权限。
2. 表结构匹配：提前在BigQuery创建对应表，字段类型需与解析数据匹配（如goLiveDate设为DATE类型）。
3. 性能优化：处理1500-3000个页面时，建议分批次处理（每100页一批），避免脚本超时。
4. 新版Sites限制：必须确保站点已发布，且脚本有权限访问发布后的内容。

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