When I run a script on google sheets my formulas disappear-CodePudding

Hi I'm new to scripts on Google sheets. I have the following script that copy values from one sheet to another according to an Id. The problem is my destination sheet "Product Backlog - How? - Funcional" have formulas but when I run the script all of them disappear. Why this is happening?.

Here is the code

function onEdit(e) {
  if(e.range.getSheet().getName() != 'Sprint'){return;}
   if(e.range.columnStart==1) {
    var idTarea=e.range.offset(0,1).getValue();
    var rgBacklog=e.source.getSheetByName('Product Backlog - How? - Funcional').getDataRange();
    var valuesBacklog=rgBacklog.getValues();
    for(var i=1;i<valuesBacklog.length;i  ) {
        if(valuesBacklog[i][2]==idTarea) {
        valuesBacklog[i][0]=e.value;
        break;
      }
    }    
  }
  rgBacklog.setValues(valuesBacklog);
}

The workbook function like this.

1-I complete values on Sheet "RoadMap - Why? - Estrategico"

2-I validate cell values on Sheet "Product Backlog - How? - Funcional" and I then i create some Ids

3-With a query according to a condition data go to sheet "Sprint"

4- When someone edit sprint column "estado" with the script this update sheet column "estado" on "Product Backlog - How? - Funcional"

after i run the script all my formulas "Product Backlog - How? - Funcional" dissapear

Here an example of the workbook https://docs.google.com/spreadsheets/d/11RQYPp0teUd2phYw4DoRRz70Xm3vJR8kwo8clQGzfWs/edit?usp=sharing

CodePudding user response：

Can you provide more information or a screenshot of your destination sheet?

A probable issue is that when using .getValues() and .setValues(), any blank cells in the array(s) will overwrite formula cells in the destination range; and vice-versa with .getFormulas() / .setFormulas().

If this is the case, one solution is to merge the "values" array with the "formulas" array, filling in the value at each index if there is no formula value present (blank cell).

const formulas = DESTINATION_SHEET.RANGE.getFormulas()
const values = DATA_SHEET.RANGE.getValues()

const data = formulas.map((row, rowIndex) => 
                  row.map((col, colIndex) => 
                      col || values[rowIndex][colIndex]))

If this doesn't solve your issue please let me know!

CodePudding user response：

If you only need to update one value (estado) try

function onEdit(e) {
  if (e.range.getSheet().getName() != 'Sprint') { return; }
  if (e.range.columnStart == 1) {
    var idTarea = e.range.offset(0, 1).getValue();
    var rgBacklog = e.source.getSheetByName('Product Backlog - How? - Funcional').getRange('A:C');
    var valuesBacklog=rgBacklog.getValues();
    for(var i=1;i<valuesBacklog.length;i  ) {
        if(valuesBacklog[i][2]==idTarea) {
        e.source.getSheetByName('Product Backlog - How? - Funcional').getRange( i 1,1).setValue(e.range.getValue())
        break;
      }
    }    
  }
}