I am trying to loop through Sheets and getLastColumn(). I activate the first sheet and get the last column, then I activate the next sheet, but this time I get an error on getLastColumn(). I am doing this in a for loop, could that be the issue?
//sort fab, milling, turning, engraving
sheet = SpreadsheetApp.getActive().getSheetByName("Fab Schedule");
sheet.activate;
var lastColumnFab = sheet.getLastColumn();
var lastColumn = lastColumnFab;
var lastColumnMilling;
var lastColumnTurning;
var lastColumnEngraving;
var currentSheet = SpreadsheetApp.getActiveSheet().getSheetName();
Logger.log(currentSheet);
for(a=0;a<3;a++){ // 'a' is for looping through the different sheets
Logger.log(a);
// counting non-empty rows
var BValues = sheet.getRange("B3:B").getValues();
var i=0;
var singleBValue = "";
//Logger.log(BValues[i]);
for(i = 0; BValues[i] != ""; i++){
singleBValue = BValues[i];
//console.log({i,singleBValue});
}
var lastRow = i;
i=i+3;
// arrays begin with 0 // all rows and columns begin with 1
sheet.getRange(3,2,lastRow,lastColumn).activate();
//sheet.getRange('B3:V').activate(); //number of columns is different on every
sheet
var statusColumn = getHeaderNumber("Status")+1; //get header uses array, so it
starts at 0
var dueDateColumn = getHeaderNumber("Due Date")+1;
var engineerColumn = getHeaderNumber("Engineer")+1;
//sort by incomplete->complete, then
sheet.getActiveRange().sort([{column: statusColumn, ascending: false}, {column: dueDateColumn, ascending: true},{column:engineerColumn, ascending: true}]);
//sheet.getActiveRange().sort([{column: 14, ascending: false}]);//, {column: 18, ascending: true},{column:2, ascending: true}]);
sheet.getRange(3,14,lastRow,lastColumn-14).setHorizontalAlignment("center"); //number of columns is different on every sheet
sheet.getRange(3,1,lastRow,lastColumn).setVerticalAlignment("middle");
sheet.getRange(3,1,lastRow,lastColumn).setFontFamily("Arial");
sheet.getRange(3,1,lastRow,lastColumn).setFontSize(10);
if (a==0){
sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Milling Schedule");
sheet.activate();
var currentSheet = SpreadsheetApp.getActiveSheet().getSheetName();
Logger.log(currentSheet);
lastColumnMilling = sheet.getLastColumn(); //error?
lastColumn = lastColumnMilling;
}
Exception: The coordinates of the range are outside the dimensions of the sheet. Sortbyincompleteanddate @ sort.gs:62
The code is not working correctly because the for (a
loop iterates three times but the code only attempts to handle two sheets. Use a more structured approach, perhaps like this:
function test() {
['Fab Schedule', 'Milling Schedule', 'Turning Schedule', 'Engraving Schedule',]
.forEach(sheetName => {
const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
if (!sheet) {
console.log(`There is no sheet by the name ${sheetName}`);
return;
}
sortAndFormat_(sheet);
});
}
function sortAndFormat_(sheet) {
const lastRow = sheet.getLastRow();
const lastColumn = sheet.getLastColumn();
sheet.getRange(3, 2, lastRow, lastColumn)
.sort([
{ column: getHeaderNumber(sheet, 'Status') + 1, ascending: false },
{ column: getHeaderNumber(sheet, 'Due Date') + 1, ascending: true },
{ column: getHeaderNumber(sheet, 'Engineer') + 1, ascending: true },
]);
sheet.getRange(3, 14, lastRow, lastColumn - 14).setHorizontalAlignment('center');
sheet.getRange(3, 1, lastRow, lastColumn)
.setVerticalAlignment('middle')
.setFontFamily('Arial')
.setFontSize(10);
}
Note that I've added a sheet
parameter to the getHeaderNumber()
call. You will need to adjust that function to accept and use the sheet
parameter instead of locating the sheet with Spreadsheet.getActiveSheet()
or similar means.
Use a more structured approach to clean up your code and make it easier to track and maintain. See Clean Code JavaScript and take a look at methods such as Array.forEach().