7 Replies Latest reply on Sep 7, 2016 8:33 AM by Marco Ardito

    google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround?

    Marco Ardito Master

      Hi,

       

      I have a working google spreadsheet model (see google spreadsheets generated by google forms: error)

       

      I noticed that if the google spreadsheet had a filter set on a column, and thus it shows in google app less rows (only those selected by the filter) than it really has, also my teiid model only gets filtered rows...

       

      Is it a bug, a configuration option which I don't know about, or there is any workaround?

       

      screenshot:

      googless_filtered_rows.jpg

       

      Marco

        • 1. Re: google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround?
          Steven Hawkins Master

          > Is it a bug, a configuration option which I don't know about, or there is any workaround?

           

          I can't find a reference on this.  There's nothing that we're doing on our end to detect column filters or alter the query sent to google, so this is apparently what the google api expects.

          • 2. Re: google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround?
            Marco Ardito Master

            Weird...

            I just maybe found a workaround, but it requires users collaboration, so it's far from optimal

            I never used those google spreadsheets before, so I'm learning all (but I already have a real use case for my users that need all data rows...)

             

            It appears that, instead of (or adding to) setting "filters" directly on the spreadsheet (as I did in the picture above), the owner can also create "filtered views" (under "data" menu)

            Those are a way to apply "predefined" filters sets, that  are also available to other users sharing the spreadhseet.

             

            It seems that in this way, applying those "filtered views", the resulting filtered rows are just a "current user" setting, and any other user can have his personal view, different from the others, while my first approach seems to set a fitered view that is what all other users see...

            At least as default: because even on this "base filtered" views, other users (even anonymous) sharing the spreadsheet can still create their "filtered views" acting on the whole data rows (even those "base filtered")...

             

            Another way around could be to replicate (periodically or after every cell edit) the whole data set into another (protected) worksheet or spreadsheet, but I woul like to avoid this complex setup, if possible...

             

            I'll dig a bit more, who knows, maybe the api can allow also teiid to create its own "filtered view" that show all rows....?

             

            Marco

            • 3. Re: google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround?
              Steven Hawkins Master

              > I'll dig a bit more, who knows, maybe the api can allow also teiid to create its own "filtered view" that show all rows....?

               

              If you find something let us know and we can add a doc note.

              • 4. Re: google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround?
                Marco Ardito Master

                Ok, I'll try to expand my knowledge on this matter... it doesn't harm...

                How can I dump all relevant requests teiid makes to my google account to get the sheets data?

                 

                Marco

                • 5. Re: google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround?
                  Steven Hawkins Master

                  There isn't a direct log made by the translator, but it should be logged by HttpClient - under the org.apache.http.client context

                  • 6. Re: google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround?
                    Marco Ardito Master

                    As suggested by google docs support forum, I posted this http://stackoverflow.com/questions/36907254/if-a-google-spreadsheet-worksheet-has-an-active-column-filter-jboss-teiid-re… where "Google engineers monitor and answer questions tagged"

                     

                    let's see if something comes out...

                     

                    Marco

                    • 7. Re: google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround?
                      Marco Ardito Master

                      Steven Hawkins ha scritto:

                       

                      > I'll dig a bit more, who knows, maybe the api can allow also teiid to create its own "filtered view" that show all rows....?

                       

                      If you find something let us know and we can add a doc note.

                      After some time experimenting, I update this thread with a some new info, and a workaround, so I will mark this as the "solution".

                       

                      First, I confirm that the problem really exists: using google spreadsheet datasources in Teiid you could face this issue: they can return less rows than those really present on the worksheet, and change anytime, if some "Google docs web interface user" applies or changes a "column filter", a sort of what you can also use in other spreadsheet products like Microsoft Excel or Libreoffice, etc. where you apply a filter to (eg) select only the rows that contain a string in a column.

                       

                      This happens also for other "Google docs web interface user", by the way: if you share the spreadsheet with other users (you can share google docs with both logged and anonymous users): whenever you apply a "column filter", immediately all other users will see the spreadhseet rows, without notice, in their browser, change, reflecting the "column filter" applied by another user... disappearing rows..?! oh my... how can one share data like this?

                       

                      There is one "way", to b ehonest: Google spreadsheet have two different "filtering methods", called "Filter" and "Filtered views". While above I was describing the use of "Filters", and the consequences for any other user sharing access to that data, there is another way to allow users to apply filters without affecting other users: "Filtered views": you can define (and also save) any number of these, which create a basically "user indipendent" view of the worksheet, allowing you manipulate all worksheet data, including applying column filters, and also apply cells formats and such, and all this editing is private to your user, not affecting other sharing user's view of the same data.

                       

                      Here is part of my conversation (see this link to Google Groups ) about this issue with a user (he's user "Troy", I'm user "apiform") on Google's product support forum:

                      "Filters: You must not use them. They will affect other people's view.

                      Filter views: You can use them. They will not affect other people's view. "

                       

                      Now, I tried what he suggested, a few posts later: create another worksheet, and use a special formula which is able to dynamically replicate a range of the original worksheet.
                      You can do this in two ways.

                      1) link from the same spreadsheet:

                      - on the same Google spreadsheet (the workbook, in Excel terms), where you have the "data" sheet (the worksheet, in Excel terms)

                      -  create a new sheet aside, called "copy" and put nothing in "copy" cells but this formula in the cell A1: "={'data'!A:Z}"

                      - all "copy" cells will contain the same values as "data", but just cells content, without any cell format.

                       

                      2) link from another spreadsheet:

                      - on a new Google spreadsheet (the workbook, in Excel terms), where you have just a the "copy" sheet  (the worksheet, in Excel terms)

                      -   put nothing in "copy" cells but this formula in the cell A1: "IMPORTRANGE("https://docs.google.com/spreadsheets/d/zworksheet-key>", "data!A:Z")"

                      - all "copy" cells will contain the same values as "data", but just cells content, without any cell format.

                      - see this page for reference IMPORTRANGE - Docs editors Help

                       

                      Then, create a Teiid data source (resource adapter and connection definition) to the spreadsheet where "copy" sheet is: You will always get all "data" rows, even if a "Google docs web interface user" applies some column filter" or else, whatever.

                      There are drawbacks, though:

                      1) you define a precise range in that formula above, so if some one adds columns beyond the original range, you will need to adapt the formula, and probably also your teiid VDB

                       

                      2) this is complicated to explain: new Google spreadsheets are created with just 1000 rows, but below the last row you'll find a button to increase by other 1000 rows, and so on.

                      Since the formula above links a "A:Z" range (all columns from A to Z, all rows), when it is applied to a 1000 rows sheet, it appears to link the same row number the linked sheet has in that moment, so you will get 1000 copied rows.

                      If later someone "expands" maximum rows in the original sheet, from 1000 to 2000, the formula has to be applied again, or it seems to show only the original 1000 rows, and thus in Teiid you will get just those 1000 rows

                       

                      3) when a cell of a column in the VDB contains date or numeric values and is empty in Google spreadsheet, it will be show a "<null>" string value instead. You will need to limit rows to "not null" key values, to avoid.

                       

                      In general, if "Google docs web interface user" are editing the same spreadsheet, you are importing in a VDB in Teiid, many things can go wrong, so keep this in mind...

                       

                      I hope this kind of trick will continue to work in the future, and help others, too....