# Overview

KSemantics is an AI-powered Google Sheets add-on for generating written reports to explain your data.

You can find KSemantics on the Google Workspace Marketplace. You can get started with a 7-day free trial and purchase the subscription with a one-time payment.

KSemantics builds on Google Sheet's inbuilt QUERY function to write explanatory notes on the data generated from the function. Here we will take a look at the QUERY function and see how KSemantics works with it.

#Query Function

Query function is an inbuilt Google Sheet function. It works with the Google Visualization Query Language. You can perform SQL-like operations on Google Sheet data, including aggregations and filters within the same function.

Query function takes three arguments, and all three are required for KSemantics to produce understandable reports.

The three arguments are:

• Data

  Enter the cell range containing the data (e.g., A1:G500). If the data is a different sheet, use the sheet name followed by an exclamation and the cell range (e.g., Sheet1!A1:G500 or 'My sheet'!A1:G500).

• Query

  Use double quotes to write a query in Google Visualization Query Language. KSemantics only supports quoted queries and does not support a query linked from another cell. (e.g., =query(Sheet1!A1:G500, 'select A, B', 1) is supported while =query(Sheet1!A1:G500, A2, 1) is NOT supported)

• Header

  Indicate the row number in which the headers are present. It is usually one since column titles are commonly in the first row.

QUERY(data, query, [headers])

# Select

The select is the first clause in the query for listing the columns in the query statement's writing order. For instance, select C,A,B will list the columns starting with C followed by A and B.

KSemantics DOES NOT support a query statement with a missing select clause or containing only select *.

#Where

The where clause filters the data with the conditions set on one or more columns. There are no specific requirements for usage with KSemantics, you may use the where clause as you see fit. You can also use Scalar Functions alongwith the where clause for specific cases.

Numerical columns use comparison operators to filter rows.

• <= less than and equal to
• < less than
• >=greater than and equal to
• >greater than
• =equal to
• != or <>not equal to

String columns use clauses to pick rows with string comparisons.

• containschecks for the contents of the string that exactly match the term and is case sensitive. For e.g., where C contains 'Statematches with 'New York State', 'United States' and 'State of California' but not with 'state of Alaska'.
• starts withchecks for the start of the string that matches the term and is case sensitive. For e.g., where C starts with 'Statematches 'State of California' but not with 'New York State', 'United States' and 'state of Alaska'.
• ends withchecks for the end of the string that exactly match the term and is case sensitive. For e.g., where C ends with 'Statematches with 'New York State' and but not with 'State of California', 'United States', or 'state of Alaska'.
• matchesuses regular expression to match rows of the selected column. For e.g., where C matches '.*state matches with 'New York State' and but not with 'State of California', 'United States', or 'state of Alaska'.
• likeuses wild card search with underscore _ for one character match or % for multiple matches. For e.g., where C like '%state%matches with all 'New York State', 'State of California', 'United States', and 'state of Alaska'.

# Group By

The group by clause works withAggregate Functions. You can select one or more columns to group by and one or more columns to aggregate over. E.g., if you want to find the total revenue (in column B) for each product (in column A), then you will have to use select A, sum(B) group by A. With KSemantics, we recommend that you list all the grouping columns with the select clause. This allows KSemantics to provide detailed explanations.

#Pivot

KSemantics DOES NOT support thepivot clause.

# Order By

The order by clause orders the rows of the specified columns. For instance, select C,A,B order by B will list the columns and order them by B.

There are no specific requirements for usage with KSemantics, you may use the order by clause as you see fit. You can also use Scalar Functions, or Aggregate Functions, alongwith the order by clause for specific cases. E.g., select C,A,sum(B) group by C,A order by sum(B).

#Limit

The limit clause selects only the rows to the set number. There are no specific requirements for usage with KSemantics, you may use the limitclause as you see fit.

# Offset

The offset clause skips the rows to the set number. There are no specific requirements for usage with KSemantics, you may use the offsetclause as you see fit.

#Label

The label clause sets the column title. There are no specific requirements for usage with KSemantics, you may use the labelclause as you see fit.

# Format

The format clause lets you manipulate the content of the selected column. There are no specific requirements for usage withKSemantics, you may use the formatclause as you see fit.

#Options

The options clause provides additional options for query execution. There are no specific requirements for usage withKSemantics, you may use the optionsclause as you see fit.

# Aggregate Functions

Aggregate Functions are sum, avg, count, min and max. They perform grouping action on columns. When used with group by or pivot, the Aggregate Functions group with unique labels of the non-aggregated columns.

Aggregate Functions can be used in select, order by, label, or format clauses but NOT with where, group by, pivot, limit, offset, or options clauses. Aggregate Functions only take column ids and they cannot be used along with Scalar Functions or Arithmetic Operators.

#Scalar Functions

Scalar Functions are year, month, day, hour, minute, second, millisecond, quarter, dayOfWeek, now, dateDiff, toDate, upper and lower.

Scalar Functions apply operations to transform columns. You can use them with Arithmetic Operators to create new columns. They can be used with select, where, group by, pivot, order by, label, and format clauses.

# Arithmetic Operators

Arithmetic Operators are +, -, *, and / and they transform numerical columns.