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; 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.htm l 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=0AsqVfMJblf1VdDNYbDRzSVRIZWZtMzd wWnQtaXBJOXc&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&au thkey=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:D 11 (see the playground <http://code.google.com/apis/ajax/playground/?type=visualization#data_so urce_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] <mailto: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] <mailto: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] <mailto: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] <mailto: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] <mailto: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. -- 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.
