How to apply a filter to a spreadsheet from script

Question

How to apply a filter to a spreadsheet from script

Navigation

#1 by (0 votes)

1

Greetings I am trying to generate a filtered view according to the user that accesses my spreadsheet, so far I have only managed to make this work:

function onOpen(){
 var currentUser = Session.getActiveUser().getEmail();
 SpreadsheetApp.getUi().alert("Bienvenido "+ currentUser);
 var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = spreadSheet.getSheets();
  if (currentUser == "[email protected]"){
   sheets[1].hideSheet();
   var sheet = spreadSheet.getSheetByName("Trabajos");
   var lastRow = sheet.getLastRow();
   var range = sheet.getRange("C2:C"+lastRow);
    //var filterRange = {};
    for (i=1; i<lastRow; i++){
     var filterQuestion = sheet.getRange(i,3).getValue();
      if (filterQuestion == "Proyecto"){
        sheet.hideRows(i);
      }
     }    
  }
}

But the rows can be displayed manually, I'm trying this other code but it does not work for me at all ...

function onOpen(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var filterSettings = {};

  // The range of data on which you want to apply the filter.
  // optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex
  filterSettings.range = {
    sheetId: ss.getActiveSheet().getSheetId()
  };

  // Criteria for showing/hiding rows in a filter
  // https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria
  filterSettings.criteria = {};
  var columnIndex = 2;
  filterSettings['criteria'][columnIndex] = {
    'hiddenValues': ["Proyecto"]
  };

  var request = {
    "setBasicFilter": {
      "filter": filterSettings
    }
  };
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());

}

and I get this error: Failed execution: ReferenceError: "Sheets" is not defined. (line 25, file "Code") [0.003 seconds of total execution time]

What am I jumping? Is there any other solution ???

google-apps-script hide

asked by Karen Lewis 24.04.2018 в 07:33

source

1 answer

Doubt with decorators in Python CMD - Skip login when connecting via FTP

score 0 · Answer 1

The error is most likely due to the fact that you have not yet activated the advanced service of Google spreadsheets.

The way to enable it is explained in English at link

Where it says (my translation, original text below):

Enable advanced services

To use an advanced Google service, follow these instructions:

In the script editor, select Resources > Advanced Google services ...

In the dialog that appears, click the on / off button next to the service you want to use.

At the bottom of the dialog, click on the link to the Google APIs Console.

In the console, click on the filter box and type part of the name of the API (for example, "Calendar"), then click on the name you see. On the next screen, click Enable API.

Close the API Console and return to the script editor. Click on the dialogue The advanced service that you enabled now is available in the autocomplete.

Enabling advanced services

To use an advanced Google service, follow these instructions:

In the script editor, select Resources > Advanced Google services ....

In the dialog that appears, click the on / off switch next to the service you want to use.

At the bottom of the dialog, click the link for the Google API Console.

In the console, click on the filter box and type of the name of the API (for example, "Calendar"), then click the name once you see   Item. On the next screen, click Enable API.

Close the API Console and return to the script editor. Click OK in the dialog. The advanced service you are enabled is now available in   autocomplete.