I am stuck at a point with Google apps script. Please help me out

I have a set of rows in Sheet1 and based on each row in a table in Sheet1 I am inserting 13 set of rows in Sheet2 corresponding to each row populated in the table in Sheet1

When J11 is populated inSheet1 and the button linked to apps script function executes, the following should happen in Sheet2

1. Insert 13 rows starting from row 43
2. The cell D43 should update with a concatenated string from Sheet1 from the first row in the table, which here is B11, H111 and J11
3. The range D43:F43 should set color to a grey background
4. The range H43:I55 should set color to a grey background(13 rows and 2 columns)
5. The range K43:P55 should set color to a grey background(13 rows and 6 columns)
6. The range R43:Y55 should set color to a grey background(13 rows and 8 columns)
7. The range AA43:AH55 should set color to a grey background(13 rows and 8 columns)

I wrote the code for Step1 to Step3 with the below code, but not able to figure out how to go ahead with rest of the steps

function addPricingRows() {
  d1= SpreadsheetApp.getActiveSpreadsheet();
  var Product_Details = d1.getSheetByName("Sheet1");
  var Product_Pricing = d1.getSheetByName("Sheet2");
  
  var offset_from1 = Product_Pricing.getRange("D43")
  var check = Product_Details.getRange("J11").isBlank();
  if(!check){
  Product_Pricing.insertRowsAfter(42,13);
  
  var a= Product_Details.getRange("B11").getValue();
  var b= Product_Details.getRange("H11").getValue();
  var c= Product_Details.getRange("J11").getValue();
  Product_Pricing.getRange("D43").setValue(a "-" b "-" c);
  var range1 = Product_Pricing.getRange("D43:F43");
  const range2 = offset_from1.offset(0,4).getA1Notation();
  range1.setBackground("#e6e6e6");
  range2.setBackground("#e6e6e6");

  }
  else{
    Product_Pricing.deleteRows(43,13)
  }
}

Please help me achieve this, here is the link to spreadsheet for reference- https://docs.google.com/spreadsheets/d/180iMihBq9-Gep9Em6v570pshPRNgsruaboFv5e4XHes/edit#gid=13131281

Thanks in advance

CodePudding user response：

I you're trying to set a specific range of cells to a certain color, it should just be sheet.getRange('D43:F43').setBackground('gray'), where sheet is the name of a specific sheet on your spreadsheet. You can also use a hex color in place of just 'gray'. For example: #ffffff is just white.