xml - 如何访问 XML 响应的子级 - Google Apps 脚本
问题描述
我正在构建一个函数,它应该获取 XML 响应的元素并将元素的每次迭代存储在电子表格中它自己的行中,直到所有元素都被考虑在内。请参阅下面的代码,我可以将元素标题和值打印到记录器,但不能访问每个值以传输到工作表。请指教。
function testPOST(e) {
var url = "API_URL";
var payload = e;
var options =
{
"method" : "POST",
"payload" : payload,
"followRedirects" : true,
"muteHttpExceptions": true
};
var result = UrlFetchApp.fetch(url, options);
if (result.getResponseCode() == 200) {
// Logger.log(result);
Logger.log(result.getResponseCode() + "\n\n");
}
/*TESTING*/
var od = XmlService.parse(result);
pm = XmlService.getPrettyFormat().format(od);
Logger.log(pm);
/* Preferred Approach */
var ss = SpreadsheetApp.openById("SPREADSHEET_ID").getSheetByName("Output"),
temps = [],
root = XmlService.parse(result).getRootElement().getDescendants(),
temps = root;
for (var [key,val] in temps)
{
if(val == "")
{
continue;
}
else{
// Logger.log("Index Value:"+e.values[key]+"\n");
Logger.log(key+" Value:"+val+ "\n");
}
}
}
这是记录器输出的示例。
[19-07-17 17:05:39:717 EDT] 200
[19-07-17 17:05:39:838 EDT] 0 Value:[Element: <serv:header [Namespace: URL]/>]
[19-07-17 17:05:39:839 EDT] 1 Value:[Element: <serv:response [Namespace: URL]/>]
[19-07-17 17:05:39:840 EDT] 2 Value:[Element: <serv:result [Namespace: URL]/>]
[19-07-17 17:05:39:840 EDT] 3 Value:SUCCESS
[19-07-17 17:05:39:840 EDT] 4 Value:[Element: <serv:gsbStatus [Namespace: URL]/>]
[19-07-17 17:05:39:841 EDT] 5 Value:BACKUP
[19-07-17 17:05:39:841 EDT] 6 Value:[Element: <serv:body [Namespace: URL]/>]
[19-07-17 17:05:39:842 EDT] 7 Value:[Element: <serv:bodyContent [Namespace: URL]/>]
[19-07-17 17:05:39:842 EDT] 8 Value:[Element: <use:user [Namespace: URL]/>]
[19-07-17 17:05:39:842 EDT] 9 Value:[Element: <use:userID [Namespace: URL]/>]
[19-07-17 17:05:39:843 EDT] 10 Value:UID
[19-07-17 17:05:39:843 EDT] 11 Value:[Element: <use:Id [Namespace: URL]/>]
[19-07-17 17:05:39:844 EDT] 12 Value:ID
[19-07-17 17:05:39:844 EDT] 13 Value:[Element: <use:firstName [Namespace: URL]/>]
[19-07-17 17:05:39:844 EDT] 14 Value:NAME
[19-07-17 17:05:39:845 EDT] 15 Value:[Element: <use:lastName [Namespace: URL]/>]
[19-07-17 17:05:39:845 EDT] 16 Value:NAME
[19-07-17 17:05:39:846 EDT] 17 Value:[Element: <use:email [Namespace: URL]/>]
[19-07-17 17:05:39:846 EDT] 18 Value:EMAIL
[19-07-17 17:05:39:846 EDT] 19 Value:[Element: <use:userRoleType [Namespace: URL]/>]
[19-07-17 17:05:39:847 EDT] 20 Value:HOST
[19-07-17 17:05:39:848 EDT] 21 Value:[Element: <use:registrationDate [Namespace: URL]/>]
[19-07-17 17:05:39:848 EDT] 22 Value:DATE
[19-07-17 17:05:39:849 EDT] 23 Value:[Element: <use:active [Namespace: URL]/>]
[19-07-17 17:05:39:849 EDT] 24 Value:ACTIVATED
[19-07-17 17:05:39:850 EDT] 25 Value:[Element: <use:timeZoneID [Namespace: URL]/>]
[19-07-17 17:05:39:850 EDT] 26 Value:11
[19-07-17 17:05:39:851 EDT] 27 Value:[Element: <use:lastLoginTime [Namespace: URL]/>]
**Iterates**
[19-07-17 17:05:39:852 EDT] 28 Value:DATE
[19-07-17 17:05:39:842 EDT] 8 Value:[Element: <use:user [Namespace: URL]/>]
[19-07-17 17:05:39:842 EDT] 9 Value:[Element: <use:userID [Namespace: URL]/>]
[19-07-17 17:05:39:843 EDT] 10 Value:UID
[19-07-17 17:05:39:843 EDT] 11 Value:[Element: <use:Id [Namespace: URL]/>]
[19-07-17 17:05:39:844 EDT] 12 Value:ID
[19-07-17 17:05:39:844 EDT] 13 Value:[Element: <use:firstName [Namespace: URL]/>]
[19-07-17 17:05:39:844 EDT] 14 Value:NAME
[19-07-17 17:05:39:845 EDT] 15 Value:[Element: <use:lastName [Namespace: URL]/>]
[19-07-17 17:05:39:845 EDT] 16 Value:NAME
[19-07-17 17:05:39:846 EDT] 17 Value:[Element: <use:email [Namespace: URL]/>]
[19-07-17 17:05:39:846 EDT] 18 Value:EMAIL
[19-07-17 17:05:39:846 EDT] 19 Value:[Element: <use:userRoleType [Namespace: URL]/>]
[19-07-17 17:05:39:847 EDT] 20 Value:HOST
[19-07-17 17:05:39:848 EDT] 21 Value:[Element: <use:registrationDate [Namespace: URL]/>]
[19-07-17 17:05:39:848 EDT] 22 Value:DATE
[19-07-17 17:05:39:849 EDT] 23 Value:[Element: <use:active [Namespace: URL]/>]
[19-07-17 17:05:39:849 EDT] 24 Value:ACTIVATED
[19-07-17 17:05:39:850 EDT] 25 Value:[Element: <use:timeZoneID [Namespace: URL]/>]
[19-07-17 17:05:39:850 EDT] 26 Value:11
[19-07-17 17:05:39:851 EDT] 27 Value:[Element: <use:lastLoginTime [Namespace: URL]/>]
[19-07-17 17:05:39:852 EDT] 28 Value:DATE
URLFetchApp.fetch 的值
<?xml version="1.0" encoding="UTF-8"?>
<serv:message xmlns:serv="http://www.webex.com/schemas/2002/06/service" xmlns:com="http://www.webex.com/schemas/2002/06/common" xmlns:use="http://www.webex.com/schemas/2002/06/service/user">
<serv:header>
<serv:response>
<serv:result>SUCCESS</serv:result>
<serv:gsbStatus>PRIMARY</serv:gsbStatus>
</serv:response>
</serv:header>
<serv:body>
<serv:bodyContent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="use:lstsummaryUserResponse">
<use:user>
<use:userID>UID</use:userID>
<use:webExId>ID</use:webExId>
<use:firstName>NAME</use:firstName>
<use:lastName>NAME</use:lastName>
<use:email>EMAIL</use:email>
<use:userRoleType>HOST</use:userRoleType>
<use:registrationDate>DATE</use:registrationDate>
<use:active>ACTIVATED</use:active>
<use:timeZoneID>11</use:timeZoneID>
<use:lastLoginTime>DATE</use:lastLoginTime>
</use:user>
<use:user>
<use:userID>UID</use:userID>
<use:webExId>ID</use:webExId>
<use:firstName>NAME</use:firstName>
<use:lastName>NAME</use:lastName>
<use:email>EMAIL</use:email>
<use:userRoleType>SITEADMIN</use:userRoleType>
<use:registrationDate>DATE</use:registrationDate>
<use:active>ACTIVATED</use:active>
<use:timeZoneID>7</use:timeZoneID>
<use:lastLoginTime>DATE</use:lastLoginTime>
</use:user>
<use:matchingRecords>
<serv:total>2</serv:total>
<serv:returned>2</serv:returned>
<serv:startFrom>1</serv:startFrom>
</use:matchingRecords>
</serv:bodyContent>
</serv:body>
</serv:message>
解决方案
- 您希望通过解析 XML 数据将值放入电子表格。
- 在您的示例 XML 数据中,用于放入电子表格的标题和值如下所示。
- 标题:
userID webExId firstName lastName email userRoleType registrationDate active timeZoneID lastLoginTime
- 价值观:
UID ID NAME NAME EMAIL HOST DATE ACTIVATED 11 DATE
UID ID NAME NAME EMAIL SITEADMIN DATE ACTIVATED 7 DATE
- 标题:
我可以像上面那样理解。如果我的理解是正确的,那么这个示例脚本怎么样?请认为这只是几个答案之一。
示例脚本:
在运行脚本之前,请设置电子表格 ID。
function myFunction() {
var id = "###"; // <--- Please set the Spreadsheet ID here.
// This is the sample XML data from your updated question.
var result = '<?xml version="1.0" encoding="UTF-8"?><serv:message xmlns:serv="http://www.webex.com/schemas/2002/06/service" xmlns:com="http://www.webex.com/schemas/2002/06/common" xmlns:use="http://www.webex.com/schemas/2002/06/service/user"><serv:header><serv:response><serv:result>SUCCESS</serv:result><serv:gsbStatus>PRIMARY</serv:gsbStatus></serv:response></serv:header><serv:body><serv:bodyContent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="use:lstsummaryUserResponse"><use:user><use:userID>UID</use:userID><use:webExId>ID</use:webExId><use:firstName>NAME</use:firstName><use:lastName>NAME</use:lastName><use:email>EMAIL</use:email><use:userRoleType>HOST</use:userRoleType><use:registrationDate>DATE</use:registrationDate><use:active>ACTIVATED</use:active><use:timeZoneID>11</use:timeZoneID><use:lastLoginTime>DATE</use:lastLoginTime></use:user><use:user><use:userID>UID</use:userID><use:webExId>ID</use:webExId><use:firstName>NAME</use:firstName><use:lastName>NAME</use:lastName><use:email>EMAIL</use:email><use:userRoleType>SITEADMIN</use:userRoleType><use:registrationDate>DATE</use:registrationDate><use:active>ACTIVATED</use:active><use:timeZoneID>7</use:timeZoneID><use:lastLoginTime>DATE</use:lastLoginTime></use:user><use:matchingRecords><serv:total>2</serv:total><serv:returned>2</serv:returned><serv:startFrom>1</serv:startFrom></use:matchingRecords></serv:bodyContent></serv:body></serv:message>';
var header = [];
var values = [];
var root = XmlService.parse(result).getRootElement();
var c1 = root.getChildren();
for (var i = 0; i < c1.length; i++) {
if (c1[i].getName() == "body") {
var c2 = c1[i].getChildren()[0].getChildren();
for (var j = 0; j < c2.length; j++) {
if (c2[j].getName() == "user") {
var c3 = c2[j].getChildren();
var temp = [];
for (var k = 0; k < c3.length; k++) {
if (j == 0) header.push(c3[k].getName());
temp.push(c3[k].getValue());
}
values.push(temp);
}
}
}
}
values.unshift(header);
var sheet = SpreadsheetApp.openById(id).getSheetByName("Output");
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
笔记:
- 在您的示例 XML 数据中,每个元素的顺序是相同的。这个脚本使用这个。如果顺序是随机的,首先需要声明头部。
参考:
推荐阅读
- wagtail - Wagtail routeablepageurl 因 FieldError 而失败,无法将关键字“类别”解析为字段
- reactjs - Next.js 无法读取未定义的属性“xx”
- git - 添加到 Git 时,VB6 代码行结尾发生了变化?
- php - 当我的图像文件存储在 laravel 项目 storage\app\public\images\002905.jpg 目录中时,从数据库中获取并显示我的图像数据的问题
- r - 将高度(5ft6in)的字符变量转换为以英寸为单位的数字变量?
- java - 另一个 Activity 自行启动
- firebase - FIREBASE - 如何根据数据中单词的出现频率对集合中的数据进行排序
- javascript - Pyodide:ReferenceError:SharedArrayBuffer 未定义
- php - Guzzle 获取带有标头和查询字符串的请求
- python - 在 Azure Data Bricks 中并行运行 python 笔记本