mobix-fg-to-component-converter.gs

/** @OnlyCurrentDoc */
/* Globals */
let sheetDataRanges = {};
let sheetVals = {};
let skuParentInfo = {};

/* This is the launch point! */
function buildNewList() {
  Logger.log('Building parent SKU info...');

  buildParentInfo();

  Logger.log(`Parent SKU info parsed! ${Object.keys(skuParentInfo).length} parent SKUs parsed.`)

  Logger.log(`Parent SKU MXH-BIO-FG-01 has the following info:\n${JSON.stringify(skuParentInfo['MXH-BIO-FG-01'], null, 2)}`);

  let curSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let curSheet = SpreadsheetApp.getActiveSheet();

  Logger.log('Duplicating sheet...')

  // duplicate sheet
  curSheet = curSpreadsheet.duplicateActiveSheet();
  console.log(curSheet.getName())

  Logger.log('Sheet duplicated')

  Logger.log('Finding all FG SKUs to replace...');

  let fgSkuCount = 0;
  let totalSkuCount = 0;
  let curSheetDataRange = curSheet.getDataRange();
  let data = curSheetDataRange.getValues();
  let fgRanges = [];

  for(let rowNum = 0; rowNum < data.length; rowNum++) {
    let row = data[rowNum];
    let sku = row[0];

    if(sku.includes('-FG-')) {
      fgRanges.push(curSheetDataRange.getCell(rowNum + 1,1));
      fgSkuCount++;
    }
    totalSkuCount++;
  }

  Logger.log(`Found ${fgSkuCount} FG SKUs in ${totalSkuCount} total SKUs`);

  Logger.log(`Annotating output file...`)

  let totalAddedRows = 0;
  for(let fgCell of fgRanges) {
    fgCell = fgCell.offset(totalAddedRows, 0);
    let sku = fgCell.getValue();
    let parentSkuData = skuParentInfo[sku];
    let fgRowIndex = fgCell.getRowIndex();

    if(parentSkuData) {
      fgCell.offset(0, 9).setValue(`Parent SKU ${sku} has ${parentSkuData.components.length} component SKUs`);
      fgCell.setBackground('#ccffcc');
      curSheet.insertRows(fgRowIndex + 1, parentSkuData.components.length);
      totalAddedRows += parentSkuData.components.length;
      
      for(let i = 0; i < parentSkuData.components.length; i++) {
        fgCell.offset(1 + i, 0).setValue(parentSkuData.components[i].sku); // Product SKU
        fgCell.offset(1 + i, 1).setValue(parentSkuData.components[i].sku); // Product UPC
        fgCell.offset(1 + i, 2).setValue(parentSkuData.components[i].name); // Product Name
        fgCell.offset(1 + i, 3).setValue(0); // On Order
        fgCell.offset(1 + i, 4).setValue(0); // Allocated
        fgCell.offset(1 + i, 5).setValue(0); // On Hand
        fgCell.offset(1 + i, 6).setValue(Number(parentSkuData.components[i].qty) * Number(fgCell.offset(0,6).getValue())); // Available
        fgCell.offset(1 + i, 0).setBackground('#ccccff');
      }
    } else {
      // fgCell.offset(0, 9).setValue(`Could not find component SKUs`);
    }
  }

  Logger.log(`Done!`);
}

function buildParentInfo() {
  let curSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let allSheets = curSpreadsheet.getSheets();
  let totalSheetsCount = allSheets.length;
  let curSheetIndex1 = 1;

  for(let sheet of allSheets) {
    let sheetName = sheet.getName();
    sheetDataRanges[sheetName] = sheet.getDataRange();
    sheetVals[sheetName] = sheetDataRanges[sheetName].getValues();

    Logger.log(`processing ${curSheetIndex1}/${totalSheetsCount}: ${sheetName}...`);

    for(let rowNum = 0; rowNum < sheetVals[sheetName].length; rowNum++) {
      for(let colNum = 0; colNum < sheetVals[sheetName][rowNum].length; colNum++) {
        let val = sheetVals[sheetName][rowNum][colNum];

        if(val.startsWith && val.startsWith('MXH-')) {
          let components = getComponentsForParent(sheetName, rowNum, colNum);

          if(!components.error) {
            skuParentInfo[val] = components;
          }
        }
      }
    }

    curSheetIndex1++;
  }

  Logger.log(`${Object.keys(skuParentInfo).length} parent SKUs found`);
}

function getComponentsForParent(sheetName, r, c) {
  let kidNameCol;
  let kidSkuCol;
  let kidQtyCol;
  let kidRow = r + 2;
  let descriptor = {
    components: []
  };

  // try {
  {
    if((sheetVals[sheetName].length > (r + 2)) && sheetVals[sheetName][r][c + 1] == 1 && sheetVals[sheetName][r + 2][c] && sheetVals[sheetName][r + 2][c].startsWith('MXH-') && c > 0) {
      // format is: Name | SKU | Qty
      kidNameCol = c - 1;
      kidSkuCol = c;
      kidQtyCol = c + 1;
    } else if((sheetVals[sheetName].length > (r + 2)) && sheetVals[sheetName][r][c + 2] == 1 && sheetVals[sheetName][r + 2][c] && sheetVals[sheetName][r + 2][c].startsWith('MXH-')) {
      // format is: SKU | Name | Qty
      kidNameCol = c + 1;
      kidSkuCol = c;
      kidQtyCol = c + 2;
    } else {
      return {error: 'Unknown child component format'};
    }

    while((kidRow < sheetVals[sheetName].length) && '' != sheetVals[sheetName][kidRow][kidNameCol] && sheetVals[sheetName][kidRow][kidSkuCol] && sheetVals[sheetName][kidRow][kidSkuCol].startsWith('MXH-')) {
      descriptor.components.push({
        name: sheetVals[sheetName][kidRow][kidNameCol],
        sku: sheetVals[sheetName][kidRow][kidSkuCol],
        qty: sheetVals[sheetName][kidRow][kidQtyCol]
      });

      kidRow++;

      // Logger.log(`sheetName: ${sheetName}, r: ${r}, c: ${c}, length: ${sheetVals[sheetName].length}`)
      // Logger.log(`name: ${kidNameCol}, sku: ${kidSkuCol}, qty: ${kidQtyCol}, row: ${kidRow}`);
    }
  }
  // } catch(e) {
  //   Logger.log(`sheetName: ${sheetName}, r: ${r}, c: ${c}, e: ${e},`)
  // }

  return descriptor;
}