0
171
VistasWhy is this nested for loop pushing 1 more element than it should? Dataset is 65, but the array is 68 length wise
I've been trying to find out why, but not knowing for loop enough doesn't let know why this is happening.
I'm doing the following:
- Get a column of data (Categories) using
getvalues() - Loop through it and isolate each value for further comparison
- Push the other categories into an array (to be ignored when comparing each with the row data in the following iteration;
- Compare the 1st column of each row of a dataset with the category isolated in step 2;
It's giving me the correct rows where each category found in the data, but when I psh that value into an array, it's pushing one more than it should, resulting in an array bigger than the sheet values.
const categories = catSubSheet.getRange(2, 1, catSubSheet.getLastRow(), 3).getValues();
const subCategories1 = categories.filter(e => e[1] != '').map(function (e) { return e[1] });
const sheetData = sheet.getRange(5, 1, sheet.getLastRow(), 15).getValues();
var sub1Array = [];
var subCategory1 = '';
for (let n = 0; n < subCategories1.length; n++) {
let subs1ToIgnore = [];
subCategory1 = subCategories1[n];
//PUSHES SUBCATEGORIES 1 TO IGNORE IN THE FOLLOWING ITERATION
for (let a = 0; a < subCategories1.length; a++) {
if (subCategories1[a] != subCategory1) {
subs1ToIgnore.push(subCategories1[a])
}
}
let found;
for (let n = 0; n < sheetData.length; n++) {
const rowData = sheetData[n][0]
rowData.toString().trim();
if (rowData !== '' && rowData === subCategory1) {
found = true
startRowsSub1 = n + 5
}
if (found) {
sub1Array.push([subCategory1])
}
if (subs1ToIgnore.indexOf(rowData) > - 1) {
found = false
}
}
}
The data starts in row 5.
Here are the logs:
Appreciate any help.
·
Juan Pablo Isaza
1 Respuestas
Responde la pregunta0
Make sure to apply offsets when doing getRange especially when dealing with data not starting at 1st row.
Since your script is pretty clunky, I have overhauled it. See the answer below.
Script:
//THIS FUNCTION FORMATS CELL BACKGROUND COLORS AND SPREADS THE FORMULAS FROM ROW 4 (WHICH IS HIDDEN) THROUGH THE SHEET
function formatBOQcells() {
const SS = SpreadsheetApp.getActiveSpreadsheet();
const sheet = SS.getSheetByName('Test')
const catSubSheet = SS.getSheetByName('Categories and Subcategories')
const categories = catSubSheet.getRange(2, 1, catSubSheet.getLastRow() - 1, 3).getValues();
const subCategories1 = categories.filter(e => e[1]).map(function (e) { return e[1] });
// get ColA, transform to 1D array and remove blank cells
const sheetData = sheet.getRange(5, 1, sheet.getLastRow() - 4, 1).getValues().flat().filter(String);
// this will store the current subcategory
var currSubCategory = [''];
// for each sheetData row
const output = sheetData.map(item => {
// check if description a subcategory
if(subCategories1.includes(item)) {
// if subcategory, save it as current subcategory
currSubCategory = [item];
// return its value
return currSubCategory;
}
// if item is this row, write nothing
if(item == 'Sub Total for Pipework') {
return [''];
}
// if item is not a subcategory, assign the current subcategory
return currSubCategory;
});
sheet.getRange(5, 3, output.length, output[0].length).setValues(output);
}
Note:
- 3rd parameter is data length. And since you are not starting at 1, you need to offset your parameter to get the exact number
- To get the proper offset, you need to subtract how many rows you skipped to the last row (assuming all rows are valid/unfiltered).
·
Juan Pablo Isaza
Denunciar
Responde la pregunta