Categories
Interior

How to use the query function in google sheets

How to use the query function in google sheets

How to use the query function in google sheets

While Google Sheets is primarily made to be used with numbers, words are an important part of any spreadsheet. You need words to calculate, verify, and keep track of what each data point represents. Sometimes, you have word count limits per cell. Labels, months, days, products—they all need specific words for the list too. Formulas play a big role in spreadsheet functionality, and word counts in Google Sheets require it. Just like using IF/THEN statements for datasets in Google Sheets, word counts use COUNTA, SPLIT, SUM, ARRAYFORMULA, and more. So, what do the statements provide for Google sheets? Here’s more information.

Word Counts in Google Docs versus Google Sheets

Google Docs allows you to easily check the list of words in any given document, giving you a count for both the entire document and the content you have highlighted using your mouse. It is really easy to figure out how long a Google Doc document is in terms of word length, but unfortunately (at first glance), that traditional word count option is not present in Google Sheets. If you are looking for a way to figure out how many words are in your Google Sheets spreadsheet versus Google Docs, there is an easy workaround that anyone can use. Let us take a look.

Word Count Options for Google Sheets

Google Sheets spreadsheets have the ability to count words within the document, but it is not a clickable action as it is in Docs. While there is no official word count tool, Sheets does display a text count within specific columns, rows, and cells when using formulas.

Counting Text per Cell in Google Sheets

Counting words per cell in Google Sheets has 2 different formulas based on specific needs. The first formula shown below calculates total word count per cell when there are no blank cells within the range specified, such as from A2 to A8. If you have any empty cells between your specified range, see the second formula below.

Option #1: Word Count per Cell with No Empty Cells in Specified Range

To preview word counts per cell when there are no empty cells in between, use the following instructions.

  1. Highlight the blank cell where you want to display your results and paste the following formula: =COUNTA(SPLIT(A3, ” “)) where “A3” specifies the cell.
    How to use the query function in google sheets
  2. Click in your display cell or hit Enter to apply the formula.

As you can see above, cell A3 has two words. Here is the breakdown of the formula used, which was “=COUNTA(SPLIT(A3, ” “)).”

  • COUNTA auto-counts the words in the cell.
  • SPLIT counts everything separated by a space as an individual point of data (your content can be counted as a word, even if it’s just a number).
  • A2 translates to Column, Row Number where “A” is the column and “2” is the row number, which totals the word count in the specified cell.

Option #2: Word Count per Cell with Empty Cells in Specified Range

To preview word counts per cell with some cells being empty within your specified range, use the following instructions.

  1. Highlight the blank cell where you want to display your results and paste the following formula: =IF(A2=””,””,COUNTA(SPLIT(A2,” “))) where “A2” specifies the cell to be counted.
    How to use the query function in google sheets
  2. Click in your display cell or hit Enter to apply the formula.
    How to use the query function in google sheets

In formula 2 above, using the “IF” command determines if there are empty cells, and if so, it does not count the cells as 1 word. Formula 1 above counts each empty cell as 1 word.
How to use the query function in google sheets

Counting Text per Column in Sheets

You could use the cell method to count each specific cell to receive a total word count, but as you can imagine for larger documents, this will take longer than you might want.

In order to properly provide the word count for your document using a much faster method, you can count your text in Sheet by using each column, instead of each cell.

The formula used for Google Sheets word count in columns also has two options, but the second one covers both calculations. Rather than waste time adding two different formulas (one for columns without blank cells and one for columns with them) you can use the following formula below.

=ARRAYFORMULA(SUM(COUNTA(SPLIT(A2:A11,” “)))-COUNTBLANK(A2:A11))

To calculate total Google Sheets word count by column, do the following:

  1. Copy the formula shown above: =ARRAYFORMULA(SUM(COUNTA(SPLIT(A2:A11,” “)))-COUNTBLANK(A2:A11)). Start with the equals sign and ignore the period in the end when copying.
    How to use the query function in google sheets
  2. Select the cell where you want the column word count to display.
    How to use the query function in google sheets
  3. Right-click the text box at the top that shows the cell’s contents, and then choose “Paste as plain text.” This ensures that the correct font and characters get pasted.
    How to use the query function in google sheets
  4. Edit the formula in the text box to reflect the correct cell range, then press enter to save it. Don’t click on another cell to save it or it may change your cell range .
    How to use the query function in google sheets

Yes, it is a more complex formula, but the method of using it is just as simple as you might expect from Google Sheets. The formula for column counts ignores blank cells (without counting them as 1) and counts the words in each column cell to deliver a total word count for the column range specified.

While it is unfortunate that your content can’t be automatically counted, as it can within Google Docs, it is not too hard to use the formula tool within Google Sheets to quickly and easily add content to your document and add up a certain word count. With just the quick application of a formula, you can access the data you want, whenever you want it.

How to use the query function in google sheets

Google Sheets is a web-based application that enables users to create, update and modify spreadsheets and share the data online in real time.

Google’s product offers typical spreadsheet features, such as the ability to add, delete and sort rows and columns. But unlike other spreadsheet programs, Google Sheets also enables multiple geographically dispersed users to collaborate on a spreadsheet at the same time and chat through a built-in instant messaging program. Users can upload spreadsheets directly from their computers or mobile devices. The application saves every change automatically, and users can see other users’ changes as they are being made.

Google Sheets is included as part of the Google Docs Editors suite of free web applications. This suite also includes Google Docs, Google Slides, Google Drawings, Google Forms, Google Sites and Google Keep.

Google Sheets is typically used for spreadsheet collaboration across different geographic locations. Multiple users can modify a Google Sheets document in real time, with changes tracked for each individual user.

How to use the query function in google sheetsFeatures of Google Sheets

The Google Sheets online spreadsheet application enables users to create, edit and format spreadsheets online to organize and analyze information. Google Sheets is often compared to Microsoft Excel, as both applications are used for similar purposes. Google Sheets is essentially Google’s cloud-based version of Microsoft Excel’s basic features.

Users can create and edit spreadsheets via the Google Sheets online web application, as well as through mobile devices running on iOS or Android. To use Google Sheets, a valid email address is required. Google Sheets enables users to do the following:

  • Edit and format spreadsheets. Spreadsheet items can be added, edited, formatted and applied to formulas and functions through Google Sheets.
  • Analyze. Google Sheets can visualize spreadsheet data in charts, graphs and tables.
  • Share. Users can share Google Sheets documents and folders with others for real-time collaboration.
  • Print and download. Users can open and import Google Sheets files into other document formats, including Microsoft Excel, Word, PowerPoint, Adobe PDF and PNG (Portable Network Graphics).

Google Sheets includes the following core features:

  • Spreadsheet editing and formatting. This includes operations and functions for data entry; data summary; text translation; data import; data validation; data protection; cleaning to return text with unprintable characters removed and trimming to remove spaces that may be leading, trailing or repeated in text; filtering data by conditions such as date, alphabetical or numerical order; custom conditions and heatmaps, which use colors to represent the density of data points in the table; and basic and advanced formulas.
  • Data visualization. Users can generate spreadsheet data for graphs, charts and other types of diagrams and embed them in websites.
  • Machine learning-based features. The Explore feature uses machine learning to build charts, create pivot tables and answer questions about the data. It can auto-update based on selected data.
  • Offline editing. Even when not connected to the internet, Sheets can edit offline, and changes will update once an internet connection is restored.
  • Compatibility. Sheets documents are compatible with a variety of formats, including Excel (XLS), Apache OpenOffice, PDF, Text, HTML and comma-separated values (CSV).
  • Google product integration. Google Sheets can be integrated with other Google services, such as Drawing, Finance, Form and Translate. It is also compatible with Microsoft files and shares many of the same keyboard shortcuts.
  • Collaboration features. Emails can be sent when other collaborators make comments or changes to the shared spreadsheet, and users can view the version history.
  • Security. Users can manage permissions for editing, downloading, copying or printing for specific collaborators through individual-, group- or domain-level access.

Google Sheets is often compared to Microsoft Office Excel, as they are both spreadsheet applications with many of the same core features. Here are a few ways in which Google Sheets and Excel differ:

  • Price. Google Sheets for personal use is free, while Excel requires an Office 365 subscription. Google Sheets for business use with extra features requires a Google Workspace subscription.
  • Cloud vs. non-cloud. Google Sheets is a cloud-based web application, while Microsoft 365 is accessed through the internet using a web browser.
  • Collaboration. Typically, Google Sheets is preferred for collaboration, as it is fully web-based. While Excel has an online version, it does not have full functionality.
  • Data processing. The speed and performance of Google Sheets can be limited by an internet connection, or if the maximum storage limit is neared. Excel can store 17 million cells, as opposed to the limit on Google Sheets, which is 5 million cells.
  • Features. While Google Sheets has the core features of a spreadsheet application, its offerings are rather basic. Excel has a larger range of features and functions for specialized purposes, as well as more customization options and built-in formulas.
  • Integration. Excel integrates with other Microsoft applications, such as Power BI. While Google Sheets integrates with Google web applications such as Google Drive, they are less ubiquitous in enterprise settings.
  • Support. Both Sheets and Excel have extensive support communities. Google offers help articles and an interactive community forum for Sheets. Microsoft offers a community help forum and an Excel-specific learning hub.

How to use the query function in google sheetsAt a glance: Google Sheets vs. Microsoft Excel

Google Sheets is a cloud-based web application that is often compared to Microsoft Excel. While it can make collaboration easy and provides many of the core spreadsheet functions of Excel, it is less ideal when specialized functions are needed.

The spreadsheet view displays scene information about elements and their parameters in a grid. This information is filtered and organized by queries that you create to show specific aspects of your scene in combination with sorting operations you can perform based on object data. You can then perform operations on many elements or parameters at once.

How to use the query function in google sheets

Each row represents a scene element. Click a row heading to select all of an element’s properties. Right-click the row to select objects in your scene.

Each column represents a parameter. Click a column heading to select a parameter on all of the displayed objects. Right-click a heading to quickly sort elements and mark parameters for animation.

The intersection between a row and a column is called a cell, each of which holds one value. You can select many cells at once and modify them all simultaneously.

Using Queries

A query is a means of requesting information filtered a specific way for display in the spreadsheet. For example, you could query Softimage for a list of local rotation values for only selected objects. Queries are text files with a .query extension; default queries are located in %SI_HOME%\Application\Queries.

You can execute a query by using one of the predefined queries found in the spreadsheet’s Query menu, or you can choose Query Open to load a query file you have created.

Once you have executed a query and the spreadsheet displays the data you have requested, you can further organize the information by sorting the table. Right-click any column heading to sort the table entries based on the column’s entries. To return to the default sorting as defined in the .query file, choose Query Sort from the spreadsheet menu.

How the Spreadsheet Works

The Softimage spreadsheet builds the content of the spreadsheet using scripting, which gives you a lot of flexibility with the type of information you can display.

Types of Queries

There are two types of queries:

The single level query filters certain types of elements from the scene, and display a information about them (for example the Geometry query).

The relational query shows the relationship among different scene elements, such as showing all of the clusters that exist on all of the objects in a scene, or showing the textures and their object owner.

Types of Cells

Each cell in the spreadsheet can show two main types of information:

Softimage parameters in what are called GetValue/SetValue cells. These cells display the value of a parameter that already exists within Softimage (whether native or custom parameters), and that are accessible to standard GetValue and SetValue calls in scripts. These cells are automatically handled by the spreadsheet as long as you identify which parameter to display.

Custom information in scripted cells. The information displayed in these cells is computed by a custom script, which allows you to compute certain custom measurements and display them. These cells are read-only.

The Components of a Spreadsheet Query

A single level query is described using three elements:

A script function that tells the spreadsheet which scene elements to show on each row.

A script function that defines the columns that show the information for each row.

A query file that tells the spreadsheet which script to call for rows and columns (and where to find these scripts). This is the file that gets opened when you choose File Open or when you run preset queries within Softimage.

Separate row and column scripts are very useful as they allow you to reuse one or the other in different queries. For example, there’s a row script called Current_Selection that is used in a few of the preset scripts and that you can use for your own queries if you need to display all of the selected elements. All spreadsheet query scripts must return elements in the form of an array.

A relational query will have the same elements as the single level query, but with the addition of two scripts.

A relation association script, which determines, for each row, the associated items. This script defines the second-level of rows (for example, a list of clusters for each of the selected elements). There can be only two levels in Softimage, that is, only one relation per query.

A column definition script, much like for single-level scripts, which defines what columns to display for the elements that are displayed at the second-level of the query.

Except where otherwise noted, this work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License

Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he’s been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing. Read more.

How to use the query function in google sheets

If you need to manipulate data in Google Sheets, the QUERY function can help! It brings powerful, database-style searching to your spreadsheet, so you can look up and filter your data in any format you like. We’ll walk you through how to use it.

Using the QUERY Function

The QUERY function isn’t too difficult to master if you’ve ever interacted with a database using SQL. The format of a typical QUERY function is similar to SQL and brings the power of database searches to Google Sheets.

The format of a formula that uses the QUERY function is =QUERY(data, query, headers) . You replace “data” with your cell range (for example, “A2:D12” or “A:D”), and “query” with your search query.

The optional “headers” argument sets the number of header rows to include at the top of your data range. If you have a header that spreads over two cells, like “First” in A1 and “Name” in A2, this would specify that QUERY use the contents of the first two rows as the combined header.

In the example below, a sheet (called “Staff List”) of a Google Sheets spreadsheet includes a list of employees. It includes their names, employee ID numbers, birth dates, and whether they’ve attended their mandatory employee training session.

How to use the query function in google sheets

On a second sheet, you can use a QUERY formula to pull a list of all of employees who haven’t attended the mandatory training session. This list will include employee ID numbers, first names, last names, and whether they attended the training session.

To do this with the data shown above, you could type =QUERY(‘Staff List’!A2:E12, “SELECT A, B, C, E WHERE E = ‘No'”) . This queries the data from range A2 to E12 on the “Staff List” sheet.

Like a typical SQL query, the QUERY function selects the columns to display (SELECT) and identifies the parameters for the search (WHERE). It returns columns A, B, C, and E, providing a list of all matching rows in which the value in column E (“Attended Training”) is a text string containing “No.”

How to use the query function in google sheets

As shown above, four employees from the initial list haven’t attended a training session. The QUERY function provided this info, as well as matching columns to show their names and employee ID numbers in a separate list.

This example uses a very specific range of data. You could change this to query all the data in columns A to E. This would allow you to continue to add new employees to the list. The QUERY formula you used will also update automatically whenever you add new employees or when someone attends the training session.

The correct formula for this is =QUERY(‘Staff List’!A2:E, “Select A, B, C, E WHERE E = ‘No'”) . This formula ignores the initial “Employees” title in cell A1.

If you add an 11th employee who hasn’t attended the training to the initial list, as shown below (Christine Smith), the QUERY formula updates, as well, and displays the new employee.

How to use the query function in google sheets

Advanced QUERY Formulas

The QUERY function is versatile. It allows you to use other logical operations (like AND and OR) or Google functions (like COUNT) as part of your search. You can also use comparison operators (greater than, less than, and so on) to find values between two figures.

Using Comparison Operators with QUERY

You can use QUERY with comparison operators (like less than, greater than, or equal to) to narrow down and filter data. To do this, we’ll add an additional column (F) to our “Staff List” sheet with the number of awards each employee has won.

Using QUERY, we can search for all employees who have won at least one award. The format for this formula is =QUERY(‘Staff List’!A2:F12, “SELECT A, B, C, D, E, F WHERE F > 0”) .

This uses a greater than comparison operator (>) to search for values above zero in column F.

How to use the query function in google sheets

The example above shows the QUERY function returned a list of eight employees who have won one or more awards. Out of 11 total employees, three have never won an award.

Using AND and OR with QUERY

Nested logical operator functions like AND and OR work well within a larger QUERY formula to add multiple search criteria to your formula.

A good way to test AND is to search for data between two dates. If we use our employee list example, we could list all employees born from 1980 to 1989.

This also takes advantage of comparison operators, like greater than or equal to (>=) and less than or equal to ( =QUERY(‘Staff List’!A2:E12, “SELECT A, B, C, D, E WHERE D >= DATE ‘1980-1-1’ and D . This also uses an additional nested DATE function to parse date timestamps correctly, and looks for all birthdays between and equal to January 1, 1980, and December 31, 1989.

How to use the query function in google sheets

As shown above, three employees who were born in 1980, 1986, and 1983 meet these requirements.

You can also use OR to produce similar results. If we use the same data, but switch the dates and use OR, we can exclude all employees who were born in the 1980s.

The format for this formula would be =QUERY(‘Staff List’!A2:E12, “SELECT A, B, C, D, E WHERE D >= DATE ‘1989-12-31’ or D .

How to use the query function in google sheets

Of the original 10 employees, three were born in the 1980s. The example above shows the remaining seven, who were all born before or after the dates we excluded.

Using COUNT with QUERY

Rather than simply searching for and returning data, you can also mix QUERY with other functions, like COUNT, to manipulate data. Let’s say we want to clear a number of all the employees on our list who have and haven’t attended the mandatory training session.

To do this, you can combine QUERY with COUNT like this =QUERY(‘Staff List’!A2:E12, “SELECT E, COUNT(E) group by E”) .

How to use the query function in google sheets

Focusing on column E (“Attended Training”), the QUERY function used COUNT to count the number of times each type of value (a “Yes” or a “No” text string) was found. From our list, six employees have completed the training, and four haven’t.

You can easily change this formula and use it with other types of Google functions, like SUM.

Author
Gordon James
Share article

Google Sheets is a Microsoft Excel-like spreadsheet program that was launched by Google in 2011. The program is useful for prototyping and many other purposes. It is also used for analyzing data and creating dashboards. The Google Sheets is a great tool for tracking the information about your business. However, it is not necessary enough for the advanced analytics. You need to use the Google Sheets functions for further processing.

The query function in Google Sheets is one of the best features in the product and it can be used a lot of ways. In this article I will show you how to use the query function to find data from the same sheet or even different sheets.

How to use the query function in google sheets

Allows you to use a QUERY string instead of SQL code to manipulate and merge records on multiple worksheets. You can also use the QUERY feature in the Google for Sheets application, but for convenience we recommend using it on a PC.

Besides the QUERY function, the VLOOKUP formula is another important function that users should be aware of for better data analysis.

Supplement

Google Sheets has many features that help users perform quality analysis on spreadsheet data. However, if we’re talking about the most important ones, it’s almost impossible not to include Google Sheets’ search feature in this list. This feature allows users to search the database, similar to the SQL function in a Google spreadsheet.

QUERY in Google Sheets is easy to understand, but you need to practice daily to understand and master it better. This feature is essential for all users working in the field of data science and analysis.

Frequently Asked Questions

How do you use the query function in Google Sheets?

The query function in Google Sheets allows you to search for a specific string of text.

Can you query a query in Google Sheets?

Yes, you can query a Google Sheets spreadsheet.

What is query function Google Sheets?

The query function is a function that allows you to search for data in Google Sheets.

Related Tags:

google sheets query from another tabgoogle sheets query from another sheetgoogle sheets query languagegoogle sheets query multiple sheetsgoogle sheets query multiple criteriagoogle sheets query join,People also search for,Feedback,Privacy settings,How Search works,google sheets query from another tab,google sheets query from another sheet,google sheets query language,google sheets query select, where contains,google spreadsheet query where cell value,google sheets query multiple sheets,google sheets query join,google sheets query multiple criteria

The Query with Importrange in Google Sheets is useful if you want to pull the exact data that you need.

Table of Contents
  1. The Anatomy of the Query with Importrange Function
  2. A Real Example of Using Query with Importrange Function
  3. How to Use Query with Importrange Function in Google Sheets

The Query with Importrange does this by merely using the QUERY function first, then nest the IMPORTRANGE function in the formula.

To break it down into its two components:

The QUERY function is one of the most versatile functions in Google Sheets. With QUERY, you can do actions like lookup, sum, count, average, filter, and sort.

On the other hand, the IMPORTRANGE function allows you to import and transfer a range of cells from one spreadsheet to another.

Let’s take an example.

Say, I own a supermarket with hundreds of products. I currently maintain two spreadsheets, one main spreadsheet for all the inventory information — ‘Category’, ‘Inventory ID’, and ‘Food Item’. Whereas, the other spreadsheet contains — ‘Inventory ID’, ‘Quantity’, ‘Price’, and ‘Value’.

I want to import the Inventory ID and the price of items whose quantity is already less than 10, to our main spreadsheet.

So how do we do that?

Easy. We can use our QUERY with IMPORTRANGE to import only the Inventory ID and price to our main spreadsheet. We will supply the functions with the right attributes.

Ultimately, the functions will output a list of Inventory ID and Price of items whose inventory count is less than 10.

You can do a lot of things with the QUERY with IMPORTRANGE functions. In fact, you can import multiple spreadsheets, provided that you have specified all your criteria.

It is one of the many powerful tools to have in your arsenal to solve a lot of your business data entry problems and save half of your data entry time.

Let’s go straight into real-business examples where we deal with actual values and textual strings and how we can write our own Query with Importrange function in Google Sheets.

The Anatomy of the Query with Importrange Function

So the syntax (or how we write) the Query with Importrange function is as follows:

Here’s what each of those terminologies mean:

  • = we must add the equal sign to start off any function in Google Sheets
  • QUERY() is the function responsible for selecting what ranges to display based on your criteria
  • IMPORTRANGE() is a function that allows you to import values from cell ranges in another spreadsheet into your own spreadsheet
  • spreadsheet_url is the link to the spreadsheet where the desired cells are coming from
  • range_string defines the range of cells to be imported. Usually, this has two components: the name of the sheet and the cells range. The components are separated by an exclamation point (!) and enclosed in a quote-unquote symbol, (” “). For example, “Data!A2:D16”
  • query includes the criteria or condition
  • [headers] are optional. But, you usually put “1” if the spreadsheet consists of a row of headers.

Now, it may look complicated but, rest assured that we will go through the step-by-step process on how to exactly use the Query with Importrange functions in Google Sheets. 🙂

A Real Example of Using Query with Importrange Function

Have a look at the example below to see how Query with Importrange functions is used in Google Sheets.

How to use the query function in google sheets

How to use the query function in google sheets

As you can see in the images above, we have two spreadsheets, and the Query with Importrange functions is used to import the ‘Inventory ID’ and ‘Price’ from Spreadsheet 2 to Spreadsheet 1. The function is as follows:

Here’s what this example does:
  • We have actively selected the cell under E1, and we want to use the Query with Importrange function to import Inventory ID and Price of items whose quantity is already less than 10.
  • As you can see in the images above, the Inventory ID and Price of items are found in the 2nd spreadsheet. So, basically, we want to import that information from Spreadsheet 2 to our main spreadsheet.
  • We started with the Query function and had the Importrange nested. We supplied the right attributes for each function.
  • As a condition, we usedless than ( How to Use Query with Importrange Function in Google Sheets
  1. Prepare your spreadsheets. Make sure you have at least two spreadsheets to make the Query with Importrange Function work seamlessly.

How to use the query function in google sheetsSheet 1

How to use the query function in google sheetsSheet 2

  1. Great! Now click on any cell to make it active. This is where you want to put your formula and where you want your data to be imported. For this guide, I have selected the cell E2 from my first spreadsheet.

How to use the query function in google sheets

  1. Start your formula with the QUERY function and an open parenthesis “(“.

How to use the query function in google sheets

  1. Next, write the IMPORTRANGE function and follow it up with an open parenthesis “(“.

How to use the query function in google sheets

  1. Enclosed in a quote-unquote symbol (” “), paste the URL or link of the other spreadsheet where the data you want to import is from. Then, add a comma to separate it from the next attribute.

How to use the query function in google sheets

  1. Great! Now, let’s add the range_string . Write the name of the spreadsheet where your desired data will be imported from, then followed by the cell range. For this guide, I have named the spreadsheet as Data, and my cell range is A2:D16. Then close the string with a close parenthesis “)“.

How to use the query function in google sheets

    We are now ready to add the condition. We want to import the ‘Inventory ID’ and the ‘Price’ which is in columns 1 and 3 (or columns A andC), provided that it meets our criteria, “where column 2 or the Quantity is less than 10“. Therefore, our condition would be select Col1, Col3 where Col2

I’m looking for a way to programmatically populate a spreadsheet that filters data from another spreadsheet based on the logged in user.

I am able to do this using the query function inside a spreadsheet. BUT, unable to figure out a way to call the query function from apps script?

Can this be done? Would appreciate sample code. Thanks.

5 Answers 5

No, there is no API for the Query function that allows it to be called from Google Apps Script. (There is no way to call ANY spreadsheet function in this way, in fact.)

You can get some similar functionality without writing it all yourself, though. The 2D Arrays Library includes a variety of “filter” functions that let you retrieve matching rows.

How to use the query function in google sheets

I do not know whether there is a restriction on that .

Perhaps, through a formula you can get something done than you need.

I’ve managed to use Ala-SQL library.

Read the documentation carefully and you are ready to go!

Create your own translator to connect sheets with the library. Please see more info in my post.

I used sql in custom functions in order to show the way to use it:

=getAlaSql(sql_text, West!A:G, East!A:G, Central!A:G)

  • sql_text use any supported syntax with “select” clause. Note: Ala-SQL also has update, insert, but not with anonymous functions.
  • select Col1 from . union all . select Col1 from ? is a proper syntax. I used Col1-notation. Question marks mean tables or variables from the rest of a function.
  • West!A:G, East!A:G, Central!A:G is a list of tables. The library replaces question marks with this variables.

You can use the following syntax to select rows that contain a specific string using the Google Sheets query function:

=query ( A1:C9 , “ select A, B where B contains ‘this’ “ , 1 )

This particular query selects columns A and B from the cell range A1:C9 where column B contains the string ‘this’ and the 1 specifies that there is 1 header row at the top of the cell range.

The following examples show how to use this function in practice with the following dataset:

How to use the query function in google sheets

Example 1: Select Rows that Contain a String

We can use the following syntax to select all rows where the Team column contains the string ‘Lak‘:

How to use the query function in google sheets

This query returns the two rows that contain the string ‘Lak’ in the Team column.

Example 2: Select Rows that Do Not Contain a String

We can use the following syntax to select all rows where the Team column does not contain the string ‘Lak‘:

How to use the query function in google sheets

Notice that this returns every row where the team name is not equal to Lakers.

Example 3: Select Rows that Contain One of Several Strings

We can use the following syntax to select all rows where the Team column contains the string ‘Lak‘ or the string ‘Mav‘:

How to use the query function in google sheets

This returns only the rows where the team name is equal to Lakers or Mavericks.

Working with Functions

How to use the query function in google sheets

Lesson 15: Working with Functions

Introduction

A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you’ll need to understand the different parts of a function and how to create arguments to calculate values and cell references.

Watch the video below to learn how to create functions.

The parts of a function

Similar to entering a formula, the order in which you enter a function into a cell is important. Each function has a specific order—called syntax—that must be followed in order for the function to calculate properly. The basic syntax to create a formula with a function is to insert an equals sign (=), a function name (AVERAGE, for example, is the function name for finding an average), and an argument. Arguments contain the information you want the formula to calculate, such as a range of cell references.

How to use the query function in google sheets

Working with arguments

Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function.

For example, the function =AVERAGE(B1:B9) would calculate the average of the values in the cell range B1:B9. This function contains only one argument.

How to use the query function in google sheets

Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C2, E1) will add the values of all of the cells in the three arguments.

How to use the query function in google sheets

Creating a function

Google Sheets has a variety of functions available. Here are some of the most common functions you’ll use:

  • SUM: This function adds all of the values of the cells in the argument.
  • AVERAGE: This function determines the average of the values included in the argument. It calculates the sum of the cells and then divides that value by the number of cells in the argument.
  • COUNT: This function counts the number of cells with numerical data in the argument. This function is useful for quickly counting items in a cell range.
  • MAX: This function determines the highestcell value included in the argument.
  • MIN: This function determines the lowest cell value included in the argument.

To create a function using the Functions button:

The Functions button allows you to automatically return the results for a range of cells. The answer will display in the cell below the range.

    Select the range of cells you want to include in the argument. In our example, we’ll select D3:D12.How to use the query function in google sheets

How to use the query function in google sheets

How to use the query function in google sheets

To create a function manually:

If you already know the function name, you can easily type it yourself. In the example below, which is a tally of cookie sales, we’ll use the AVERAGE function to calculate the average number of units sold by each troop.

    Select the cell where the answer will appear. In our example, we’ll select C10.

How to use the query function in google sheets

How to use the query function in google sheets

How to use the query function in google sheets

How to use the query function in google sheets

Google Sheets will not always tell you if your function contains an error, so it’s up to you to check all of your functions. To learn how to do this, read our article on why you should Double-Check Your Formulas.

Google Sheets function list

If you have experience using spreadsheets and want to use Google Sheets to make more advanced calculations, you can explore the Google Sheets function list. It is a handy reference for hundreds of financial, statistical, and data analysis functions.

How to use the query function in google sheets

If you are familiar with functions found in Microsoft Excel’s Function Library, you will find that the Google Sheets function list has many of the same functions.

To access the function list:

Click the Functions button and select More functions. from the drop-down menu. The Google sheets function list will appear in a new browser tab.

If you’re comfortable with basic functions, you may want to try a more advanced one like VLOOKUP. You can check out our article on How to Use Excel’s VLOOKUP Function for more information. Like most functions, VLOOKUP works the same way in Excel and Google Sheets.