Currently, I am diving into Excel Office Script through API requests. Even though I'm not a programmer by trade, I find myself facing challenges that I wouldn't encounter in jQuery. The struggle lies in structuring objects correctly to set values on the page and utilize the object in Power Automate.
The issue seems to stem from how I need to construct my arrays in relation to the "interfaces". I'm still grappling with figuring out the correct structure needed for this task. The error arises at setValues due to incorrect dimensions for the operation.
I'm working on importing JotForm submissions directly into Excel Online, which prevents me from sharing my full code. However, I can provide an edited JSON snippet along with an explanation of my approach.
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();
// Filtering out unnecessary data after retrieving the complete answer in the Json Object from "content"
const result: JSONData[] = json["content"]
const rows: (string | boolean | number)[][] = [];
// Iterating through the object as intended.
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;
}
// Key sections for Power Automate
interface JSONData {
id?:number
created_at?:number
answers?:SUBDATA;
}
interface SUBDATA{
answer?:string;
}
2°) Below is the truncated JSON Object obtained from console.log(rows). Personal information has been redacted for privacy purposes. Notice how the "ANSWERS" section sometimes includes a defined "answer" field while other times it does not. Making this distinction helps maintain the correspondence between questions and answers.
[
{
"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"],
[""],
[""],
]
Lastly, here's a sample of working JQuery code for reference.
$.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");