Looking for examples how to use the Google Sheets

问题:

I am trying to understand how to construct an AddFilterViewRequest in the Google Sheets API. However, there don't seem to be any good examples that I can locate in any programming language which demonstrate how it is used.

Specifically, I am trying to understand the FilterCriteria object, and what I need to set hiddenValues and condition to.

In my application I am trying to construct a filter that will only show the rows where the cell in the column I have selected is not empty. I can do this manually in the Google Sheets editor, and I want to replicate the same settings in my program.

This is the code as it stands...

Request request = new Request();
request.AddFilterView = new AddFilterViewRequest();
request.AddFilterView.Filter = new FilterView();
request.AddFilterView.Filter.FilterViewId = 0;
request.AddFilterView.Filter.Title = "Hide rows with errors";
request.AddFilterView.Filter.Range = new GridRange();
request.AddFilterView.Filter.Range.SheetId = 0;
request.AddFilterView.Filter.Range.StartColumnIndex = 8;
request.AddFilterView.Filter.Range.EndColumnIndex = 9;
FilterCriteria criteria = new FilterCriteria();
//criteria.Condition = BooleanCondition;
criteria.HiddenValues = new List<string>();
//criteria.HiddenValues.Add("item");
IDictionary<string, FilterCriteria> criteriaDictionary = new Dictionary<string, FilterCriteria>();
//criteriaDictionary.Add("string", criteria);
request.AddFilterView.Filter.Criteria = criteriaDictionary;

The lines that are commented out at the moment are the ones that I can seeking assistance with. I am also trying to find out what the string variable should be for the criteriaDictionary.

回答1:

After posting this question I realised one way I could answer it myself would be to reverse engineer an existing spreadsheet where this filter was already applied. Based on that, I now have the following working code...

FilterCriteria criteria = new FilterCriteria();
criteria.Condition = new BooleanCondition();
criteria.Condition.Type = "NOT_BLANK";

IDictionary<string, FilterCriteria> criteriaDictionary = new Dictionary<string, FilterCriteria>();
criteriaDictionary.Add("8", criteria);

Request request = new Request();
request.AddFilterView = new AddFilterViewRequest();
request.AddFilterView.Filter = new FilterView();
request.AddFilterView.Filter.FilterViewId = 0;
request.AddFilterView.Filter.Title = "Hide rows with errors";
request.AddFilterView.Filter.Range = range1;
request.AddFilterView.Filter.Criteria = criteriaDictionary;
requests.Add(request);

I don't know why they key value was set to 8, but that's what it was in the existing spreadsheet (and that sheet only had one Filter View in it). Anyway I copied it, and it works, so I haven't found a need to change it.

回答2:

I don't have a good setup right now to construct the appropriate code here (perhaps someone else can amend this or add an answer with the code inline), but in general your best bet right now for figuring out the "right way" to construct complex objects like this in the API is to create one with the UI and then do a spreadsheets.get call. You can look at the resulting object and mimic the FilterView (with your own modifications as necessary).