“query” part is ignored

  • Resolved timeboxed

    (@timeboxed)


    8 years ago

    Heyho,

    Firstoff,I love the plugin – looks way better than the iframe I used before.
    Now I have an issue which really bugs me – I have a sheet (of course) which I embedded.

    I only want the first two columns of the sheet being embedded, and that they should be sorted by a fourth one which is hidden.

    [gdoc key=”1YufvZqgD8aLhbJQM6H5d3BezwzWHWgm-NpE9udHMTVI” query=”select A,B”]

    For reference, the document looks like this:
    https://docs.google.com/spreadsheets/d/1YufvZqgD8aLhbJQM6H5d3BezwzWHWgm-NpE9udHMTVI/pubhtml

    (Note that the tab I originally experimented with is now a clone, called “Backlog_Embedded_Testing” I created for this post.

    I currently have it working by changing the structure of the doc and use class=”no-datatables” but that’s not a perfect solution of course.

    Any hint is highly appreciated!

    On another note: Can someone give me a hint on how to achieve my “perfect” setup? I mocked a screenshot into the sheet (tab “screenshot”) – the rough description:
    – It is sorted by column D
    – It only shows column A
    – If someone clicks on the green plus, Column B is shown
    – The header (Search, DOWNLOAD AS, etc) is not there

    Any hints on where to read up would be awesome as well!

    Thanks a lot,
    Georg

Viewing 6 replies - 1 through 6 (of 6 total)
  • Dustin Tantum

    (@tantumonium)

    8 years ago

    Can someone give me a hint on how to achieve my “perfect” setup?

    This shortcode will display columns A & B sorted by D (a.k.a. 4) in descending order.

    [gdoc key="https://docs.google.com/spreadsheets/d/1YufvZqgD8aLhbJQM6H5d3BezwzWHWgm-NpE9udHMTVI/edit#gid=109303573" query="select A,B" use_cache="no" http_opts='{}' datatables_order='%5B%5B 4, "desc" %5D%5D']

    As for making it only display column A by default, with the data from column B showing after clicking the green “+” icon, I imagine that could be done by modifying the DataTables Responsive Plug-in parameters ( https://datatables.net/extensions/responsive/ ), but I don’t yet know how to do that.

    Thread Starter timeboxed

    (@timeboxed)

    8 years ago

    Thank you for the reply!
    Sadly, simply copy/pasting it leads to an endless page load. I figured out that it is because I can only sort on columns which are queried as well.

    This means that I have altered it to:
    [gdoc key=”https://docs.google.com/spreadsheets/d/1YufvZqgD8aLhbJQM6H5d3BezwzWHWgm-NpE9udHMTVI/edit#gid=109303573″ query=”select A,B,D” use_cache=”no” http_opts='{}’ datatables_order=’%5B%5B 2, “asc” %5D%5D’]

    This now gives the correct order and shows the columns – but I can’t hide the “D” column.
    Even adding in CSS a .col-3 { display: none;} does not change it. I suspect this is because, again, the table is too wide and the column I want removed is hidden in the green (+).

    I think I can work around this by changing the table structure but I’m not yet 100% sure. Should you have more ideas, I’m very open to them 🙂

    Thanks a lot already, you helped me a great lot forward in understanding how this plugin works.

    Cheers,
    Georg

    Plugin Author Meitar

    (@meitar)

    8 years ago

    There are two ways to sort the data in your Sheet.

    • One way is by asking Google to sort things for you and then give you pre-sorted results. You would use Google’s order by semantics in the query attribute for this.
    • Another way is to get the results from Google and then sort them yourself. You would do this in DataTables by using the datatables_order option.

    If you want to have Google do the sorting for you, you can write a query like this:

    select A, B order by D

    If you want to have DataTables do the sorting for you, you will need to select all the relevant columns (including the one to sort by) in your query. Then, to hide a column, you can make use of DataTables’ columns option and set the visibility of the column you want to sort by to false (to hide it). See columns.visible in the DataTables documentation for details on that option.

    You can also use CSS if you want, but if you’re already using DataTables anyway, you might as well tell DataTables you don’t need to display a given column, as this keeps your code a bit more organized. 🙂

    • This reply was modified 8 years ago by Meitar.
    Plugin Author Meitar

    (@meitar)

    7 years, 12 months ago

    Oh! I also just realized that you’re using the old-style Google sheet syntax. Queries are only supported on new-style Google sheets (the default for any sheet created in the year 2016). In other words: use the entire URL to your Sheet in the key attribute, not just the Sheet’s document ID. See this plugin’s Other Notes page for more details.

    Thread Starter timeboxed

    (@timeboxed)

    7 years, 12 months ago

    Hey Meitar,

    Thanks a lot of for the in-depth answers! Sadly, I just can’t figure it out.
    It’s OK though, I worked around be reworking the sheet, reducing it to two columns and sorting it manually.

    Just for future people who find this via google. This one led to a deadlock, endlesss loading-animation, both with and without giving a specific query (A to D):
    [gdoc use_cache=”no” key=”https://docs.google.com/spreadsheets/d/1YufvZqgD8aLhbJQM6H5d3BezwzWHWgm-NpE9udHMTVI/edit#gid=109303573″ http_opts='{}’ datatables_order=’%5B%5B 1, “asc” %5D%5D’]

    This one just ignored the order-by and sorted by the (only visible) column A:
    [gdoc use_cache=”no” key=”https://docs.google.com/spreadsheets/d/1YufvZqgD8aLhbJQM6H5d3BezwzWHWgm-NpE9udHMTVI/edit#gid=109303573″ query=”SELECT A,B order BY D”]

    And so I sorted the table manually and did this:
    [gdoc class=”no-datatables” query=”select A, B” key=”https://docs.google.com/spreadsheets/d/1YufvZqgD8aLhbJQM6H5d3BezwzWHWgm-NpE9udHMTVI/edit#gid=109303573″%5D

    This is not as beautiful as this plugin can be but it works for now and I expect that the sheet and its use will change over time anyway.

    I thank you a lot for the input, I already learned quite a bit here! In a few weeks I’ll revisit your post and spend a few more days experimenting. Should I find something new then I will update the thread!

    Thanks,
    Georg

    Plugin Author Meitar

    (@meitar)

    7 years, 12 months ago

    No worries, Georg.

    For what it’s worth, I think the thing you’re missing is that, by default, DataTables’s own sorting will override whatever sorting you’ve asked from Google, since Google’s happens first and DataTables’s happens second. What this means in practice is that this:

    [gdoc key="..." query="select A,B order by D"]

    will be sorted by DataTables despite the results being fed to DataTables in an already-sorted fashion by Google. You need to explicitly turn off sorting/ordering in DataTables or turn off DataTables entirely for this Google-supplied order by clause to be visible at the final output after all the automated processing.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘“query” part is ignored’ is closed to new replies.

Tags