I want to create a script, which will be grouping all not empty rows. But, i have problem with checking getDepth or getRange when group doesn’t exist. I get “undefined” and i can’t create easy check like “if getDepth < 0 or “undefined”” because script stopping. Few years ago i created many scripts on VBA but this Apps Scripts a little bit hard for me, I’m newbie here) Can anyone help me?)
function Grouping() {
var i = 2;
var j = 1;
var count = 0;
let firstCellCount = 0;
var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
for (; i < lastRow + 1; i++) {
var curCell = SpreadsheetApp.getActiveSheet().getRange(i, 1).getValue();
var firstCell = SpreadsheetApp.getActiveSheet().getRange(i - 1, 1).getValue();
var lastCell = SpreadsheetApp.getActiveSheet().getRange(i + 1, 1).getValue();
let a = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRowGroup(i, 1).getDepth();
if (a < 1) {
if (curCell !== "" && firstCell == "") {
firstCellCount = i;
}
if (curCell !== "") {
count = count + 1;
}
if (curCell !== "" && lastCell == "") {
SpreadsheetApp.getActiveSheet().getRange(firstCellCount + 1, 1, count).activate().shiftRowGroupDepth(1);
count = 0;
firstCellCount = 0;
}
}
}
}
Advertisement
Answer
It’s not clear how are you getting "undefined", anyway, Sheet.getRowGroup() throws an exception if the group doesn’t exist:
h:mm:ss AM/PM Error Exception: A row group does not exist with index 1 and group depth 1 myFunction @ Code.gs:2
Considering the above you might use try..catch
try{
const group = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRowGroup(i, 1);
// add here whatever you want to do with the group
} catch(error){
// add here something that you want to do when a exception occurs
}
// add here something that you want to do after handling the group that doesn't need a group
Resources