Again, I'm looking into just giving back header information with each list entry, which should resolve everyone's issues. With a valid workaround of using the cells feed to look up the first row positionally though, this isn't a huge deal, in my honest opinion.
-Vic On Thu, Mar 17, 2011 at 8:22 AM, David Bullock <[email protected]> wrote: > Hey Jason, that's a great write-up, and a great illustration of the > pain of (as you nicely put it) content-dependent keys. > > Just dreaming here, but follows might be the definition of a list. > (Borrowing heavily from the deprecated Table API). > > <gs:data> > <gs:header height="1" nativenames="1" /> > <gs:column idx="1" key="id" name="Item" /> > <gs:column idx="2" key="descr" name="Description" /> > <gs:column idx="3" key="qty" name="Quantity" /> > <gs:column idx="4" key="ea" name="Each" /> > <gs:column idx="5" key="total" name="Total" c1="=qty * ea" /> > </gs:data> > > > The 'name' attribute would be the content-dependent key, while the > 'key' would be the content-independent key, conforming to existing > list-feed naming constraints. User changes to the sheet would modify > 'idx' and 'name', but 'key' would stay put (and the E-Tag and Last- > Modified of the resource containing this metatdata would change). > When updating this metadata via the ATOM/REST API, 'key' would be > required always, and either 'name' or 'idx' would suffice to establish > the binding (so long as 'name' could be distinctly found in the header > row when the metadata is POSTed or PUT or else a 4xx error). If > *both* 'name' and 'idx' are supplied, the index establishes the > binding, and the content of the header cell is rewritten. > > Where to put this metadata? How to integrate it with the existing > list feed? OK, here's my proposal in baby-steps: > > Put this metadata into entries in a separate feed. Call it the 'List > Definition' feed to avoid confusion with Tables. Now, for each > worksheet in the spreadsheet, there is an automatic List Definition, > which have the property that their 'key' is always a function of > 'name' according to the present name-munging rules in the List API. > These automatic List Definitions can't have their column 'key' values > set via the API (except as a side-effect of setting 'name') or else > they give a 4xx HTTP status. > > Existing clients of the API access the lists defined by these > 'automatic list definitions' in the same way they do now. > > I guess by now we have to fill in a few details: > > <entry> > <title>tableA</title> > <gs:listtype>auto</gs:listtype> > <gs:worksheet name="Invoices" /> > <gs:data> > <gs:header height="1" nativenames="1" /> > <gs:column idx="1" key="id" name="Item" /> > <gs:column idx="2" key="descr" name="Description" /> > <gs:column idx="3" key="qty" name="Quantity" /> > <gs:column idx="4" key="ea" name="Each" /> > <gs:column idx="5" key="total" name="Total" c1="=qty * ea" /> > </gs:data> > <content src="https://foo/bar"; type=http://schemas.google.com/ > spreadsheets/2006#listfeed" /> > </entry> > > Now, if we want, we can add more list definitions (unique by <title/> > within the feed), defining whatever we liked. Additional list feeds > are created for each definition we POST/PUT, using the key names for > the <gsx:*/> elements in the list entry. (The <gs:field name="" > idx=""/> was probably a bit of overkill in the defunct Records feed). > > Any cool things that might have come with the now-defunct Record feed > in terms of queries can be added to the list feed's querying abillity. > > I slipped in the 'c1' attribute to <gs:column/> ... that's all about > declaring the formula for a given column, but only in terms of other > fields in the row. Thought would need to be given to naming and > synatx, but think of it more as a template for a legal spreadsheet > expresion rather than a legal spreadsheet expression itself. It might > only be valid in the 'automatic' list feed definitions. Such columns > would exhibit the property that, when you inserted a row via the API > or via the GUI, it auto-filled the formula into the cell. > > That API would certainly solve my use-cases. What about you Jason? > > What about you, Google? > > cheers, > David. > > On Mar 17, 3:20 am, Jason Cipriani <[email protected]> wrote: > > I agree with David's sentiments. Personally, however, my use cases are > very > > different. > > > > I've been doing mostly web application development with PHP (and some > > desktop application development with Java). The Zend client library does > not > > support tables, only list and cell feeds. > > > > I map Google spreadsheet column names to application-specific column > names > > myself, all of my applications have a central configuration that defines > > this mapping, and I have the spreadsheet access hidden behind a layer of > > abstraction in my software. I abstract it away like this for two reasons: > 1) > > because, as David mentions, list feed keys are content-dependent and > > unreliable, and 2) so I can move away from Google spreadsheets some day > (the > > only reason I am using them is because I've been building applications on > > top of existing systems powered by Google Forms). > > > > What I do to combat unreliable column headers is each time I access the > list > > feed, I compare the key names (and order) with the last time I accessed > the > > list feed. This is a virtually free operation compared to the list feed > > access itself. If they change I have the application alert me via email > so I > > can go adjust the configuration. If the keys change to the point where > they > > might break the mapping in my application, the applications will report > an > > error to the user until I fix it -- for the applications I've been > writing > > it is more important to make sure the data is not corrupt rather than > making > > sure the apps have 100% uptime. Column changes happen infrequently enough > > that this works well for me, the only reason they change is because some > > users access the spreadsheets directly and change things they shouldn't > be > > changing (something I can't control). > > > > Because the spreadsheets I work with are generated by Google Forms, and > > because Google Forms puts the wording of the question in the column > headers, > > and because sometimes the questions are very, very long, sometimes I map > by > > column index rather than list key because I simply don't feel comfortable > > using 500 character key names that can change any time somebody, say, > fixes > > a grammatical error in a Google Forms question. > > > > Here is the algorithm I've been using to map column header values to > indexes > > and list feed keys. So far it has been working but it makes a lot of > > undocumented assumptions, and only works if there is at least one > non-header > > row in the spreadsheet (list feed must return a result). Weird pseudocode > > follows: > > > > === BEGIN === > > > > struct COLUMN { > > string header; > > int index; > > string listkey; > > > > }; > > > > headers = retrieve first row via cell feed, no empty cell return; > > columns = a list of COLUMN structures, initially empty; > > > > for each cell in headers { > > COLUMN column; > > column.header = cell.text; > > column.index = cell.columnnumber; > > // column.listkey not determined yet > > columns.add(column); > > > > } > > > > sort columns by .index; // no guarantees that cells returned in order. > > > > // now 'columns' is a list of nonempty column headers and column > > // indices, from left to right in the spreadsheet. > > > > listitem = retrieve a single row via list feed; > > int curindex = 0; > > > > // assumptions: > > // - keys for columns with empty headers will start with an underscore. > > // - list entry fields are in order left to right, and correspond with > > // column headers. > > for each field in listitem { > > if (field.key starts with '_') > > continue; // skip > > columns[curindex].listkey = field.key; > > > > } > > > > // columns now contains column header, index, and list key name for each > > // column. this data can be cached, and this cache invalidated if the > > // application detects a change in the list entry keys, which it can do > > // as it retrieves items. > > > > === END === > > > > *So for me, it would solve a ton of problems if the list feed simply had > a > > new column metadata element that contained column header names, column > > indices, and key names.* > > > > It's all very messy and hard to work with right now. > > > > Jason > > > > > > > > On Wed, Mar 16, 2011 at 9:01 AM, David Bullock <[email protected]> > wrote: > > > In the lately deprecated tables API, if it had ever worked the way I > > > read that it was intended to work (which it once did), one wouldn't > > > ever need to discover the column names by reading the header row. > > > Instead, one would say something like the following when defining a > > > table: > > > > > *in future, for my own convenience, I will refer to column B as > > > 'Description'" > > > > > The next day, when some sensible spreadsheet user inserts a column to > > > the left of B and renames the new C1 to "Item Name", your app doesn't > > > break. Just like a named range, the sheet adjusts the reference so > > > that the name your code relies on - 'Description' - now refers to > > > column C ... and further, while the name your code chose and the value > > > of the header cell C1 are now different, it's no big deal - the column > > > has the same *semantic* meaning. You're just isolated from changes in > > > its position and its user-facing description. > > > > > How nice would it have been? Very nice. We could even have defined > > > table P to include columns A-F, and table Q to include just columns A > > > and E (useful since the Spreadsheet API doesn't support partial > > > response/update). > > > > > Instead, Google screwed it up by introducing a two-way binding between > > > the name the application chose to refer to the colum, and the value of > > > the header cell. Oomph. Quick - back to the cells feed to see if we > > > can work out (somehow) what to call these columns when addressing them > > > via the list feed! > > > > > All kinds of wierd behaviour ensued (I could point you to a few bugs I > > > personally filed), among the worst of which was the stupid, stupid > > > message "This operation is not permitted because it would alter the > > > structure of this table" when the spreadsheet user tried to do such > > > nefarious things as oh, fill-down a formula cell, or cut and paste a > > > range. > > > > > Having thus reduced the grand concept of tables to an annoying > > > repetition of the List API, some grown up in the playground obviously > > > canned it. > > > > > And now we are stuck with this rigmarole of *looking up* the column > > > names ... and Google are 'considering a feature that would allow you > > > to fetch the header row'. Just let us bind our application-specific > > > names to column indexes already, and adjust them the way you do named > > > ranges. > > > > > But it's like talking to a brick wall ... > > > > > David. > > > > > On Mar 16, 6:38 am, Vic Fryzel <[email protected]> wrote: > > > > Unfortunately, at the moment, you must use the cells feed to > accomplish > > > > this. I'm considering a feature that would allow you to fetch the > header > > > > row with the lists feed, but I don't have an ETA yet. > > > > > > -Vic > > > > > > On Fri, Mar 4, 2011 at 9:56 PM, Jason Cipriani < > [email protected] > > > >wrote: > > > > > > > Using PHP Zend bindings for spreadsheet API. > > > > > > > If I have a WorksheetEntry; I can use getContentsAsRows() to get > all of > > > the > > > > > entries in that worksheet's list feed. > > > > > > > The keys in each list entry are the column headers converted to > > > lowercase > > > > > with all non alpha-numeric characters stripped out. > > > > > > > How do I get the real column names for this worksheet entry, and > the > > > > > corresponding list entry keys for each column? Alternatively, a way > to > > > get > > > > > the real column name given the list entry key would work too. > > > > > > > My goal is to be able to both display the real column header text > > > > > (spreadsheet column headers are Google Forms questions) to the user > of > > > my > > > > > application, and also to know what the list entry key is for each > > > column so > > > > > I can display the values of the columns as well. > > > > > One way I've been toying with is to use a cell feed to get the > first > > > row > > > > > and read the headers that way. > > > > > > > But then, how do I build a correspondence between the column > headers > > > and > > > > > the list entry values? > > > > > > > Am I guaranteed that the values in a list entry will be returned in > > > > > left-to-right GUI order, and empty values will be present (value > count > > > will > > > > > be equal to column count)? > > > > > > > Being able to display the column headers and the corresponding > values > > > in an > > > > > application seems like a very reasonable goal, I feel like I'm > missing > > > > > something because this is surprisingly clunky... > > > > > > > Thanks! > > > > > Jason- Hide quoted text - > > > > > > - Show quoted text -- Hide quoted text - > > > > - Show quoted text - >
