Google Sheet XML导入脚本如何移除<![CDATA[TEXT]]>标签？

你可以添加一个通用的清理函数，专门去除CDATA标签，然后在提取内容后调用这个函数处理结果，就能只保留标签内的文本内容了。

修改后的完整代码 #

function XML() {
  const url = 'xml.url';
  const request = UrlFetchApp.fetch(url);
  const html = request.getContentText();

  // 定义清理CDATA标签的工具函数
  const cleanCdata = (text) => {
    return text.replace(/<!\[CDATA\[(.*?)\]\]>/g, '$1');
  };

  const names = html.match(/<id>(.*?)<\/id>/g)
    .map(name => cleanCdata(/<id>(.*?)<\/id>/g.exec(name)[1]));

  const percentage = html.match(/<price>(.*?)<\/price>/g)
    .map(p => cleanCdata(/<price>(.*?)<\/price>/g.exec(p)[1]));

  const title = html.match(/<title>(.*?)<\/title>/g)
    .map(name => cleanCdata(/<title>(.*?)<\/title>/g.exec(name)[1]));

  const man = html.match(/<manufacturer>(.*?)<\/manufacturer>/g)
    .map(man => cleanCdata(/<manufacturer>(.*?)<\/manufacturer>/g.exec(man)[1]));
    
  const kat = html.match(/<short_description>(.*?)<\/short_description>/g)
    .map(kat => cleanCdata(/<short_description>(.*?)<\/short_description>/g.exec(kat)[1]));    

  const results = [];
  for (let i = 0; i < names.length; i++) {
    results.push([names[i], percentage[i], title[i], man[i], kat[i]]);
  }

  return results;
}

关键说明 #

• 新增的cleanCdata函数用正则匹配<![CDATA[...]]>格式内容，只保留括号内的核心文本。
• 每个字段提取后都调用该函数处理，确保CDATA标签被彻底移除。

更稳妥的替代方案：用XmlService解析XML #

正则处理XML在结构复杂时容易出错，推荐使用Google Apps Script内置的XmlService来解析，它能自动处理CDATA：

function XMLWithXmlService() {
  const url = 'xml.url';
  const request = UrlFetchApp.fetch(url);
  const xmlContent = request.getContentText();
  const document = XmlService.parse(xmlContent);
  const root = document.getRootElement();
  
  // 根据你的XML实际结构修改节点名称，示例假设根节点下是多个<item>节点
  const items = root.getChildren('item');
  const results = items.map(item => {
    // 封装获取节点文本的工具函数
    const getNodeText = (tag) => {
      const element = item.getChild(tag);
      return element ? element.getText() : '';
    };
    return [
      getNodeText('id'),
      getNodeText('price'),
      getNodeText('title'),
      getNodeText('manufacturer'),
      getNodeText('short_description')
    ];
  });
  
  return results;
}

使用XmlService时，只需根据你的XML实际层级调整节点名称即可，getText()方法会自动提取CDATA内的文本，稳定性比正则更高。

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