Google AppScript can't match number of columns in data with number of columns in range-CodePudding

I am having a very similar problem to this question, but when I try to apply their solution, it does not work for my data.

I am trying to write a script that downloads data from an API and puts it into a spreadsheet. The data comes in the form of a nested array [[.,.,.], [.,.,.]] where each sub-array should eventually be one row of data. The number of rows should be the length of the overall array and the number of columns should be the length of one of the nested arrays.

When I try to feed it into the line thisSheet.getRange(1, 1, data.length, data[0].length).setValues(data) I get an error that says the number of columns in my data does not match the number of columns in the range.

When I try to use the push method explained in the linked post, I end up getting a triple nested array that looks like [[[.,.,.], [.,.,.]]].

How can I match up the values so that I can input my data into the spreadsheet?

CodePudding user response：

From your question, I guessed your question as follows.

Your sample input value is as follows. In this sample, the length of each array in the 2-dimensional array is different.

[
  ["sample", "sample", "sample"],
  ["sample", "sample", "sample", "sample", "sample"],
  ["sample", "sample"],
  ["sample", "sample", "sample", "sample"],
]

You want to convert the above array as follows. You want to convert the length of all arrays to the same length.

[
  ["sample", "sample", "sample", "", ""],
  ["sample", "sample", "sample", "sample", "sample"],
  ["sample", "sample", "", "", ""],
  ["sample", "sample", "sample", "sample", ""],
]

If my understanding is correct, how about the following sample script?

Sample script:

const sampleArray = [
  ["sample", "sample", "sample"],
  ["sample", "sample", "sample", "sample", "sample"],
  ["sample", "sample"],
  ["sample", "sample", "sample", "sample"],
];

const maxLen = Math.max(...sampleArray.map(r => r.length));
const res = sampleArray.map(r => [...r, ...Array(maxLen - r.length).fill("")]);
console.log(res);

When this script is run, the above result value is obtained.

Note:

In order to put the values to the Spreadsheet, in this case, I think that fill("") can be also replaced with fill(null).
As the additional information, for example, when "Method: spreadsheets.values.update" of Sheets API is used, even when the length of each array in the 2-dimensional array is different, the values can be put to the spreadsheet using the array.
```
  Sheets.Spreadsheets.Values.update({ values: sampleArray }, spreadsheetId, "Sheet1", { valueInputOption: "USER_ENTERED" });
```

Reference:

map()