CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
WasHere
WasHere

Reputation: 59

How to flattern only visible (non-filtered) cells in Google app script/ Google sheets?

Currently, I have a basic spreadsheet: https://docs.google.com/spreadsheets/d/1nlLdD48PfqrhyQzmJrhx_Tlw73C194YUqKeRkXoCOLg/edit?usp=sharing, and I have made a script that flattens the values of the formulas in the spreadsheet:

  function freezeValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  // or whatever name
  var range = sheet.getRange("A2:C");    // assuming data appears in A2-C 
  range.copyTo(range, {contentsOnly: true});
}

The issue with my current script is that it flattens all formula, even if the row is hidden by the filter, is there a way I can edit my script so that it will only flatten visible cells?

I found this operation for google app script isRowHiddenByFilter, but I am not sure how I could put it into my current script.

P.S. I will likely use this for different filters, so am not looking for a workaround. For example, if you look at my example spreadsheet, I wouldn't want the app script to specify "background-colour = white" etc)

Upvotes: 1

Views: 929

Answers (1)

ziganotschka
ziganotschka

Reputation: 26806

You can loop through all rows and query either it is hidden before copying

Sample

function freezeValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  // or whatever name
  var firstRow = 2;
  var firstColumn = 1;
  var lastColumn = 3;
  var range = sheet.getRange(firstRow, firstColumn, sheet.getLastRow() - firstRow + 1, lastColumn - firstColumn +1);    // assuming data appears in A2-C 
  var rowHeight = range.getHeight();
  for (var i = 0; i < rowHeight; i++){
    if(!sheet.isRowHiddenByFilter((firstRow+i))){
      var row = sheet.getRange((firstRow+i), firstColumn, 1, lastColumn - firstColumn +1);
      row.setValues(row.getValues());
    }
  }
}

This sample copies the non-hidden rows one by one, thus it makes more sense to use the method setValues() instead of copyTo()

Upvotes: 3

Related Questions