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....
