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:

1. Get a column of data (Categories) using getvalues()
2. Loop through it and isolate each value for further comparison
3. Push the other categories into an array (to be ignored when comparing each with the row data in the following iteration;
4. 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.

CodePudding user response：

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).