5 ms·

Google Sheets Query Function (2016)

75 points by pentestercrab 8y ago

airstrike 8y ago
Why MS hasn't implemented a similar UDF in Excel is truly beyond me. No, Power BI isn't the same thing.
- m0meni 8y ago
  https://www.querystorm.com https://www.querystorm.com
  - lostsock 8y ago
    That looks really great, thanks for sharing!
    - krembo 8y ago
      While it's a cool project it cannot be compared to Sheets Query as long as Excel doesn't implement array expanding formulas (out-puting an array from a single cell formula). PowerQuery is a very powerful tool for ETL is also not comparable as it's not natively integrated into Excel and still acts as an external component. [ex-Excel FTE here)
      - chrispsn 8y ago
        You're going to love this: https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of-Dynamic-Arrays-in-Excel/ba-p/252944 https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of... But you'll probably also love Mesh Spreadsheets: http://chrispsn.com/mesh-preview.html http://chrispsn.com/mesh-preview.html http://mesh-spreadsheet.com/ http://mesh-spreadsheet.com/
        krembo 8y ago
        Nice! I saw the initial versions of it, it took years to finish dev.
        airstrike 8y ago
        Fuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuck! I am in awe. Thank God. I can barely contain my enthusiasm. I literally wrote a "SUPERLOOKUP" formula in VBA that would apply SORT and UNIQUE but making the range dynamic would require an XLL which is a beast I never had time to tame as a hobbyist... this is a dream come true. My eyes are literally watery. Wow.
- josephjrobison 8y ago
  Agreed, I would say query is the strongest reason to use Sheets over Excel
gpapilion 8y ago
I use query a lot in sheets. The behavior gets wonky when other users apply filters and sorts.
mmacvicarprett 8y ago
The mods are asleep? how this ended up in hacker news front page?
- minimaxir 8y ago
  Out of curiosity, why don’t you think this is a good fit for the Fromt Page?
  - deleted 8y ago
    [deleted]
anikdas 8y ago
That's a great tool. The other day I had a CSV of ML predicted data which I had to analyse. Created a google sheet with the CSV and wrote Query function to generate reports.
- josephjrobison 8y ago
  Possible to share or point to where to learn about that? Have been having a lot of fun and success with query and always looking to push it further!
remify 8y ago
I love Google Sheets. The API is perfect for rapid prototypes and shits

75 points by pentestercrab 8y ago

airstrike 8y ago

Why MS hasn't implemented a similar UDF in Excel is truly beyond me. No, Power BI isn't the same thing.

m0meni 8y ago
https://www.querystorm.com https://www.querystorm.com
- lostsock 8y ago
  That looks really great, thanks for sharing!
  - krembo 8y ago
    While it's a cool project it cannot be compared to Sheets Query as long as Excel doesn't implement array expanding formulas (out-puting an array from a single cell formula). PowerQuery is a very powerful tool for ETL is also not comparable as it's not natively integrated into Excel and still acts as an external component. [ex-Excel FTE here)
    - chrispsn 8y ago
      You're going to love this: https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of-Dynamic-Arrays-in-Excel/ba-p/252944 https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of... But you'll probably also love Mesh Spreadsheets: http://chrispsn.com/mesh-preview.html http://chrispsn.com/mesh-preview.html http://mesh-spreadsheet.com/ http://mesh-spreadsheet.com/
      - krembo 8y ago
        Nice! I saw the initial versions of it, it took years to finish dev.
      - airstrike 8y ago
        Fuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuck! I am in awe. Thank God. I can barely contain my enthusiasm. I literally wrote a "SUPERLOOKUP" formula in VBA that would apply SORT and UNIQUE but making the range dynamic would require an XLL which is a beast I never had time to tame as a hobbyist... this is a dream come true. My eyes are literally watery. Wow.
josephjrobison 8y ago
Agreed, I would say query is the strongest reason to use Sheets over Excel

gpapilion 8y ago

I use query a lot in sheets. The behavior gets wonky when other users apply filters and sorts.

mmacvicarprett 8y ago

The mods are asleep? how this ended up in hacker news front page?

minimaxir 8y ago
Out of curiosity, why don’t you think this is a good fit for the Fromt Page?
- deleted 8y ago
  [deleted]

anikdas 8y ago

That's a great tool. The other day I had a CSV of ML predicted data which I had to analyse. Created a google sheet with the CSV and wrote Query function to generate reports.

josephjrobison 8y ago
Possible to share or point to where to learn about that? Have been having a lot of fun and success with query and always looking to push it further!

remify 8y ago

I love Google Sheets. The API is perfect for rapid prototypes and shits