Google Sheet Formatting

I believe your goal as follows.

• You want to achieve the following conversion on Google Spreadsheet using Google Apps Script.

  • From: Source sheet

    

  • To: Destination sheet

    

Flow:

In this case, I would like to propose the following flow of the sample script.

1. Retrieve values from the source sheet.
2. Create values for putting to the destination sheet an 2 dimensional array.
   • In this case, in the following sample script, the value of column "A" is checked. When the value of column "A" is not empty, the values of columns "A" to "D" is set to temp array. And when the next row of it has no value of column "A", the value of row is added to temp. By this, 0,123-00000-00,Test Assembly,,ABC Inc,abc123 is created. When the next row has no value of column "A", the value is added to ar by adding 4 empty values. By this, ,,,,XYZ Inc,abc234 is created. By this flow, the destination values are created.
3. Put the created value to the destination sheet.

Sample script:

When you use this script, please copy and paste the following script to the container-bound script of the Spreadsheet. And please set the source sheet name and destination sheet name.

function myFunction() {
  const srcSheetName = "Sheet1";  // Please set the source sheet name.
  const dstSheetName = "Sheet2";  // Please set the destination sheet name.
  
  // 1. Retrieve values from the source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [header1, [,subHead1, subHead2], ...srcValues] = ss.getSheetByName(srcSheetName).getDataRange().getValues();
  
  // 2. Create values for putting to the destination sheet an 2 dimensional array.
  let temp = [];
  const dstValues = srcValues.reduce((ar, [a,b,c,d]) => {
    if (a.toString() != "") {
      if (temp.length == 4) ar.push(temp.concat("",""));
      temp = [].concat(a,b,c,d);
    } else {
      ar.push(temp.length == 4 ? temp = temp.concat(b,c) : Array(4).fill("").concat(b,c));
    }
    return ar;
  }, [header1.concat(subHead1, subHead2)]);
  
  // 3. Put the created value to the destination sheet.
  ss.getSheetByName(dstSheetName).getRange(1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
}

• When you run the function of myFunction(), the values are put to the destination sheet.

Note:

• Please use this script with enabling V8.

• If you want to use the script as the custom function, you can also use the following script. When you use this script, for example, when your sample input sheet is used, please put a custom function of =CUSTOMSAMPLE(A1:D12).

    function CUSTOMSAMPLE(values) {
      // 1. Retrieve values from the source sheet.
      const [header1, [,subHead1, subHead2], ...srcValues] = values;
  
      // 2. Create values for putting to the destination sheet an 2 dimensional array.
      let temp = [];
      const dstValues = srcValues.reduce((ar, [a,b,c,d]) => {
        if (a.toString() != "") {
          if (temp.length == 4) ar.push(temp.concat("",""));
          temp = [].concat(a,b,c,d);
        } else {
          ar.push(temp.length == 4 ? temp = temp.concat(b,c) : Array(4).fill("").concat(b,c));
        }
        return ar;
      }, [header1.concat(subHead1, subHead2)]);
  
      // 3. Put the created value to the destination sheet.
      return dstValues;
    }
  

• This sample script is for your sample input and output values in your question. When the cell structure in your actual sheet is different from them, the script might not be able to be used. Please be careful this.

References:

• getValues()
• reduce()
• setValues(values)