Merge The Following Regular Function And OnEdit Function To Obtain The Desired Result

I want to merge these two functions below to get the desired output which is the following: Lets assume, we have size variations available in Column E of Sheet 1. So, I want the fu

Solution 1:

Explanation:

You want to copy the product and color based on the number of the size elements.

Replace:

const valuesAr = values.flatMap(r=>[r,...emptyAr]);

with:

const valuesAr = values.flatMap(r=>[...new Array(sizes.length)].map(elem => r));

and you can now delete emptyAr and you can also get rid of the onEdit trigger if you don't need it.

Answer :

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Sheet 1');
  const sh2 = ss.getSheetByName('Sheet 2');
  const sizes = sh1.getRange('E2:E5').getValues().flat();
  const vals1 = sh1.getRange('A2:D'+sh1.getLastRow()).getValues();
  const values = vals1.filter(r=>r[0]==true).map(([,b,c,d])=>[b,c,d]);
  const valuesAr = values.flatMap(r=>[...new Array(sizes.length)].map(elem => r));
  const sizesAr = new Array(values.length).fill(sizes).flat().map(c=>[c]);
  const lrow = sh2.getLastRow();
  sh2.getRange(lrow+1,2,valuesAr.length,valuesAr[0].length).setValues(valuesAr);
  sh2.getRange(lrow+1,11,sizesAr.length,sizesAr[0].length).setValues(sizesAr);
}