[Free] Spreadsheet Extension with Web Component
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.
/spreadsheets/d/Your_Sheet_ID/edit#gid=0
Step3: Get Sheet Name Like: Sheet1, Sheet2…
Download Spreadsheet Extension
GoogleSheet.aix (6.5 KB)
[V2] Advance Google Sheet Extension with Power of SQL Query
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
Spreadsheet Get Single or Multiple Columns
Spreadsheet Get Single Row
Row = 0
than it Return Column Names
Spreadsheet Get Cell
Column: Column ID
Row: Row Number
Row = 0
than it Return Column Name Cell
Spreadsheet Get Range
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.
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.
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
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