4

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?

Alejandro Tamalet
  • 570
  • 3
  • 10
Shiv Yadav
  • 467
  • 2
  • 11

1 Answers1

2

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165