Script to run on google sheet with specific functionality

script.js

function generatePageSheet() {
  let mainSheetName = "input - Price Table"; // Name of the sheet that will be read
  let sheet = SpreadsheetApp.getActive().getSheetByName(mainSheetName); 
  let data = sheet.getDataRange().getValues(); // Getting the values data is 18 rows and 14 columns. The first row has two empty values and then the list

  const [matrixPircesGaps, matrixPricesExtended] = fillMatrix(data);

  name = 'Generated Output'
  // let genSheet = sheet.insertSheet(name);
  let genSheet = SpreadsheetApp.getActive().getSheetByName(name);
  genSheet.clearContents();

  let cols = ['id', 'Title', 'Content', 'SKU', 'Product Type', 'Parent Product ID', 'Product Height', 'Product Height Fractions', 'Product Width', 'Product Width Fractions', 'Material-type', 'Product Attributes', 'Regular Price'];
  let defaultValues = ['', 'Cellular Shades - Light Filtering', '', '', 'variable', '1055', '<VALUE>', ' ', '<VALUE>', '', 'Orchestra 3/8 Double Cell', '', ',<MATRIX_VALUE>'];
  genSheet.appendRow(cols);

  const heightIndexInValue = 6; const widthIndexInValue = 8; const regPriceInValue = 12;
  let collator = new Intl.Collator([], { numeric: true });

  const sortedExtendedCols = Object.keys(matrixPricesExtended).sort((a, b) => collator.compare(a, b));
  const valuesColExtended = Object.values(matrixPricesExtended);
  const sortedExtendedRows = Object.keys(valuesColExtended[0]).sort((a, b) => collator.compare(a, b));

  const sortedColsGap = Object.keys(matrixPircesGaps).sort((a, b) => collator.compare(a, b));
  const valueColGap = Object.values(matrixPircesGaps);
  const sortedRowsGap = Object.keys(valueColGap[0]).sort((a, b) => collator.compare(a, b));

  let matrix_generated = [];
  for (let colExt of sortedExtendedCols) {
    for (let rowExt of sortedExtendedRows) {
      let newRow = Array.from(defaultValues);

      if (colExt in matrixPircesGaps) {
        newRow[widthIndexInValue] = colExt;
        newRow[heightIndexInValue] = rowExt;

        if (rowExt in matrixPircesGaps[colExt]) {
          newRow[regPriceInValue] = matrixPircesGaps[colExt][rowExt];
        } else {
          const nextRowGap = sortedRowsGap.find(rowGap => parseInt(rowGap, 10) > parseInt(rowExt, 10));
          newRow[regPriceInValue] = matrixPircesGaps[colExt][nextRowGap];
        }
      } else {

        const nextColGap = sortedColsGap.find(colGap => parseInt(colGap, 10) > parseInt(colExt, 10));
        newRow[widthIndexInValue] = colExt;
        newRow[heightIndexInValue] = rowExt;

        if (rowExt in matrixPircesGaps[nextColGap]) {
          // newRow[heightIndexInValue] = rowExt;
          newRow[regPriceInValue] = matrixPircesGaps[nextColGap][rowExt];
        } else {
          const nextRowGap = sortedRowsGap.find(rowGap => parseInt(rowGap, 10) > parseInt(rowExt, 10));
          // newRow[heightIndexInValue] = nextRowGap;
          newRow[regPriceInValue] = matrixPircesGaps[nextColGap][nextRowGap];
        }
      }

      matrix_generated.push(newRow);
      if(matrix_generated.length % 1000 == 0){
        Logger.log(matrix_generated.length)
      //   addMatrix(matrix_generated);
      //   matrix_generated = [];
      }
      // genSheet.appendRow(newRow);
    }
  }
  Logger.log(`Matrix with size ${matrix_generated.length}`);
  addMatrix(matrix_generated)
}

/**
 * Add a whole matrix at once
 */
function addMatrix(matrix){
  let lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
  SpreadsheetApp.getActiveSheet().getRange(lastRow + 1,1,matrix.length, matrix[0].length).setValues(matrix);
}

/**
 * Fill a Matrix with the values of the prices for easy access m[c, r]
 */
function fillMatrix(data, result = {}) {
 
  // FROM [ '', '', 24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ] to [ 24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ]
  const cleanCols = removeEmptyValuesFromHeader(data);

  const initExtended = {}; 
  //        👇👆 {24: {}, 25: {}, 26: {},..., 107:{}, 108:{}}
  const resultExtended = createEmptyContinuosDictWithIndexMinToIndexMax(data, initExtended, cleanCols);

  const initGaps = {}; 
  //      👇 👆{24: {}, 25: {}, 26: {},..., 107:{}, 108:{}}
  const resultGaps = createEmptyDictWithGaps(data, initGaps, cleanCols);

  //FROM
  // ['', 36, 42, 48, 54, 60, 66, 72, 78, 84, 90, 96, 102, 108, 114, 120, 126, 132 ]
  // to 
  // [ 36, 42, 48, 54, 60, 66, 72, 78, 84, 90, 96, 102, 108, 114, 120, 126, 132 ] 
  //      👇
  const cleanRows = cleanRowsHeaderValues(data);
  
  // FROM {24: {}, 25: {}, 26: {},..., 107:{}, 108:{}} to
  // {24: { 36:{}, 37:{}, 38:{}, ..., 131:{},132:{} }, 
  //  25: { 36:{}, 37:{}, 38:{}, ..., 131:{},132:{} },
  //  ,..., 
  //  107: { 36:{}, 37:{}, 38:{}, ..., 131:{},132:{} },
  //  108: { 36:{}, 37:{}, 38:{}, ..., 131:{},132:{} },} 
  //            👇
  const resultMatrixContinuosIndexExtendedWithRows = fillRowsExtended(data, resultExtended, cleanRows);
  // FROM {24: {}, 25: {}, 26: {},..., 107:{}, 108:{}} to
  // {24: { 36:{}, 42:{}, 48:{}, ..., 126:{},132:{} }, 
  //  25: { 36:{}, 42:{}, 48:{}, ..., 126:{},132:{} }, 
  //  ,..., 
  //  107: { 36:{}, 42:{}, 48:{}, ..., 126:{},132:{} }, 
  //  108: { 36:{}, 42:{}, 48:{}, ..., 126:{},132:{} }, 
  //            👇
  const resultMatrixWithGapsWithRows = fillRowsWithGaps(data, resultGaps, cleanRows);

  // ALL THE PREVIOUS CODE IS FOR ACCESSING OR ASSIGN A VALUE TO
  //  resultMatrixWithGapsWithRows[colHeaderIndex][rowHeaderIndex]
  let collator = new Intl.Collator([], { numeric: true }); // LITLE HACK Sort as Integer
  let headersCol = Object.keys(resultMatrixWithGapsWithRows).sort((a, b) => collator.compare(a, b));

  for (let rowIndex = 0; rowIndex < data.length; rowIndex++) {
    let isHeaderRow = rowIndex === 0;
    if (isHeaderRow) {
      continue;
    }
    let row = data[rowIndex];
    for (let colIndex = 1; colIndex < row.length; colIndex++) {

      // 24 30  36  42  48  54  60  66  72  84  96
      let colHeaderIndex = headersCol[colIndex - 1];
      // Logger.log(colHeaderIndex);
      // 166  191 212 235 255 277 296 318 365 410 456 / 182 204 230 255 278 304 326 351 403 453 506 ...
      let item = parseInt(row[colIndex], 10);
      // Logger.log(item);

      // 36 42 48 54 60 66 72 78 84 90 96 108 120  
      let rowHeaderIndex = row[0];
      // Logger.log(rowHeaderIndex);

      resultMatrixWithGapsWithRows[colHeaderIndex][rowHeaderIndex] = item;
      // Logger.log(resultGapsWithRows);
    }
  }
  return [resultMatrixWithGapsWithRows, resultMatrixContinuosIndexExtendedWithRows];
}



/**
 * Help to fill rows
 */
function fillRowsExtended(data, result, cleanRows = cleanRowsHeaderValues(data)) {

  let minRowHeader = Math.min(...cleanRows);
  let maxRowHeader = Math.max(...cleanRows);

  let collator = new Intl.Collator([], { numeric: true });
  let extendedColsKeys = Object.keys(result).sort((a, b) => collator.compare(a, b));

  for (let key of extendedColsKeys) {
    for (let index = minRowHeader; index <= maxRowHeader; index++) {
      result[key][index] = {};
    }
  }

  return result;

}

function fillRowsWithGaps(data, result, cleanRows = cleanRowsHeaderValues(data)) {

  let collator = new Intl.Collator([], { numeric: true });
  let cols = Object.keys(result).sort((a, b) => collator.compare(a, b));

  for (let col of cols) {
    for (let row of cleanRows) {
      result[col][row] = {};
    }
  }

  return result;

}

/**
 * Return clean Rows
 */
function cleanRowsHeaderValues(data) {
  let cleanRows = [];
  for (let i = 1; i < data.length; i++) {
    cleanRows.push(data[i][0]);
  }
  return cleanRows;
}

/**
 * Only pass values that are numeric this is:
 * Original example the header is [ '', '', 24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ]
 * This function will earease the empty values
 * [24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ]
 */
function removeEmptyValuesFromHeader(data) {
  
  let colHeader = data[0]; // [ '', '', 24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ]
  let cleanCols = [];
  for (let i = 0; i < colHeader.length; i++) {
    let item = colHeader[i];
    let skipEmptyValue = typeof item === 'string' && item.length === 0;
    if (!skipEmptyValue) {
      cleanCols.push(item);
    }
  }

  return cleanCols;
}

/**
 * createEmptyContinuosDictWithIndexMinToIndexMax
 * It will create a matrix from min to max. 
 * E.g:
 * It will receive a list [ 24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ] and its
 * min is 24  and Max is 108 the it will create a matrix with numbers of [ 24, 25, 26,..., 107, 108]
 * The result will be a dict with empty values this is:
 * {24: {}, 25: {}, 26: {},..., 107:{}, 108:{}}
 */
function createEmptyContinuosDictWithIndexMinToIndexMax(data, result = {}, cleanCols = removeEmptyValuesFromHeader(data)) {
  let minColHeader = Math.min(...cleanCols); // Get the min from [ 24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ] => 24
  let maxColHeader = Math.max(...cleanCols); // Get the max from [ 24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ] => 108

  for (let index = minColHeader; index <= maxColHeader; index++) {
    result[index] = {};
  }

  return result;
}


/**
 * createEmptyDictWithGaps it will get the values of the list and set it as index on the dict
 * E.g:
 * It will receive a list [ 24, 30, 36, 42, 48, 54, 60, 66, 72, 84, 96, 108 ] and its
 * it will create a matrix 
 * {24: {}, 30: {}, 36: {},..., 96:{}, 108:{}}
 */
function createEmptyDictWithGaps(data, result = {}, cleanCols = removeEmptyValuesFromHeader(data)) {
  for (let col of cleanCols) {
    result[col] = {};
  }
  return result;
}