Google Sheets script, maintaining conditional formatting after inserting a row

1
votes

I have a spreadsheet, where I have a list of items from row 6 down. the fifth row is reserved for inputting new item details. Then I use a custom button to add the item to the list (insert new row above row 6, copy row 5 to row 6, clear row 5). My problem is I have conditional formatting on column A:C, and after 'addItem' runs the new row isn't included in conditional formatting rule, it changes from 'A1:C1211' to 'A1:C4,A6:C1211' (gap increases as more items are added). How can I keep formatting rule applied to the new row, or update the rule afterwards to include the new row?

Manually inserting row in sheet maintains formatting, so not sure why it's not maintained with script.

function addItem() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var poshSheet = spreadsheet.getSheetByName("Posh");
  var data = spreadsheet.getSheetByName("Background Data");

  var cell;
  var inputRow = poshSheet.getRange('A5:R5');
  var insertRow = poshSheet.getRange('A6:R6');

  if(inputRow != null){  
    //get and updateItemNo
    var itemNo = data.getRange('D12').getCell(1, 1).getValue();
    inputRow.getCell(1,1).setValue(itemNo);
    data.getRange('D12').getCell(1, 1).setValue(itemNo+1);

    //insert blank row at top 
    poshSheet.insertRowBefore(6);
    //copy input 
    inputRow.copyTo(insertRow);

    //clear input row
    inputRow.clear()
  }  
}
1
google-apps-scriptgoogle-sheetsgs-conditional-formattinggoogle-sheets-conditionalformatting

1 Answers

2
votes

I think that the reason of your issue is inputRow.clear(). In this case, the format is also cleared. By this, the range is splitted. So how about modifying as follows?

From:

inputRow.clear();

To:

inputRow.clear({contentsOnly: true});

or

inputRow.clearContent();

Note:

  • In this case, the cell value is cleared without splitting the range.

References:

If I misunderstood your question and this was not the result you want, I apologize.