I am working on a google sheet where i am using a vlookup formula. I want to replace formulas with results, only if the result contains value in it, else formula should stay intact. The below code is working but in some rows the formula is getting removed even though these cells are not having any value. While for other rows the formula is staying intact becoz there is no value for the formula result. I want to freeze output for complete range(E2:AI211) but want the formula to stay intact if there is no value. I have total 31 columns & 211 rows in the google sheet.

If i change the range to just one column say range(E2:E211) & change this line of code as : var cell = range.offset(i, 0, 1, 1); then for this single column the formula is staying intact wherever there is no value in that column. But when i input range as multiple columns then in some rows the formula is getting removed while in some rows the formula remains intact. I couldn't figure out what really could be the issue. Will really appreciate your help.


function freezePositiveOutput(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Feb 2019");
  var range = sheet.getRange("E2:AI211");
  var values = range.getValues();
    for (var i = 0; i < values.length; i++) {
    if (values[i][0] > 0) {
      var cell = range.offset(i, 0, 1, 31);   
      cell.copyTo(cell, {contentsOnly:true});

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.