Video není dostupné.
Omlouváme se.

QUERY Function in Google Sheets - 2024 Tutorial ✏️

Sdílet
Vložit
  • čas přidán 14. 08. 2024
  • Do you want to master the Google Sheets QUERY function? Then you’ve found the right place. We explain the QUERY function for you. In this extensive tutorial, we cover all available clauses of the QUERY function with plenty of practical examples. You’ll learn:
    ✅ How to use the QUERY function in Google Sheets.
    ✅ How to filter, sort, group, pivot your data, and more.
    ✅ How to write advanced queries in Google Sheets.
    Grab the sample dataset and play along! docs.google.co...
    Jump to the section that interests you the most:
    00:00 Intro
    00:38 QUERY function syntax
    01:43 Simple QUERY example
    02:41 SELECT A, B vs SELECT Col1, Col2
    03:10 WHERE clause
    03:33 Multiple WHERE conditions
    04:21 ORDER BY clause
    04:47 GROUP BY clause and aggregation
    06:51 Combine data from multiple sheets
    08:46 QUERY with IMPORTRANGE
    09:55 PIVOT clause
    10:59 LIMIT and OFFSET clauses
    11:55 LABEL and FORMAT clauses and scalar functions
    14:08 Sum up
    At Coupler.io Academy, we help you make sense of your data. We explain how to use spreadsheets, data warehouses, and BI tools. We share our tips on integrating data between apps and automating data transfers. Be sure to subscribe to our channel so you never miss a thing.
    Learn more about Coupler.io and start a free trial at 🔗 app.coupler.io.... No credit card is required.
    What is Coupler.io?
    Coupler.io is a data automation and analytics platform that integrates data transfers from the business apps you use with over 200 available integrations. Over 800,000 users worldwide use Coupler.io to schedule automated data transfers, transform data, and bring it to various available destinations, including Google Sheets, Excel, BigQuery, Looker Studio, Power BI, and more. All available in a simple no-code interface that requires only a 5-minute setup.
    See the complete list of the available data sources: bit.ly/3OP7in2
    For more information, visit:
    ✅ Our website: www.coupler.io/
    ✅ Help Center: help.coupler.io/
    ✅ Email us: contact@coupler.io
    📝 For more information on mastering Google Sheets, visit Coupler.io blog blog.coupler.io/
    🔗 Google Sheets Query Function: Everything you Need to Know in One Article blog.coupler.i...
    🔗 QUERY + IMPORTRANGE in Google Sheets: Real-Life Formula Examples blog.coupler.i...
    Keep in touch and join us on social media:
    CZcams: / @coupleracademy
    TikTok: / coupler.io
    Twitter: / coupler__io
    Facebook: / coupler.io
    LinkedIn: / coupler-io
    #queryfunctiontutorial
    #queryfunctiongooglesheets
    #googlesheetsqueryfunction
    #googlesheetsqueryformula
    #queryfunctioningooglesheets
    #howtousequeryfunctioningooglesheets
    #googlesheetsquery
    #queryfunctionexplained
    #queryfunction
    #googlesheets
    #advancedqueryingooglesheets
    #queryfunctiongooglesheetssum
    #googlesheetsquerywherecontains
    #googlesheetsquerydoesnotcontain
    #googlesheetsquerywherecellisblank
    #googlesheetsquerymultiplesheets

Komentáře • 33

  • @user-jy4yc3hb8f
    @user-jy4yc3hb8f Před 4 měsíci +2

    Perfect quick and direct to the point presentation

  • @lafamillecarrington
    @lafamillecarrington Před 6 měsíci +1

    Really nice rapid summary of how to use Query

    • @coupleracademy
      @coupleracademy  Před 6 měsíci

      our pleasure, check out other vudeo tutorials on our channel, we cover different Google Sheets functions and mucn more!

  • @nedoctopus
    @nedoctopus Před 5 měsíci

    Penjelasannya sangat mudah dimengerti, ringkas dan cepat. Channel rekomendasi untuk belajar lebih lanjut mengenai Google Sheets. Sangat mudah bagi saya untuk mengaplikasikannya, terima kasih banyak 😍👌👍

  • @madisonandhouston
    @madisonandhouston Před 4 měsíci

    THANK YOU SOOO MUCH! a paid Coursera course couldn't have explained it better!

    • @coupleracademy
      @coupleracademy  Před 4 měsíci

      Thank you for such a lovely comment, we're so happy you enjoyed our content. Come back for more :)

  • @tridibbiswas3361
    @tridibbiswas3361 Před 25 dny

    Thank you. This is the prefect video I was lookiing for when I am trying to migrte from excel and the query fuction is so much more versatile. Could also do video on IMPORTHTML

    • @coupleracademy
      @coupleracademy  Před 23 dny

      Hi! Thank you so much and glad you enjoyed our video. We don't have anything on IMPORTHTML planned at the moment but we'll definitely note your request and will discuss it with the team. Thanks again!

  •  Před 9 měsíci +1

    Thank you

    • @coupleracademy
      @coupleracademy  Před 9 měsíci +1

      you're welcome, check back for more interesting content soon :)

  • @indradutta4136
    @indradutta4136 Před 4 měsíci

    to import from a different sheet if I need to select col A:D & column H how will go about it..apart from writing the column name like col1,col2 and so on

    • @coupleracademy
      @coupleracademy  Před 4 měsíci

      Hi! The simplest way is to probably set as a range an array with all columns you want to fetch and then SELECT *, for example
      =QUERY({Sheet1!A1:D, Sheet1!H1:H}, "SELECT *")

  • @mooripo
    @mooripo Před 24 dny

    thanks

  • @luanbaviloni6714
    @luanbaviloni6714 Před 29 dny

    For anyone getting error when trying to use QUERY functions, replace the comma separator for the semicolon separator.

    • @coupleracademy
      @coupleracademy  Před 27 dny

      Thanks for the helpful tip! Regional settings can cause issues with separators. Switching from commas to semicolons is a great solution for those experiencing errors in QUERY syntax.

  • @TechWookie
    @TechWookie Před 28 dny

    How do you put in spacer columns or a static NULL column?

    • @coupleracademy
      @coupleracademy  Před 27 dny

      Hi! You can use ' ' (single quotes) for empty columns or NULL for null columns. For example:
      =QUERY(A:D, "SELECT A, '', B, NULL, C")

  • @sqgrowthconnect
    @sqgrowthconnect Před 6 měsíci

    This is beautifully done but doesn't work for me and I can't figure out why... when I try to add the curly braces inside the query function, my version of google sheets auto add "ArrayFormula(" syntax to the beginning of the function and therefore ignores the semi-colons that combines data from multiple sheets. Any ideas on how to resolve?

    • @coupleracademy
      @coupleracademy  Před 6 měsíci

      I haven't encountered such an issue with auto-adding an arrayformula function. However, you could try to specify the ranges first and then add curly braces to the formula. I hope this trick will help you resolve the issue.

  • @foodmastiIndia
    @foodmastiIndia Před 2 měsíci

    great tutorial. Can this be used in App script to fetch data and show the reports ?

    • @coupleracademy
      @coupleracademy  Před 2 měsíci +1

      Thanks for the great feedback! 😊 Yes, you can use the QUERY function in Google Apps Script to fetch data and generate reports. You'll use the SpreadsheetApp service to access your data and then apply the QUERY function to manipulate it. It's super handy for automating tasks and creating dynamic reports. If you need any help getting started, let us know!

  • @RTRT-jr8jv
    @RTRT-jr8jv Před 4 měsíci

    Hi, how can I get C + 30 days using sheets query and C is text(not date) with YYYY-MM-DD,HH:MM:SS format?

    • @coupleracademy
      @coupleracademy  Před 2 měsíci

      Hey there! Great question! 😊 You can add 30 days to a date in text format using a combination of QUERY and DATE functions. Here's how you can do it:
      1. Convert the text to a date:
      =DATEVALUE(LEFT(C1, 10)) + 30
      This converts the text date in C1 to a date and adds 30 days.
      2. Use this in a QUERY:
      =QUERY(A:D, "SELECT A, B, C, DATEVALUE(LEFT(C, 10)) + 30 WHERE ...", 1)
      Replace A:D with your range and adjust the SELECT statement as needed. Let me know if you need more help!

    • @RTRT-jr8jv
      @RTRT-jr8jv Před 2 měsíci

      @@coupleracademy Thank you for trying to help but DATEVALUE(LEFT(C, 10)) function is not allowed in QUERY

  • @posmophthamour383
    @posmophthamour383 Před 7 měsíci

    Can we do the column concatenation in query and how

    • @coupleracademy
      @coupleracademy  Před 7 měsíci

      Hi! Unfortunately QUERY doesn't support concatenation. You may need to use a workaround, maybe this one helps? stackoverflow.com/questions/42571114/how-to-use-concat-in-query

  • @mooripo
    @mooripo Před 24 dny

    This is so correct until I use Label BEFORE the end of the query like so :
    (
    =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount', Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header))
    )
    Although, when I use Label at the END it works correctly like in this function :
    (
    =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount'")
    )
    Finishing both calculated column and leaving the labeling to the end doesn't work :/
    (
    =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount' )")
    )
    *WORKING Here*
    (
    =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)&" LABEL"&" Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&"'Disciount Amount', Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&"'Paid sum'")
    )

    • @coupleracademy
      @coupleracademy  Před 23 dny

      Hi! It looks like there might be a syntax issue when using multiple LABEL statements with calculated columns. Ensure you have proper spacing and syntax. Here’s a corrected example:
      =QUERY(Invoices_Extracted_on_2024.07.21!A1:L, "SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount', Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&" 'Paid sum'")
      The key is to ensure each LABEL is correctly associated with its column, and there’s proper spacing and punctuation.