How to overwrite cell function with Paste value only using Google Script

0
votes

I have a database that makes live calculations, but once the new data added to the database has been processed that day, I effectively want to Copy + Paste value only on these cells so that the results of the formulas are fixed and can no longer change.

The current script I am using is:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var r = ss.getRange(1,2,ss.getLastRow());
  var vals = r.getValues();
  for(var i =0;i<vals.length;i++){
      ss.getRange(i+1,2).setValue(ss.getRange(i+1,2).getValue());
  }
}

However, in column B beneath the data set, all the cells start with IF(B2092="","",etc). On cells that are empty, I still require the formula to remain in place. I only want to script to be applied to cells that have data within.

Can anyone help with this? Thanks

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

Try this:

function myFunction() {
    var sh = SpreadsheetApp.getActive();
    var ss = sh.getActiveSheet();
    var rg = ss.getRange(1,2,ss.getLastRow());

    var vals     = rg.getValues();   // get values
    var formulas = rg.getFormulas(); // get formulas

    // merge values & formulas
    var data = formulas.map((row,row_index) => 
                    row.map((cel, cel_index) => 
        { return (cel) ? cel : vals[row_index][cel_index] }));    

    rg.setFormulas(data);            // output
}

Corrected version:

function copy_values_and_formulas() {
    var sh = SpreadsheetApp.getActive();
    var ss = sh.getActiveSheet();
    var rg = ss.getRange(1,2,ss.getLastRow());
    
    var vals     = rg.getValues();   // get values
    var formulas = rg.getFormulas(); // get formulas
    
    // merge values & formulas
    var data = vals.map((row, row_index) => 
                row.map((cel, cel_index) => 
        { return (cel == 0) ? formulas[row_index][cel_index] : cel }));

    rg.setFormulas(data);            // output
}
0
votes
function myFunction() {
  var sh = SpreadsheetApp.getActive();
  var ss = sh.getActiveSheet();
  var rg = ss.getRange(1,2,ss.getLastRow());
  var vals = rg.getDisplayValues();
  for(var i=0;i<vals.length;i++){
    if(vals[i][1] && vals[i][1].length!=0) {
      ss.getRange(i+1,2).setValue(ss.getRange(i+1,2).getValue());
    }
  }
}