How to fix code to remove formulas but leave values in all sheets APART from those specified

user_11208127 0
Viewed 110

I want to remove formulas and paste values in all sheets apart from Base Data and Template.

function myFunction() {
  var ss = SpreadsheetApp.getActive(); 
  var allsheets = ss.getSheets();
  for(var s in allsheets){
    var sheet = allsheets[s];
    if(
      (sheet.getSheetName() == "Base Data") || 
      (sheet.getSheetName() == "Template")  
    );
    function removeFormulas() {
      SpreadsheetApp.getActive().getSheets()
        .forEach(function (sh) {
          var r = sh.getDataRange()
          r.copyTo(r, {
            contentsOnly: true
          })
      })
    }
  }
}

The desired outcome would be to leave the formulas in Base Data and Template alone but for the other tabs to have them removed and the values maintained.

google-sheets if-statement loops javascript
1 Answers

Untested - but it might give some idea.

The main difference is to test for sheet names NOT equal to your listed sheets, then everything other sheet should have copy, contents only.

function myFunction() {
  var ss = SpreadsheetApp.getActive(); 
  var allsheets = ss.getSheets();
  for(var s in allsheets){
    var sheet = allsheets[s];
    if(sheet.getSheetName() != "Base Data" || sheet.getSheetName() != "Template") {

            var thissheet = ss.getSheetbyName(sheet.getSheetName());
            var lR = thissheet.getLastRow();
            var LC = thissheet.getLastColumn();
            var range = thissheet.getRange(1,1,LR,LC);
            range.copyTo(range, {contentsOnly: true});

        }
    }
}

UPDATE: The following script has been tested and runs as per the OP's expectations. Sheets "Base Data" and "Template" are excluded from the copyTo method.

There are a few things to note:

  • The if statement combines the test for "Base Data" and "Template" with an AND rather than an OR. The required logic is to establish that the current sheet name is neither of the named sheets. AND has the effect of treating this as a whole; OR has the effect of allowing one or the other to be true, but doesn't enforce the condition of them both being true.
  • I made a mistake by including getSheetbyName(). This was unnecessary because the loop has already created the context for the sheet.
function so5850593801() {

  // setup spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get all the sheets
  var allsheets = ss.getSheets();

  // loop through the sheets
  for(var s in allsheets){
    var sheet = allsheets[s];
    var sheetname = sheet.getSheetName();

    // don't process Base data or Template
    if(sheetname !== "Base Data" &&  sheetname !== "Template") {
      var LR = sheet.getLastRow();
      var LC = sheet.getLastColumn();
      var range = sheet.getRange(1,1,LR,LC);
      range.copyTo(range, {contentsOnly: true});
    }
  }
}
user_13305600
Related