如何用Google Apps Script循环解析API XML并写入谷歌表格？

我通过API获取到如下XML格式的数据：

<?xml version="1.0" encoding="UTF-8"?>
<computers>
  <size>3830</size>
  <computer>
    <id>6</id>
    <name>A user </name>
    <managed>false</managed>
    <username/>
    <model>Computer 1</model>
    <department/>
    <building/>
    <mac_address>78:4F:XXXXX</mac_address>
    <udid>A3E2C80A-XXXXX</udid>
    <serial_number>C02TGXXXX</serial_number>
    <report_date_utc>2022-04-19T13:23:00.404+0000</report_date_utc>
    <report_date_epoch>165037458XXX</report_date_epoch>
  </computer>
  <computer>
    <id>13</id>
    <name>C1MRXXXX</name>
    <managed>true</managed>
    <username>my.user</username>
    <model>Mac</model>
    <department/>
    <building/>
    <mac_address>98:01:XXXXXX</mac_address>
    <udid>A4177C40-0B6B-57CD-932A-XXXXXX</udid>
    <serial_number>C1MRV3XXXXXX</serial_number>
    <report_date_utc>2022-09-21T14:07:19.421+0000</report_date_utc>
    <report_date_epoch>1663769239421</report_date_epoch>
  </computer>
</computers>

我想把其中部分数据导入谷歌表格，但现有脚本只能处理第一个<computer>节点的数据，写入一行。请问怎么修改脚本，才能遍历所有<computer>节点，每个节点的选中项都添加成表格的新行？

现有脚本如下：

function GetMyData() {

//Query jamf 
  var url = 'https://MYSERVER.mydomain.com/computers/subset/basic';
  var myXml = UrlFetchApp.fetch(url, {
    "method": "GET",
    "headers": {
      "Authorization": "Basic ENCODED_BASIC_CREDS_HERE",
      
      "Content-Type": "application/xml"
    },
  }).getContentText();

  var document = XmlService.parse(myXml);
  var root = document.getRootElement();

  //set variables to data from myXml
  var id = root.getChild('computer').getChild('id').getText();
  var name = root.getChild('computer').getChild('name').getText();
  var managed = root.getChild('computer').getChild('managed').getText();
  var username = root.getChild('computer').getChild('username').getText();
  var model = root.getChild('computer').getChild('model').getText();
  var serial_number = root.getChild('computer').getChild('serial_number').getText();

  // Populate sheet with variable data
  SpreadsheetApp.getActiveSheet().getRange(2,1).setValue(id);
  SpreadsheetApp.getActiveSheet().getRange(2,2).setValue(name);
  SpreadsheetApp.getActiveSheet().getRange(2,3).setValue(managed);
  SpreadsheetApp.getActiveSheet().getRange(2,4).setValue(username);  
  SpreadsheetApp.getActiveSheet().getRange(2,5).setValue(model);  
  SpreadsheetApp.getActiveSheet().getRange(2,6).setValue(serial_number);  

  // Logger items
  Logger.log(id);
  Logger.log(name);
  Logger.log(managed);
  Logger.log(username);
  Logger.log(model);
  Logger.log(serial_number);
  
}

function GetMyData() {
  // Query jamf 
  var url = 'https://MYSERVER.mydomain.com/computers/subset/basic';
  var myXml = UrlFetchApp.fetch(url, {
    "method": "GET",
    "headers": {
      "Authorization": "Basic ENCODED_BASIC_CREDS_HERE",
      "Content-Type": "application/xml"
    },
  }).getContentText();

  var document = XmlService.parse(myXml);
  var root = document.getRootElement();
  // 获取所有computer节点
  var computers = root.getChildren('computer');
  // 准备存储所有行数据的数组
  var rows = [];

  // 遍历每个computer节点
  computers.forEach(function(computer) {
    // 提取当前节点的字段值，处理空节点避免报错
    var id = computer.getChild('id')?.getText() || '';
    var name = computer.getChild('name')?.getText() || '';
    var managed = computer.getChild('managed')?.getText() || '';
    var username = computer.getChild('username')?.getText() || '';
    var model = computer.getChild('model')?.getText() || '';
    var serial_number = computer.getChild('serial_number')?.getText() || '';

    // 将当前节点的字段组成一行，加入数组
    rows.push([id, name, managed, username, model, serial_number]);
    // 日志输出当前节点数据
    Logger.log([id, name, managed, username, model, serial_number]);
  });

  // 获取活动表格
  var sheet = SpreadsheetApp.getActiveSheet();
  // 清空原有数据（可选，根据需求决定是否保留）
  sheet.getDataRange().clearContent();
  // 添加表头（如果需要）
  sheet.getRange(1, 1, 1, 6).setValues([["ID", "名称", "是否托管", "用户名", "型号", "序列号"]]);
  // 将所有行数据批量写入表格，从第2行开始
  sheet.getRange(2, 1, rows.length, 6).setValues(rows);
}

• 获取所有节点：用root.getChildren('computer')替代root.getChild('computer')，拿到全部<computer>节点集合
• 遍历处理：通过forEach循环逐个处理每个节点，提取所需字段
• 空值防护：对可能为空的节点（如username）使用?.getText() || ''，避免因空节点引发脚本错误
• 高效写入：将所有行数据存入数组后，用setValues()批量写入表格，比逐个单元格setValue()性能更优
• 可选优化：添加了表头写入和原有数据清空的逻辑，可根据实际需求开启或关闭

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