I got this code implemented in a bigger function. Basically it searches for hidden columns to hide them in a new formed sheet that will be exported as a pdf. What happens is that at every hidden column above 30 it just stops, when columnHidden
is true
. Under that it just works fine.
The last to line of the logger and the error:
Info start column: 33, left: 58 , Ende: 85 , Checking column: 33, isHidden: false
Error Exception: Those columns are out of bounds.
So they cant be out of bounds, cause it is right in the middle of it.
const startColumn = range.getColumn(); // The starting column of the range (e.g., "E" -> 5)
const numColumns = range.getNumColumns(); // The number of columns in the range
for (let i = 0; i < numColumns; i++) {
const originalColumn = startColumn + i; // Get the original column number
const columnHidden = sheet.isColumnHiddenByUser(originalColumn); // Check if the column is hidden in the original sheet
const ende = tempSheet.getLastColumn();
// If the column is hidden in the original sheet, hide the corresponding column in the temporary sheet
if (columnHidden) {
tempSheet.hideColumns(i+1); // Hide the corresponding column in the temporary sheet
}
Logger.log(`start column: ${startColumn + i}, left: ${numColumns-i} , Ende: ${ende} , Checking column: ${originalColumn}, isHidden: ${columnHidden}`) ;
}
I searched for merged cells, changed the index, copy pasted. Googled it, but its not a common issue. Asked chatgpt, made a test function to see if HideColumns
has a Problem with numbers above 30(it does not)…
Lovis Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Use Math.min()
, like this:
function test() {
const ss = SpreadsheetApp.getActive();
const range = ss.getRange('Sheet1!E2:Z');
hideSameColumns_(range.getSheet(), ss.getSheetByName('Sheet2'), range.getColumn());
}
function hideSameColumns_(sourceSheet, targetSheet, startColumn = 1) {
const numColumns = Math.min(sourceSheet.getMaxColumns(), targetSheet.getMaxColumns()) - startColumn + 1;
for (let i = 0; i < numColumns; i++) {
const colNumber = startColumn + i;
if (sourceSheet.isColumnHiddenByUser(colNumber)) targetSheet.hideColumns(colNumber);
}
}
See Math.min().
Based on your code, you should only replace tempSheet.hideColumns(i+1);
with:
tempSheet.hideColumns(originalColumn);
Here’s the code with a sample setup:
function test() {
//sample setup
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tempSheet = ss.getSheetByName('Sheet2');
var sheet = ss.getSheetByName('Sheet1');
var range = sheet.getRange(1,5,sheet.getLastRow(), sheet.getLastColumn()-4);
var tempSheet = ss.getSheetByName('Sheet2');
var sheet = ss.getSheetByName('Sheet1');
const startColumn = range.getColumn(); // The starting column of the range (e.g., "E" -> 5)
const numColumns = range.getNumColumns(); // The number of columns in the range
for (let i = 0; i < numColumns; i++) {
const originalColumn = startColumn + i; // Get the original column number
const columnHidden = sheet.isColumnHiddenByUser(originalColumn); // Check if the column is hidden in the original sheet
const ende = tempSheet.getLastColumn();
// If the column is hidden in the original sheet, hide the corresponding column in the temporary sheet
if (columnHidden) {
tempSheet.hideColumns(originalColumn); // use original column here
}
// Logger.log(`start column: ${startColumn + i}, left: ${numColumns - i} , Ende: ${ende} , Checking column: ${originalColumn}, isHidden: ${columnHidden}`);
}
}
Here’s an alternative code using forEach
and ternary operator
:
function myFunction() {
//sample setup
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tempSheet = ss.getSheetByName('Sheet2');
var sheet = ss.getSheetByName('Sheet1');
var range = sheet.getDataRange();
var tempSheet = ss.getSheetByName('Sheet2');
var sheet = ss.getSheetByName('Sheet1');
var startColumn = 5;
//actual edits
var data = range.getValues(); //get the 2d array data to get the dimensions for iteration
data[0].forEach((x, i) => ((i>=startColumn)*(sheet.isColumnHiddenByUser(i + 1)) ? tempSheet.hideColumns(i + 1) : null));
}
The code above does entirely the same thing, and the code that uses forEach
and ternary
shortens the syntax of your code.
Reference
- forEach
3