CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Davagaz
Davagaz

Reputation: 884

Apply google app script function to range of cells

I have a funktion, that replace solved formulas by values, and not solved formulas its just refresh. But I need this funktion to apply it for fixed range (B2:G) of cells on several sheets. Please help to solve it or just say where its better to search answer?

function testfindfunc1() 
{
  var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('18869')
  var targetrow = 37;
  var targetcell = 2;
  var result = ss1.getRange(targetrow, targetcell);
  //var formula1 = result.getFormula();
  var findVal = ss1.getRange(targetrow, targetcell).getValue();

  if( findVal == ('#N/A') || findVal == ('Loading...')) 
  {
   var rangeToCopy = ss1.getRange(targetrow, targetcell);
   rangeToCopy.copyTo(ss1.getRange(targetrow, targetcell));
  } 
  else 
  {
   result.setValue(findVal);
  }
}

Upvotes: 0

Views: 2276

Answers (1)

iJay
iJay

Reputation: 4283

Setting a value on a cell will remove the formula on it. First select the range you want and then loop through the range checking each cell of it for whether formula has executed or not.

function testfindfunc1() 
{
  var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('18869');
  var range = ss1.getRange('B2:G10');
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {

      var currentValue = range.getCell(i,j).getValue();
        if( currentValue == "#N/A"|| currentValue == "Loading...") 
        {
          // if formula has not exicuted
         // do nothing, leave the cell as it is
        }
       else
       {
         //remove the formula and set the value
          range.getCell(i,j).setValue(currentValue);
       }
    }
  }  
}

Edit : Much faster method

function testfindfunc2() 
{
  var ss1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = ss1.getRange('B2:G100');
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  var valuesToSet = [];
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {

      var currentValue = range.getCell(i,j).getValue();
        if( currentValue == "#N/A"|| currentValue == "Loading...") 
        {
          // if formula has not exicuted
         // do nothing, leave the cell as it is
        }
       else
       {
         //remove the formula and set the value
         //range.getCell(i,j).setValue(currentValue);
         // store in an array
          valuesToSet.push([range.getCell(i,j),currentValue])
       }
    }
  }
  //setting values
  for(var rng in valuesToSet)
  {
    valuesToSet[rng][0].setValue(valuesToSet[rng][1]);
  }  
}

Upvotes: 3

Related Questions