App does not read full range of an excel/google sheet

Hi, i am sharing an excel sheet in my drive and am trying to read in into my app. Unfortunately, it looks like it cant read the full mentionned range but only a very small part of it.
When i switch this excel sheet for a more common google sheet (another document), I have absolutely no problem at all reading the full mentionned range.

I have tried with another excel sheet and faced the same problem.
Question is: is it a known issue with excel sheets (because chrome/brave/vivaldi/Firefox dont handle all excel macros/functions) or is it something else i am missing?
Many thanks for the incoming help

Did you try exporting the excel data to a csv, then downloading that to your app?

You do not show how you are currently trying to fetch the data...

no I did not cause the excel sheet is quite complicated: i have tried to export to a csv to then fetch and sort datas, but it would be a long and hard work.
I import the data this way

I noticed that depending on the range i set, the import goes more or less smoothly.
I think the easiest way will be to create a macro in my excel sheet that will process the data on my sheet to make it easier to use.

You should use the web component to return tqx=out:csv

Hi, i have given a try with csv query. But for a reason I cant explain, i dont manage to perform any smart query.


If ever it eventually works, my next step would be to add automatic query buttons like Tracteur that would send a query with a simple click on it. Dont know if its possible.

There is no column called Tracteur. Use column letters in your query?

It is strange to have all your data headers the same?

What is the query?

What do you mean there is no column called Tracteur: the first five are called Tracteur.
Should I query this way: select B, C, D, E, F ? How could i make this query easier?

Yes, read my guide

yes, sorry, I had read it, but obviously read too fast the way you have to query.
Is it possible to go deeper and had a button that would automatically do a query like SELECT B,C,D,E, F where E contains ... so user would only have to write one word?

Another question: is it possible to query the headers? I mean, I have three different headers, and I'd like to make a query so that only the requested columns appear (like the columns with the Tractor header). I've created this csv so that I can query in this way.

By entering one word, I presume you mean Tracteur ?

You cannot query by rows.

Can you share your sheet data, I will see what I can come up with for you.

https://docs.google.com/spreadsheets/d/<>SHEETID/export?format=csv&gid=<GID

Yes, I admit I would be happy to query by rows or by headers.
Thanks a lot for the time you re spending on my noob requests...
And by one word, it would either be Tracteur, or any other like LCM, DPS, etc..

sheet ID: 1k7YnKNdYLYKGIQ_FDVHUSM8GRgdeC68w

gid=1657292678

There are several ways you could do this. I will suggest below what is probably the easiest to setup:

  1. Create a new worksheet in your spreadsheet
  2. Put a transpose formula on cell A1, in Google Sheets this would be something like:
    =TRANSPOSE(('originalsheet'!(1:1000) - the whole sheet
  3. Query like this:
    https://docs.google.com/spreadsheets/d/193Kssa9UZ3WJT1X0TmlvEF9rnQIo40cTpa3gPgz8JX8/gviz/tq?tqx=out:csv&gid=1865627609&tq=SELECT *WHERE A contains 'Tracteur'
  4. In the app, you would need to run a procedure to transpose the data back to the original layout:

Amazing! Many thanks for this juge job, i am now gonna try this solution and see what I can do with it. Thanks again for the great job and help you provided!

Unfortunately cant manage to have this working. I get every time i run query the same answer: Cannot parse text argument to "list from csv table" as a CSV-formatted table

Button 2 works and shows the all sheet page if i dont put anything in tbquery.

You do not show your query. The value, e.g. Tracteur needs to be in single quotes 'Tracteur'

tbQuery =
SELECT * WHERE A contains 'Tracteur'

Well, in your example, the single quotes are already in the blocks this way
image
so I reproduced it.
I thought it would mean i dont need to add any more quote to my query. So i was quyering Tracteur.
Anyway, I have tried both ways, with or without single quote and I am facing the same error message.
gVizQueryD.aia (4.5 KB)

I join my aia if it can help

If you look at your spreadsheet, you will see you need to query Column B...

1 Like

gVizQueryD (1).aia (3.7 KB)

Once again, many thanks for fixing these noob mistakes..
Im trying to figure out how i could mix queries like this
image
image
image

But I cant reach my goal. Is there anywhere i can find sintax help about these queries? I found this but it did not help me much; Query Language Reference (Version 0.7)  |  Charts  |  Google for Developers

The only thing i can figure would be to call two different webviewers with two different queries. But I am pretty sure there is an easiest way to do so...