On Thu, Oct 14, 2010 at 8:17 PM, Traub, Adam <[email protected]> wrote:
> Jinji, > > > > I understand there’s a bug with the way the formulas are handled, but > copying the values defeats the purpose of having the formulas; > Yes, I know. Sorry for not having a better answer... I’d love to hear when this is figured out. > > > > For your trouble shooting, the following formulas come through just fine: > > > > > =Arrayformula( MMULT( iferror(value(C2:H)) ; TRANSPOSE(SIGN(COLUMN(C2:H))))) > > > > And > > > > =CONTINUE(I2, 2, 1) > > > > Hope this helps the bug tracking! > > > > Cheers, > > Adam > > > > > > Adam Traub | Systems Librarian | St. John Fisher College | > [email protected] | (585)385-8382 > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Jinji > *Sent:* Thursday, October 14, 2010 11:09 AM > > *To:* [email protected] > *Subject:* Re: [visualization-api] Re: Query Request Timing Out > > > > It seems there's a bug with cells that have formulas. A possible workaround > for now is to copy/paste the range using "copy values only" to a new place > and work with the new range. It should then work. > > > > Thanks again for the report. > > On Thu, Oct 14, 2010 at 2:29 PM, Jinji <[email protected]> wrote: > > Yes, there's definitely a problem here. When I try to access the 'Stats' > sheet I get a server error message. We'll continue investigation. > > Thanks for the report! > > > > On Tue, Oct 12, 2010 at 8:30 PM, Traub, Adam <[email protected]> wrote: > > Jinji, > > > > I actually tried the range parameters as well to no avail. Interestingly > enough, I also tinkered with the sheet= parameter and changing it to > ANYTHING (even a random string of characters) returns the same, initial > sheet. Ok, so I’m thinking maybe I need to use the gid= parameter to > indicate that I want to use something other than the initial sheet, so I > follow these directions to get the gid: > > > > http://code.google.com/apis/visualization/documentation/spreadsheets.html > > > > Get this: the widget times out. Here’s the gadget data source URL: > > > > https://spreadsheets.google.com/tq?range=A1:J8&key=KEYHERE&gid=2 > > > > Does this mean there’s something in the data the API doesn’t like? I even > tried pasting that URL into a browser and I get a Google Doc’s error. > > > > To answer your second question, my current query is: > > > > query.setQuery('SELECT avg(I) GROUP BY dayofweek(A)'); > > > > This is actually a simplistic version off the base sheet (since I realized > that it is using the base sheet whether I tell it to or not…even with gid, > it seems). What I want to display a line chart for average headcounts that > has a different line for each day of the week, then have time slots > (9AM,11AM,1PM,etc – which are in the sheet) along the horizontal axis. If > there’s a good way to query the initial datasheet (the headcount one below) > to do that, I’m happy to hear suggestions on how that query should work. > > > > The sheet is here: > > > > > https://spreadsheets.google.com/ccc?key=0AsqVfMJblf1VdDNYbDRzSVRIZWZtMzdwWnQtaXBJOXc&hl=en > > > > > > Thanks so much Jinji, despite all the roadblocks I’m finding, I’m learning > a lot! > > > > -Adam > > > > > > Adam Traub | Systems Librarian | St. John Fisher College | > [email protected] | (585)385-8382 > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Jinji > *Sent:* Tuesday, October 12, 2010 10:07 AM > *To:* [email protected] > *Subject:* Re: [visualization-api] Re: Query Request Timing Out > > > > The second URL has a range parameter that contains only columns and not > cells (A instead of A1). Could it be that this is the problem? > > As for your question if the range parameter is necessary, I didn't > understand the question. Can you give an example query? > > > > On Mon, Oct 11, 2010 at 11:25 PM, Traub, Adam <[email protected]> wrote: > > Actually, it seems I lied; I’m still getting the timeout issue…here’s the > new URL I’m using: > > > > > https://spreadsheets.google.com/tq?key=KEYHERE&hl=en&sheet=stats&authkey=AUTHHERE&range=A1:J8 > > > > BUT, this one works: > > > > > https://spreadsheets.google.com/tq?key=KEYHERE&hl=en&sheet=headcounts&authkey=AUTHHERE&range=A:J > > > > So, yes…still getting one sheet working and another not. > > > > Any ideas? > > > > > > Adam Traub | Systems Librarian | St. John Fisher College | > [email protected] | (585)385-8382 > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Jinji > *Sent:* Friday, October 08, 2010 5:42 PM > *To:* [email protected] > *Subject:* Re: [visualization-api] Re: Query Request Timing Out > > > > In general, queries to Google spreadsheets to get a Google Visualization > DataTable should be to a URL that look like this: > > > > http://spreadsheets.google.com/tq?key=pCQbetd-CptGXxxQIG7VFIQ&range=B1:D11 > > > > (see the > playground<http://code.google.com/apis/ajax/playground/?type=visualization#data_source_request>for > an example) with a possible sheet parameter. > > So the URLs you initially posted don't qualify (they have "ccc" instead of > "tq"). The new URL seems OK at first look. Do you still get good result for > one sheet and timeout for the other sheet of the same spreadsheet, with the > second URL? > > On Tue, Oct 5, 2010 at 9:56 PM, Adam Traub <[email protected]> wrote: > > You may have seen my response that I assumed that the #N/A values were > causing my request to timeout, but apparently this isn't true. > > I've handled those cells with an IFERROR(), setting those to 0. > Unfortunately, the requests still timeout and I can't even get a > gadget to display the data, which had a slightly different URL than > those I posted earlier (https://spreadsheets0.google.com/tq? > range=A1:J8&key=KEYGOESHERE&gid=2). > > Any ideas? > > Thanks! > > > On Oct 5, 2:37 pm, Adam Traub <[email protected]> wrote: > > Hi All, > > > > I've having some issues querying the various sheets in my spreadsheet. > > > > Using a simple query and calling: > > > > var query = new google.visualization.Query( > > 'https://spreadsheets.google.com/ccc? > > key=KEYGOESHERE&hl=en&sheet=headcount&authkey=AUTHGOESHERE'); > > query.setQuery('SELECT C,D,E'); > > > > Works fine, but using the same query for the sheet in the same > > spreadsheet called "Stats:" > > > > var query = new google.visualization.Query( > > 'https://spreadsheets.google.com/ccc? > > key=KEYGOESHERE&hl=en&sheet=stats&authkey=AUTHGOESHERE'); > > query.setQuery('SELECT C,D,E'); > > > > Returns a "Request Timed Out" error. Any ideas? > > > > Thanks, > > Adam > > -- > 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]<google-visualization-api%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-visualization-api?hl=en. > > > > -- > > 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]<google-visualization-api%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-visualization-api?hl=en. > > -- > > 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]<google-visualization-api%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-visualization-api?hl=en. > > > > -- > 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]<google-visualization-api%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-visualization-api?hl=en. > > -- > 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]<google-visualization-api%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-visualization-api?hl=en. > > > > > > -- > 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]<google-visualization-api%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-visualization-api?hl=en. > > -- > 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]<google-visualization-api%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-visualization-api?hl=en. > -- 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.
