How to remove conditional formatting in sheets using script

Question

I'm currently using a modified script that allows me to copy an entire line from sheet#1, create a new line at the top of sheet #2, paste the copied line to that sheet, and delete the old line from sheet#1. This is done often throughout the day by many users. The function is onEdit.

This is the script :

function onEdit(e) { var ss = e.source; var activatedSheetName = ss.getActiveSheet().getName(); var activatedCell = ss.getActiveSelection(); var activatedCellRow = activatedCell.getRow(); var activatedCellColumn = activatedCell.getColumn(); var activatedCellValue = activatedCell.getValue(); var URGENCE = ss.getSheetByName("List"); // source sheet var COMPLET = ss.getSheetByName("Comp"); // target sheet // if the value in column K is "x", move the row to target sheet if (activatedSheetName == URGENCE.getName() && activatedCellColumn == 11 && activatedCellValue == "x") { COMPLET.insertRows(2,1);// insert a new row at the second row of the target sheet var rangeToMove = URGENCE.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ URGENCE.getMaxColumns()); rangeToMove.moveTo(COMPLET.getRange("A2")); URGENCE.deleteRows(activatedCellRow,1); // delete row from source sheet } }

Recently this has been crashing my sheet. Everytime someone puts an "x" in Column K, the sheet will stall and most of the time, it will crash and chrome will kill the page.

I could be wrong, but the problem I'm guessing is that most of the rows in sheet#1 have conditional formatting. When the line is copied, it also copies the conditional formatting. This results in my sheet#2 having hundreds of repeating conditional formatting: this sheet is VERY slow to open. IT could also be because this document is shared with about 30 people who view it and edit it very often: perhaps onEdit isn't the right function here?

Is there a simple script I could add to my function which would strip the conditional formatting on the pasted line? I don't need the conditional formatting in my sheet#2 and for some odd reason I can't find an answer to this anywhere.

Max Makhrov · Accepted Answer · 2016-05-03 13:55:40Z

Found this function from here.

clearFormats()

Clears the sheet of formatting, while preserving contents. Formatting refers to how data is formatted as allowed by choices under the "Format" menu (ex: bold, italics, conditional formatting) and not width or height of cells.

Sample code:

function testKillFormatting (nameOfSheet) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(nameOfSheet); sheet.clearFormats(); }

could I add this to the end of my function or should I keep is as a separate function that I would run at another time?
Yes, this may be used as part of function, or as separate function.

Collectives™ on Stack Overflow

How to remove conditional formatting in sheets using script

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related