Google Form/Sheet script clears formulas, conditional formatting in row

Question

I have this code that parses out data entered into a form as it is put into the linked spreadsheet. I also have a part at the end to set one cell to a specific value on form submit. However, any formulas or conditional formatting I had in the row the submission is entered into gets cleared out. For example, I have color-coding for the cell that receives the aforementioned specific value. But when I look at the conditional formatting section of my sheet, the range of values is shifted down 1. ie: Original I2:I50 ; Post-submission I3:I51

There is a formula I have to perform some calculations that is completely cleared out as well. Here is my script:

function onSubmit(e) {
  Logger.log("onSubmit() called with data:");
  Logger.log(e);

  var category = e.namedValues[ 'Location' ];
  Logger.log("category from onSubmit is " + category);
  Logger.log("range in sheet was:" + e.range.getA1Notation());

  var rowNum = e.range.getRow();
  Logger.log("row in range in sheet was " + rowNum);

  var columnNum = 3;
  Logger.log("last column in range in sheet was " + columnNum);

  var pieces = category.toString().split(" > ");
  Logger.log("split up category: ");
  Logger.log(pieces);


  var arrayLength = pieces.length;
  for (var i = 0; i < arrayLength; i++) {

    var targetColumnNum = columnNum + i;

    Logger.log("target Column " + targetColumnNum );
    Logger.log("setting row " + rowNum + " Column " + targetColumnNum + " to value " + pieces[i]);

    SpreadsheetApp.getActiveSheet().getRange(rowNum, targetColumnNum).setValue(pieces[i]);

  }
  targetColumnNum = 9;
  SpreadsheetApp.getActiveSheet().getRange(rowNum, targetColumnNum).setValue("(1)Open");
}

Is there something in how the for loop operates that would make it clear all the conditional formatting and formulas from other cells in the row?

Unknown Unknown · Accepted Answer · 2016-12-01T05:15:19

Each time that data is submitted through the form, a new row is inserted in the sheet (under the most recent submission), and the data is placed in that new row. This is why you don't have any formatting or formulas in that row.

Here are some ways to combat this effect: 1-2 are alternative ways to maintain formulas, 3-4 formatting.

Have an arrayformula that handles the computation for the entire column (assuming the logic of your formula can be expressed in this way).
Use setFormula in onSubmit to insert the formula to each new row with submitted data.
Copy conditional formatting from, say, the top row of the sheet to the new row: for example,
```
var range = sheet.getRange("A1:Z1");
range.copyTo(newRow, {formatOnly: true});
```
Format cells directly by the script, using setBackground based on the submitted values.

Google Form/Sheet script clears formulas, conditional formatting in row

1 Answers