Excel Online中二维数组生成异常导致setValues()写入失败问题求助

Hey Cedric, let's break down why you're getting a one-dimensional array instead of the two-dimensional structure you need, and fix it up!

问题根源 #

The core issue here is that your answerDetail array is not being reinitialized for each iteration of the outer loop. Chances are you declared answerDetail once outside the for...of loop over Object.entries(result). That means every time you process a new entry in result, you're adding more elements to the same array instead of creating a fresh one. When you push this array into answerArray, you're just adding a reference to that single, growing array—hence the flattened one-dimensional result. Your attempt with push([answerDetail]) just wraps that overfilled array in another layer, leading to the triple-nested mess.

修正方案 #

Move the declaration of answerDetail inside the outer loop so a new empty array is created for each entry in result. Additionally, if you want to combine the basic info from answerID with the answer details (to make a complete row for Excel), you can merge those two arrays into one row before pushing to answerArray.

Here's the adjusted code:

const answerArray = [];

for (const [key, value] of Object.entries(result)) { 
  // 为每个result条目创建全新的答案详情数组
  const answerDetail = [];
  // 收集基础ID/元数据字段
  const answerID = [value.id, value.created_at, value.form_id, value.updated_at];
  
  for (const [subKey, subValue] of Object.entries(value.answers)){ 
    // 将答案（或空字符串）加入当前条目的详情数组
    answerDetail.push(subValue.hasOwnProperty("answer") ? subValue.answer : "");
  } 
  
  // 选项1：合并元数据和答案为单行（适配Excel行结构）
  const fullRow = [...answerID, ...answerDetail];
  answerArray.push(fullRow);
  
  // 选项2：若仅需单独的答案详情作为行数据
  // answerArray.push(answerDetail);
}

为什么这样有效 #

• 把answerDetail的声明放到外层循环内部，确保每个result条目都有专属的数组存储答案，不会再共享和覆盖同一个数组。
• 使用扩展运算符([...answerID, ...answerDetail])将元数据字段和答案详情合并为单行数组，完美对应Excel的一行数据结构。
• 最终生成的answerArray会是标准的二维数组格式[[row1Data], [row2Data]]，可以直接适配Excel的setValues()方法。

用你提供的result结构测试，第一行数据会是：
["50993654", "2021-10-13 08:15:47", "212597363", null, "", ""]
第二行会对应第二个条目的数据——完全符合你写入Excel Online的需求。

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