如何在OfficeScript中读取对象中的对象- interfaces和setValues() &

Question

我目前正在通过API请求学习Excel Office Script。当然我不是程序员。

这里，我在Jquery中很容易做的事情让我在Excel Office Script中头痛。我似乎无法正确地构建对象，以便在页面上设置值，并在Power automation中使用对象。

最后，我的错误与我必须根据"接口"构建数组的方式有关。我还是想不明白，怎么组织它。错误出现在setValues的末尾——setValues的尺寸不正确。

为了理解目的:我请求直接提交一个JotForm到Excel Online，所以我不能完全给出我的代码。但是我可以展示一个编辑过的Json并解释我的过程。

1°Excel office Script

async function main(workbook: ExcelScript.Workbook): Promise<void> {

const workSheet = workbook.getActiveWorksheet();
let fetchResult = await fetch(myApiQuery)

let json: JSONData[] = await fetchResult.json();

// From here, I don't need the complete answer, I filter the Json Object from ["content"]
const result: JSONData[] = json["content"]
const rows: (string | boolean | number)[][] = [];
// FROM HERE, I want to iterate through the object.
// Basically, the structure of my code should work like this : 
// iterate through "CONTENT" -> Iterate THROUGH "ANSWERS" -> IS "answer" UNDEFINED ? (if UNDEFINED : return empty, if not return the answer). 
//See JSON below.
for (const [key, value] of Object.entries(result)) {

rows.push([value["id"],value["created_at"],value["answers"]])

for (const [subKey, subValue] of Object.entries(value["answers")) {
if (typeof subValue["answer"] !== "undefined") {
rows.push([subValue["answer"]])
}
else {
rows.push([""])
}
}

console.log(rows);
const targetRange = workSheet.getRange('A2').getResizedRange(rows.length - 1, rows[0].length - 1);
targetRange.setValues(rows);

return;

}

// This is what I understood will be important for Power Automate
interface JSONData {
id?:number
created_at?:number
answers?:SUBDATA;
}
interface SUBDATA{
answer?:string;

}

2°)这是JSON对象从我的控制台。log(控制台。log(行))。我编辑了个人信息，并从数百行中删除了它。如你所见，在"answer "(复数)，有时"answer";(单数)有定义，有时没有。我需要能够有所作为，如果未定义，则返回空，以保持问题和答案之间的对应关系。

[
{
"id": "---",
"form_id": "---",
"ip": "---",
"created_at": "2021-09-18 07:39:14",
"updated_at": null,
"answers": {
"1": {
"name": "vousAvez",
"order": "6",
"text": "QUESTION",
"type": "control_head"
},
"2": {
"name": "email",
"order": "7",
"text": "Email",
"type": "control_email",
"answer": "email Address" }
}
],
[""],
[""],
[""],
["emailAdress"],
["Name"],
["FristName"],
[""],
[""],
]

下面是一个在JQUERY中工作的例子

$.each(responseText["content"], function (index, element) {
items.push("<br/><span style='color:red'>" + element["id"] + " - " + element["created_at"] + "</span><br/><br/>");
$.each(element["answers"], function (subIndex, subElement) {
if (typeof subElement["answer"] !== "undefined") {
items.push("<li id='" + subIndex + "'>" + subElement["name"] + "<span style='color:blue'> " + subElement["answer"] + "</span></li>");
}
else {
items.push("<li id='" + subIndex + "'>" + subElement["name"] + ": </li > ");
}
items.push('<br/>');
})
})
$("<ul/>", {
"class": "my-new-list",
html: items.join("")
}).appendTo("body");

Answer 1

在TypeScript Playground中试试这个例子

我建议用你的数据替换dataArrayInput，并在TypeScript Playground中使用它，直到printDataAnswers方法像你期望的那样工作并返回你正在寻找的值。然后你可以把它复制到你现有的逻辑中。

// define data interface
interface Data {
id: string;
form_id: string;
ip: string;
created_at: string;
updated_at: string | null;
answers: {
[key: string]: {
name: string;
order: string;
text: string;
type: string;
answer?: string;
}
}
}
// example of static data
const dataArrayInput: Data[] = [
{
"id": "---",
"form_id": "---",
"ip": "---",
"created_at": "2021-09-18 07:39:14",
"updated_at": null,
"answers": {
"1": {
"name": "vousAvez",
"order": "6",
"text": "QUESTION",
"type": "control_head"
},
"2": {
"name": "email",
"order": "7",
"text": "Email",
"type": "control_email",
"answer": "email Address" }
}
},
];

/**
* Iterates through all answers in an array of data and returns and array all the string answers.
*/
function printDataAnswers(dataArray: Data[]) {
// iterate through data and pull out answers
const allAnswers: string[] = []
dataArray.forEach((data) => {
const {answers} = data; // destructure object
if (answers) {
// iterate through answer key and values
for (const [key, value] of Object.entries(answers)) {
console.log(key);
const {answer} = value; // destructure object
const answerOrEmpty = answer || "";
console.log(answerOrEmpty);        
allAnswers.push(answerOrEmpty);    
}
}
});
return allAnswers;
}
const answerArray = printDataAnswers(dataArrayInput);
console.log(answerArray);