How to SetFormula() to Cell in row hidden by filter applied in google sheet?

2
votes

Can anyone help with this , I found that google script can SetValue to hidden filtered rows but SetFormula doesn't work , the output is blank cell: I have sheet applied filter from A1 to A999 , the only showed rows as per certain condition are ROWS 1 to 4 as the screenshot, row 10 is unfiltered and hidden , if I run the below code, nothing resulted in cell C10....

sheet filtered-showed rows from 1 to 4 /////

code run - and removed filter to check if C10 received function or not - found blank without any formula set to it

if I removed the filter , and run the code ...C10 receive the function please refer to screenshot

without filtering - and C10 is showing on sheet - run the code received the formula in C10

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getSheetByName('Sheet1') ; 

var formula=sheet.getRange('C10').setFormula('=iferror(Vlookup($A5863,TRANSACTIONS_QUEUE!$B:$E,3,0),"")');
}
1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

You can temporarily remove the filter and set it back after setting the formula

Sample:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Sheet1') ; 
  var filter = sheet.getFilter();
  var filterColumn = 1;
  var criteria =  filter.getColumnFilterCriteria(filterColumn);
  filter.removeColumnFilterCriteria(filterColumn);
  var formula=sheet.getRange('C10').setFormula('=iferror(Vlookup($A5863,TRANSACTIONS_QUEUE!$B:$E,3,0),"")');
  filter.setColumnFilterCriteria(filterColumn, criteria)
}