CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Adam Thomas
Adam Thomas

Reputation: 11

Google Sheets Script to Clear/Remove Filters

I have a script in a Google Sheet that clears/removes the filters on all tabs of the Sheet upon opening. It accomplishes this by grabbing the top row, then deleting it and then replacing it without the filters.

The script works, however, it seems like it is too much for Sheets to handle with 6 tabs. When it runs, it leaves 2-3 tabs completely gray without any data displayed. When you double click a gray cell, it returns the following message: "These cells are currently being loaded. Please retry when loading completes". No matter how long I wait, the data doesn't display. If I refresh the page 3 or 4 more times, all tabs will eventually display data.

I'm hoping to either clean up the code or come up with a new script in order to not have to refresh the page. I used the script on a sheet with only two tabs and it worked fine, so I don't think it has to do with the amount of data.

Fair warning: I am an extreme beginner when it comes to this, so I'm sure the code and formatting are pretty ugly.

Here is a link to a copy of the Sheet:

https://docs.google.com/spreadsheets/d/1cg4wVIHPumahQgf5gorc4JQXsG_6l4eg3OhONABx8gs/edit#gid=369791415

And here is the code for the script (it is a repeating chunk of code for each tab):

function myFunction() 
{
var row2 = 1 //the row with filter 
var rowBefore2 = row2 
var Sheet2 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet2.getSheets()[1]);

Sheet2.insertRowBefore(row2); //inserts a line before the filter
row2++;

var Line2 = Sheet2.getRange(row2 + ":" + row2); //gets the filter line
Line2.moveTo(Sheet2.getRange(rowBefore2 + ":" + rowBefore2)); //move to
new line
Sheet2.deleteRow(row2); //deletes the filter line - this clears the 
filter

var row3 = 1
var rowBefore3 = row3 
var Sheet3 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet3.getSheets()[2]);

Sheet3.insertRowBefore(row3);
row3++;

var Line3 = Sheet3.getRange(row3 + ":" + row3);
Line3.moveTo(Sheet3.getRange(rowBefore3 + ":" + rowBefore3));
Sheet3.deleteRow(row3);

var row4 = 1 //the row with filter 
var rowBefore4 = row4 
var Sheet4 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet4.getSheets()[3]);

Sheet4.insertRowBefore(row4);
row4++;

var Line4 = Sheet4.getRange(row4 + ":" + row4);
Line4.moveTo(Sheet4.getRange(rowBefore4 + ":" + rowBefore4));
Sheet4.deleteRow(row4);

var row5 = 1 //the row with filter 
var rowBefore5 = row5 
var Sheet5 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet5.getSheets()[4]);

Sheet5.insertRowBefore(row5);
row5++;

var Line5 = Sheet5.getRange(row5 + ":" + row5);
Line5.moveTo(Sheet5.getRange(rowBefore5 + ":" + rowBefore5));
Sheet5.deleteRow(row5);

var row6 = 1
var rowBefore6 = row6 
var Sheet6 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet6.getSheets()[5]);

Sheet6.insertRowBefore(row6);
row6++;

var Line6 = Sheet6.getRange(row6 + ":" + row6);
Line6.moveTo(Sheet6.getRange(rowBefore6 + ":" + rowBefore6));

Sheet6.deleteRow(row6);

var row7 = 1 
var rowBefore7 = row7 
var Sheet7 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet7.getSheets()[6]);

Sheet7.insertRowBefore(row7);
row7++;

var Line7 = Sheet7.getRange(row7 + ":" + row7);
Line7.moveTo(Sheet7.getRange(rowBefore7 + ":" + rowBefore7));

Sheet7.deleteRow(row7);

}

Upvotes: 1

Views: 2172

Answers (2)

Rodger
Rodger

Reputation: 841

Loop through your sheets instead of referencing them directly. Then stick your code that you want to do... it should look something like this. I copied your sheet and put the code in there and shared it back to you. It runs through in a few seconds and there are any grey pages. I don't have access to the sheet you are doing an importrange on. So if you find that it is still grey for you with this script, that might be related to the issue.

function removeFilters() {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var row1 = 1;
    var lastColumn = 20
    for (var i = 0; i < sheets.length ; i++ ) {
        var sheet = sheets[i];
      sheet.activate();
      lastColumn = sheet.getLastColumn()
      sheet.insertRowBefore(row1); //inserts a line at the top
      sheet.getRange(row1+1, 1, 1, lastColumn).moveTo(sheet.getRange("a1"));
      Logger.log("value of i: " + i + " and active sheet is: " + sheet.getName());
      sheet.deleteRow(row1+1);
        }       

}

Upvotes: 1

Ed Nelson
Ed Nelson

Reputation: 10259

Get an array of all sheets and loop through them:

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets =ss.getSheets() //get array of all sheets
  for(i=0;i<sheets.length;i++){ //for each sheet
 var row2 = 1 //the row with filter 
 var rowBefore2 = row2 
 var s=SpreadsheetApp.setActiveSheet(ss.getSheets()[i]);

s.insertRowBefore(row2); //inserts a line before the filter
row2++;

var Line2 = s.getRange(row2 + ":" + row2); //gets the filter line
Line2.moveTo(s.getRange(rowBefore2 + ":" + rowBefore2)); //move to new line
s.deleteRow(row2); //deletes the filter line - this clears the filter
  }}

Upvotes: 0

Related Questions