I actually do create a new column of type string, but somehow the data does not seem to be available when I read in the spreadsheet. I've tried a lot of approaches to reading using getValue and getFormattedValue. Some of my spreadsheets don't seem to pass through all the data I can see. For example, I created a simple gadget that just reads and displays data from the spreadsheet. You can see it here:
http://spreadsheets.google.com/pub?key=roHMMPzRR6EnevCpWioVznA The gadget xml is here http://susa.scottgilkeson.com/gadgets/datatest.xml In the test output, which shows each row's data as returned by getValue and getFormattedValue, you can see that most of the 'SE' cells don't appear in the Year row, and the second cell of the rows starting with 'By:' don't either. On that sheet, you can also see the effect on my custom gadget (you'll need to move the test gadget), which uses the By rows to divide the data into tables, deletes the SE rows (which show standard errors) and transpose the table so that it can be charted by the linechart visualization. If there is anything I can do about this problem, I'd love to know about it. The same sort of scenario works as expected on other spreadsheets, so I assume there is something about making the spreadsheet that is different, but I have no idea what it might be. I also have a problem with the custom gadget in that the chart renders in a tiny area when first drawn on the first tab, but if you click to different tabs and back again, it works correctly. I figure that is more a gadget/tabs problem, and this is not the right forum to ask about it, so I will post that issue to another forum. But if you have any thoughts on that, I'm very happy to hear them. This custom gadget is at: http://susa.scottgilkeson.com/gadgets/SUSAlinechart.xml I have a number of enhancements I want to make, and then plan to post it to Google visualizations. Thanks much for any advice you can give me. Scott On Apr 30, 3:57 am, VizBoy <[email protected]> wrote: > Hi, > > Unfortunately, there's no ideal solution i can offer you. > You can do these manipulations in JavaScript (add a new column to the > datatable, of type 'string' and copy the data to it). > But if you use a gadget, you'll have to create a custom gadget in order for > this to work. > > It is in our feature request list to have better support for such scenarios. > > Regards, > VizBoy. > > On Tue, Apr 28, 2009 at 7:06 PM, Scott G <[email protected]> wrote: > > > Yes, thanks, that helps a lot. I saw the Headers setting that defaults > > to Auto in the Edit prefs, and I tried setting it various ways, but I > > never realized it meant "how many rows to skip." I thought is was > > somehow saying which row to use as the labels for data points. > > > I have a problem with that too, probably related to the 'not typed' > > you mention. Even using the standard Line Chart gadget, I couldn't get > > the Year to show up as a label on the x axis--it would show up as a > > line of data, which of course messed up the chart. I eventually found > > that if I set the line to be plain text, it would pick it up, but in > > changing formats back and forth, somehow it ended up formatting my > > Years with commas, and I'm still having a problem getting everything > > to show up properly at the same time (I have some years with > > footnotes, like "1990 [1]" so they are affected differently than years > > like "1997"). > > > Thanks > > Scott > > > On Apr 28, 5:45 am, VizGuy <[email protected]> wrote: > > > Please read the following page about Google Spreadsheets as a data > > source: > >http://code.google.com/apis/visualization/documentation/spreadsheets.... > > > > In general, your issues are not related to the GadgetHelper, but more to > > the > > > fact that Spreadsheet are not typed, and so there are some heuristics > > that > > > have to be done in order to analyze the data format, and if there are > > header > > > rows. > > > Please read specifically about the header= parameter, that lets you > > override > > > the heuristics and explicitly tell the number of header rows from the > > > selected cells range. > > > > Hope this helps. > > > > Regards, > > > VizGuy > > > > On Sun, Apr 26, 2009 at 6:30 PM, Scott G <[email protected]> wrote: > > > > > I'm working on a spreadsheet gadget that will take some standard text > > > > in a spreadsheet, format it and wrap it around a data chart. I'm > > > > planning to use this on a blog (and make it available to others for > > > > their blogs) and I think the easiest way to go is to create Google > > > > Docs spreadsheets in a standard format, create my special gadget on > > > > the spreadsheet, then get the publish script to embed it elsewhere. By > > > > using createQueryFromPrefs I hope to avoid having to reference the > > > > spreadsheet key explicitly in the XML, thereby avoiding having to > > > > clone the XML for each distinct spreadsheet. > > > > > Here's the rub: it seems that the table I get from getDataTable starts > > > > with the first numeric row of the table, skipping all the text rows > > > > above it. I can't even get my header row, which is Years, unless I > > > > format it as a number (which puts a , in the year, like 2,007). > > > > > The Range defined in the Edit section when I set up the gadget clearly > > > > specifies A1:W20, but what I get back is A7:W20, where my actual data > > > > starts (or if I make row 7 plain text, like I want to, I only get > > > > A8:W20). Interestingly, I do get any text-only rows *after* the data > > > > rows (so I could move my text rows lower, I assume, but that still > > > > doesn't help the Year header row problem). Is this a special feature > > > > of the GadgetHelper, to assist the visualization code? Is there some > > > > way to turn it off, or at least ask for one text row before the > > > > numeric rows? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Google Visualization API" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en -~----------~----~----~----~------~----~------~--~---
