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 -
