[Free] Advance Google Sheet Extension with Filter and Query

[Free] Advance Spreadsheet Extension with Filter and Query Function

This extension allow you to get sheet, row, column, cell, column names, Get Range and advance filter option or you can also apply query and get result in CSV format it does not require any script for that.

Step1: First create New Google Spreadsheet and share this as viewer only.
Step2: Get your Sheet ID from URL.

blocks(37)

/spreadsheets/d/Your_Sheet_ID/edit#gid=0

Step3: Get Sheet Name Like: Sheet1, Sheet2…

blocks(38)

Download Spreadsheet Extension

GoogleSheet.aix (6.5 KB)

You can use All Block Like This

Here file response name can pass any string by using that string name we can extract response content easily.

Extension Blocks

Spreadsheet Get Column Names

component_method

Spreadsheet Get Single or Multiple Columns

blocks(40)

blocks(41)

blocks(42)

Spreadsheet Get Single Row

blocks(43)

Row = 0 than it Return Column Names

Spreadsheet Get Cell

blocks(44)

Column: Column ID
Row: Row Number

Row = 0 than it Return Column Name Cell

Spreadsheet Get Range

blocks(45)

fromCell: Start From Cell Number
toCell: End at Cell number
Response: CSV Table

Spreadsheet Apply Filter and Get Columns Result

Here A B C D are column Names we Get and use any one Condition.

blocks(46)

blocks(47)

blocks(48)

Columns: 
1. *  "for all columns, "
2. A,B "for Column A or Column B" 

Condition:
1. A > 5
2. C LIKE 'Apple'
3. A > 5 AND B < 10
4. A > 5 OR B < 10
5. C > 5 OR F LIKE 'Aman'

Spreadsheet Apply Query and Get Columns Result

Here we share some spreadsheet Query keywords and its uses.

blocks(52)

The keywords are not case sensitive, so you can write “SELECT” or “select” and both work.
However, the column letters must be uppercase: A, B, C, etc. otherwise you’ll get an error.

The keywords must appear in this order (of course, you don’t have to use them all):

Keywords:
SELECT => columns that you want to get 
WHERE => apply condition
GROUP BY => create group by column
ORDER BY => arrange data by column ascending or descending
LIMIT => apply limit and get limited row
LABEL => change column label name

Here i share some Query Examples by using these keywords

Example 1:

SELECT *

The statement retrieves all of the columns from our data table.

Example 2:

SELECT B,D

The statement retrieves B or D columns from our data table.

Example 3:

SELECT * 
WHERE D > 100

The statement retrieves B or D columns where data in Column D>100 from our data table.

Example 4:

SELECT B, C, D 
WHERE C = 'Europe'
Method 1

blocks(50)

Method 2

Method 1 or 2 both return same response if you are familiar with Spreadsheet query than use method 1 otherwise create your own query by using Q1-Q6 blocks
Keyword block Q1 to Q6 always use them in Ascending Order with join block.

Example 5:

SELECT B, C, D 
ORDER BY D ASC

Example 6:

SELECT B, C, D 
ORDER BY B DESC

Example 7:

SELECT B, C, D 
ORDER BY D ASC 
LIMIT 10

Example 8:

SELECT B, C, (D / 700) * 100 
LABEL (D / 700) * 100 'Percentage'
Method 2

Here column D have marks and we get direct percentage and label column name as percentage.

Example 9:

SELECT max(D), min(D), avg(D), count(D)

max => maximum value in column D
max => minimum value in column D
avg => average value in column D
count => count row for applied condition

Example 10:

SELECT C, count(B) 
GROUP BY C

Example 11:

SELECT C, count(B), min(D), max(D), avg(D) 
GROUP BY C 
ORDER BY count(B) DESC 
LIMIT 3

Example 12:

SELECT C, count(B), min(D), max(D), avg(D) 
GROUP BY C 
ORDER BY avg(D) DESC 
LIMIT 3 
LABEL avg(D) 'Average'
Method 2

Method 1 or 2 both return same response if you are familiar with Spreadsheet query than use method 1 otherwise create your own query by using Q1-Q6 blocks
Keyword block Q1 to Q6 always use them in Ascending Order with join block.

You can Read Full Doc for these Advance Queries here

20 Likes

Convert list from CSV Row text or list from CSV table text Like This

blocks(46)

1 Like

Here in the community, as you know, we advise users not to use extensions from DeepHost

2 Likes

OK i doesn’t know about it now i update it …

2 Likes

You might wanna follow the naming conventions -

3 Likes

Great extension @Kuldeep_Pilaniya1

2 Likes

this is my first extension.

3 Likes

great, thank you for your contribution
please modify the extension to follow the naming conventions and update your first post in this thread accordingly
thank you

Taifun

2 Likes

Finally all the web query comes out a extension.

Well-done. :clap: :clap:

Bythe way, you can suggest limit function too here. So the users can take limited values from the sorted order. Eg. While looking for leaderboard, first 10 then can take

ORDER BY D ASC LIMIT 10

2 Likes

Always use SELECT Block First

SELECT * ORDER BY D LIMIT 10

  • Here * for all column
  • Otherwise you can replace * by column name that you want to get like this.

SELECT A,B ORDER BY D LIMIT 10

1 Like

Example 7 is answer

2 Likes

Will this work with private (restricted) google sheets ?

1 Like

This work only with shared sheet like viewer or editor mode but recommend viewer mode for secure your data.

If you want to improve security you can also use encrypt or decrypt function in kodular.

1 Like

not working if value is less than 10

1 Like

Share your sheet picture

1 Like

You can take this lecture and create your own query and get response in your another sheet

After only green text in full query you can use with query block.

Spreadsheet Query Full Lecture

1 Like

problem solved

3 Likes

how to find user row number using query

1 Like

If the user detail in B col, use Select A, B, C, D where B = ’ user input data’

2 Likes

thank you for your attention, but i am asking about user column index, not user row details

1 Like