0

I currenty use Excel Office Script to get a response from Jotform API.

In my Json response, on the first level of my Array, I get some informations like "Submission ID" and "timestamp from Submission", then I have a Key "Answers" with all the answers from that Submission (could be 150 anwers). So it's a 2D array.

I'm having trouble being able use setValues after all the iterations and write all the answers in Excel in one shot.

Here is my iteration where rows will be "Submission ID + Timestamp", then next to it, subRows which will have all the answers for that submission, each answers being one Excel row each

    let rows: (string | boolean | number)[][] = [];
    let subRows: (string | boolean | number)[][] = [];
    
    for (const [key, value] of Object.entries(result)) {
        rows.push([value["id"], value["created_at"]]);
        for (const [subKey, subValue] of Object.entries(value["answers"])) {
           
                if (subValue.hasOwnProperty("answer")) {
                    subRows.push([checkAnswer(subValue["answer"])]);
                }
               
            
        }
       
    }

    // This function is important because sometimes I have several answers in one answer, so I join it when necessary.
    function checkAnswer(answer: string[]) {
        if (answer.length > 1) {
            return (typeof answer == "string" ? answer : answer.join("||"));
        }
        return answer;
    }
 // Here are the methods I use to write the result on my Excel file. The first one is great, it is one Excel row/ one result.
    
    const targetRange = workSheet.getRange('A5').getResizedRange(rows.length - 1, rows[0].length - 1);
    targetRange.setValues(rows);


    // I TRANSPOSE so that the subROWS get in line, not vertically. 
    subRows = subRows[0].map((_, colIndex) => subRows.map(row => row[colIndex]));

    const subTargetRange = workSheet.getRangeByIndexes(4, 2, subRows.length, subRows[0].length).setValues(subRows);

My problem is that all the subrows (all the answers) are being written in one line, there is not one answer/one row.

I could use setValues inside the iteration, but I always try not too as it slows down the work.

The best being able to make one big array and do everything in one setValues.

Any idea ?

Thank you very much,

Cedric

Improve this question

1 Answer 1

Reset to default
2

You can definitely create one array and write it out at once using SetValues. Where it gets tricky is that SetValues expects the 2-d array in a certain structure. So you can't just supply any 2d array and expect it to work with SetValues. If you use GetValues on the range and log it to the console, it will let you know of the structure it expects it in. Please see a few examples below. The stringified values are just some random data I put into a worksheet:

function main(workbook: ExcelScript.Workbook) {
  let sheet: ExcelScript.Worksheet = workbook.getWorksheet("Sheet1");
  let rang1: ExcelScript.Range = sheet.getRange("A1:A5");
  console.log(rang1.getValues()); //(5) [Array(1), Array(1), Array(1), Array(1), Array(1)]
  console.log(JSON.stringify(rang1.getValues())); //[["A"],[0],[1],[2],[3]]

  let rang2: ExcelScript.Range = sheet.getRange("A1:B5");
  console.log(rang2.getValues()); //(5) [Array(2), Array(2), Array(2), Array(2), Array(2)]
  console.log(JSON.stringify(rang2.getValues())); //[["A","B"],[0,1],[1,2],[2,3],[3,4]]
  
  let rang3: ExcelScript.Range = sheet.getRange("D1:H1");
  console.log(rang3.getValues()); //(1) [Array(5)]
  console.log(JSON.stringify(rang3.getValues())); //[["A","B","C","D","E"]]

  let rang4: ExcelScript.Range = sheet.getRange("D1:H2");
  console.log(rang4.getValues()); //(2) [Array(5), Array(5)]
  console.log(JSON.stringify(rang4.getValues())); //[["A","B","C","D","E"],["B","C","D","E","F"]]
}

If the array does not come in the necessary structure, one thing you can try to do is write the array values to a 1d array. From there, you'd need to create a 2d array in the same structure as expected by SetValues for the range.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for you comment !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.