Google Spreadsheet - To execute a custom function only in the expanded row group

Question

I have a form where my team fills in daily sales activity data. I have to fetch the data of each member and display it another sheet. Though I can do it with query, I have written a custom function that takes 3 arguments and search in the form responses and returns an array of data. Daily(name,week,day) is my function. Every time the formula is called, the search happens on the entire form response data.

Now the issue is the formula is used 13*365(13 people ad 365 days) times in the sheet.

I am unable to stop the function from executing again and again. I have grouped the rows month wise, also created named ranges. either of the ways, can I execute the formula only in this expanded rows/named ranges using Apps script???? Attached the sheet

If the function is being called in a cell, it will be executed no matter what. What you could do is to not call this function for non-expanded rows, or modify your function so that it is called in less cells and it returns data for multiple cells. More information would be needed though, in order to help you here. Please consider providing a copy of the spreadsheet, clearly indicating the desired outcome. — Iamblichus
@lamblichus - Returns data to multiple cells needs a kind of sorting on days/date of the week. Please do take a look at the sheet. docs.google.com/spreadsheets/d/… — Nemo
Have you thought of using an onEdit trigger instead of a custom function? — Iamblichus
Yes, I tried using all three - On Edit, On change, On Form submit - Doesn't stop running :( — Nemo
What do you mean by Doesn't stop running? The idea behind implementing a trigger is to get rid of the custom functions and do the same actions using a trigger. What doesn't stop running in this case? — Iamblichus

Malte Kleimann Malte Kleimann · Accepted Answer · 2021-06-16T13:29:10

I wrote the code, like i would go from row 1 to max and check every row. If it is not hidden, you can execute your code, else its skipped.

Its working for me. Depends on your code you have to adaped it.

function HiddenRowSkipper() {
  var devTest = SpreadsheetApp.getActive().getSheetByName("DevTest");

  var maxRow = devTest.getMaxRows();

  for(var i = 1; i < maxRow; i++){
    if(devTest.isRowHiddenByUser(i) == false){
      //Execute your code on the Row "i" 
    }
  }
}

Google Spreadsheet - To execute a custom function only in the expanded row group

1 Answers