英文:
Those Columns are out of bound error at autoResizeColumns method
问题
我有一个2D数组数据要添加到工作表中。我有一个脚本可以在Google应用脚本中添加列。我使用setValue方法在Google表格中添加数据。我有长度为784的数据要添加到表格中。之后，我试图根据文本调整列的大小。当我有长度为500的数据时，autoSizeColumns方法正常工作，但当我有长度为784的数据时，它显示错误"Those columns are out of bounds"。我不知道为什么会出现这个错误。脚本在将数据打印到工作表时运行正常，但在resizeColumns时出错。
添加数据到工作表的代码：
const lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
SpreadsheetApp.getActiveSheet().getRange(lastRow + 1, 1, arr.length, arr[0].length).setValues(arr);
SpreadsheetApp.getActiveSheet().autoResizeColumns(1, arr.length);
有人可以指导我吗？
英文:
I have 2D array data to add to the sheet. I have a script that will add columns in the Google app script. I used the setValue method for adding data in the Google sheet. I had data of length 784 to add to the sheet. After that, I'm trying to resize the column as per the text. When I have data of length 500 at that time autoSizeColumns method works fine but when I had data of length 784 at that time it's showing the error "Those columns are out of bounds". I don't know why this error is coming. The script working fine for printing data to the sheet but at resizeColumns it's giving an error.
Code for adding data into sheet.
const lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
SpreadsheetApp.getActiveSheet().getRange(lastRow + 1, 1, arr.length, arr[0].length).setValues(arr);
SpreadsheetApp.getActiveSheet().autoResizeColumns(1, arr.length);
Can anyone Guide me on this?
答案1
得分: 2
autoResizeColumns(startColumn, numColumns) 方法的参数分别是 startColumn 和 numColumns。在你的脚本中，看起来你是这样使用它的：
SpreadsheetApp.getActiveSheet().autoResizeColumns(1, arr.length);
如果 arr 是 2D 数组数据，且 data of length 784 表示行数，那么 autoResizeColumns(startColumn, numColumns) 中的 startColumn 和 numColumns 分别为 1 和 784。当你的活动工作表中的最大列小于 784 时，会出现像 Exception: Those columns are out of bounds. 这样的错误。我认为这可能是你当前问题的原因，即 当我有长度为 784 的数据时，autoSizeColumns 方法可以正常工作，但当我有长度为 784 的数据时，它会显示错误"Those columns are out of bounds"。
例如，如果你想避免错误，可以考虑以下修改。在这个修改中，最大列被用作 numColumns。
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
sheet.getRange(lastRow + 1, 1, arr.length, arr[0].length).setValues(arr);
sheet.autoResizeColumns(1, sheet.getMaxColumns());
或者，如果你必须使用 arr.length 作为 numColumns，可以考虑以下修改：
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
sheet.getRange(lastRow + 1, 1, arr.length, arr[0].length).setValues(arr);
const maxColumn = sheet.getMaxColumns();
const arrLength = arr.length;
if (arrLength <= maxColumn) {
sheet.autoResizeColumns(1, arrLength);
} else {
sheet.insertColumnsAfter(maxColumn, arrLength - maxColumn).autoResizeColumns(1, arrLength);
}
参考链接：
英文:
The arguments of autoResizeColumns(startColumn, numColumns) are startColumn, numColumns. Ref In your script, it seems that you are using it as follows.
SpreadsheetApp.getActiveSheet().autoResizeColumns(1, arr.length);
If arr is 2D array data of I have 2D array data to add to the sheet. and data of length 784 is the number of rows, startColumn and numColumns of autoResizeColumns(startColumn, numColumns) are 1 and 784, respectively. When the maximum column in your active sheet is less than 784, an error like Exception: Those columns are out of bounds. occurs. I thought that this might be the reason for your current issue of When I have data of length 500 at that time autoSizeColumns method works fine but when I had data of length 784 at that time it's showing the error "Those columns are out of bounds"..
For example, if you want to avoid the error, how about the following modification? In this modification, the maximum column is used as numColumns.
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
sheet.getRange(lastRow + 1, 1, arr.length, arr[0].length).setValues(arr);
sheet.autoResizeColumns(1, sheet.getMaxColumns());
Or, if you are required to use arr.length as numColumns, how about the following modification?
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
sheet.getRange(lastRow + 1, 1, arr.length, arr[0].length).setValues(arr);
const maxColumn = sheet.getMaxColumns();
const arrLength = arr.length;
if (arrLength <= maxColumn) {
sheet.autoResizeColumns(1, arrLength);
} else {
sheet.insertColumnsAfter(maxColumn, arrLength - maxColumn).autoResizeColumns(1, arrLength);
}
Reference:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论