How to Query Data to Google Sheets
In Skyvia Query add-on, you get data from a data source to Google Sheets by creating and running queries. Query is an inquiry into the data source, using an SQL SELECT statement. Query is used to extract data from the source in a readable format according to the user’s request.
Skyvia Query add-on allows creating a query to retrieve data to Google Sheets either visually, in visual Query Builder, or simply by entering an SQL statement. You can create and run a query in a few simple steps:
1. Log In to Skyvia
You must log in to to Skyvia before you start using the add-on. If you are not logged in yet, on the Add-ons menu, point to Skyvia Query and click Login. Then click the Login button. After this, in the opened tab, enter your Skyvia credentials, email and password, and click Sign In. Then you may close this tab and return to your Google Sheets workbook.
If you do not have a Skyvia account yet, you can register for free at https://app.skyvia.com/register. Note that you can query data 5 times per day completely free.
2. Open Query Editor
To open Query Editor, on the Add-ons menu, point Skyvia Query and click Query.
In the Query editor, you can select a connection to the data source, configure and run your query. Query Editor has two tabs — Builder and SQL. The Builder tab allows you to configure query visually, while the SQL tab allows you to specify an SQL statement to query data.
3. Connect to Data Source
To query data, first you need to connect to the data source you want to query data from. For this, click the topmost drop-down list in the connection editor.
If you already have the necessary connection created on Skyvia, select this connection from the list. You may enter a part of the connection name to filter the list and quickly find a connection. If you haven’t created the necessary connection yet, you can create a new connection.
A connection allows Skyvia to access your data in a database or cloud application. Each connection has the name that you specify, in order to help you find the necessary connection. To create a connection, click the New link at the bottom of the opened list.
This will open the Connections page in Skyvia, where you can manage connections to your data sources, in a new tab.
On this page, click New, and then click a data source you want to connect to. You may filter data sources by selecting the data source category on the left. After you click a data source, a connection editor will open. In this connection editor you will need to enter the connection Name and other parameters. The details of which parameters you need to enter depend on which cloud app or database you select. You can find more details about specific connections in the topics about corresponding Cloud Sources and Databases. After you create a connection, it will be available in the drop-down list of the Query Editor.
Note that you can create an unlimited number of connections to various data sources in your Skyvia account.
4. Configuring Query Visually
Skyvia offers a convenient visual Query Builder for defining your queries visually. It allows you to quickly select which data to retrieve, configure data filters, sorting, and aggregations.
When you configure a query visually, Skyvia Query Add-on automatically generates the underlying SQL SELECT statement. You can view it by switching to the SQL tab of the query editor.
Selecting Data to Retrieve
When configuring a query, the first step is to determine what data you need. After you selected a connection to query data from, you need to select the necessary table or cloud object in the Object list. Then simply select check boxes for the object fields in the Columns box.
Querying Data from Multiple Tables (Objects)
Query Builder allows you to query data from multiple tables (objects) from a data source. For this, the objects must have a relation between them in the data source — a foreign key. You can select columns not just from the object that is currently selected in the Object list, but also from all the objects, to which the current object has foreign keys.
Thus, if you want to select data from more than one table, you should select a table that is lower in the hierarchy in the Object list. For example, if you need to get product information with prices from Salesforce, you will need to select the PricebookEntry object in the Object list, not Product.
When you select data from multiple tables, Skyvia Query Add-on automatically joins these tables by the corresponding foreign keys.
The Columns list displays check boxes for all the available columns from the currently selected table and from the tables, the current one has foreign keys to. The columns are grouped by their tables.
Note that the groups correspond to the foreign keys/relations, not to the tables. If a table has multiple foreign keys to the same table, the latter one will be present in the list multiple times, with names including the corresponding foreign key names. Thus, you can explicitly choose by which foreign key the tables are joined.
By default, the current table column group is expanded, and groups for all the foreign keys are collapsed. You can expand/collapse these groups by clicking down/up arrow in the right part their header. The check box in the left part of a group header allows you to quickly select and clear all the columns in the group.
Grouping and Aggregating Data
Skyvia Query Add-on allows you to group and aggregate data by some criteria. For example, you may quickly retrieve the list of customers with the numbers or sums of their orders, etc. To group and aggregate data, you simply select aggregation functions for columns you want to aggregate. Skyvia Query Add-on automatically groups data by all other selected columns.
Skyvia supports the following aggregation functions: COUNT, AVG, MIN, MAX, SUM. COUNT is supported for most of the column types, while other aggregations — only for columns with numeric data.
To apply an aggregation, click the value link near the name of a selected column and then select the necessary function from the list. Note that only the functions, applicable to the type of the selected column, are displayed in this list.
To remove a function from a column click the fn link near the column name and then click Value.
Grouping Data By Time Periods
It is even more convenient to group and aggregate data by time periods because of handy functions that can be applied to datetime data.
Skyvia Query Add-on offers a number of functions that extracts a portion of date in a form, convenient for grouping by different time periods. For example, Day function returns only the date portion of a value, like “2017-02-16”, allowing you to group results by a day. The following table lists functions, available for datetime columns:
|Function Name||Description||Example of a returned value|
|Day||Returns the date portion of a value||2017-02-16|
|Month||Returns the year and month portions of a value||2017-02|
|Quarter||Returns the year and quarter number||2017,1|
|Year||Returns the year portion of a value||2017|
|Day of Month||Returns the number of day in a month||16|
|Month of Year||Returns the number of a month||2|
|Quarter of Year||Returns the number of a quarter||1|
To group and aggregate data by a time period, simply select check box for a column (or columns) you want to aggregate data from and for date or datetime column, which you want to use for grouping. Then select the corresponding aggregation function for the column that you want to aggregate, and the corresponding date function (like Day, Month, Quarter, or Year) for the datetime column. The result will be grouped, respectively, by day, month, quarter or year. See an example of creating such report below.
Example of Aggregating Data
Suppose we want to get total sum of the QuickBooks invoices per month. So we need to get and sum the invoices’ total amount, grouping the result by the invoice date. We can do it in the following way:
- First, we need to log in to Skyvia and open the query editor as described above.
Then we select our QuickBooks connection and the Invoice object from it in the corresponding lists.
Clear check box for the Invoice table. We don’t need all the Invoice fields, only the TxnDate and TotalAmt fields.
- Select check boxes for the TxnDate and TotalAmt fields.
For the TotalAmt field, click the value link and select Sum in the list.
For the TotalAmt field, click the value link and select Month in the list. This is how we get the sums for all invoices per month.
That’s all, we can run our query.
Configuring data sorting in Query Builder is easy. All you need is to select one or more columns for sorting either from the selected object or from its related objects and specify the sorting order - ascending or descending.
To add a sorting column, simply click the button under the Order By heading. Then select an object and its field to order data by in the first and second lists, respectively. You may sort data by any column from the selected object or the objects it references, regardless of whether you select data from this column or not. If necessary, you may add more columns to the Order By list.
By default, Skyvia Query Add-on uses ascending sorting order. Click the Sort button if you need to change order.
To filter data, you can define various filter conditions for your queries in Query Builder. Read Filtering Data in Skyvia Query Add-on for more information about filter kins, adding filters, etc.
Working with Query SQL Code
Note that this step is not required, and you may skip it.
While Query Builder usually is enough for most cases, sometimes you may need to configure your query more deeply and flexibly. In this case you may switch to direct editing of the query SQL code.
Switching between SQL and Builder Tabs
You can switch to the SQL code at any time - either immediately and create your SQL query from the scratch or configure your query in the Query Builder first, and then tinker the automatically generated SQL code.
To view and edit the SQL code of the query, click the SQL tab in the top right corner of the query editor.
Please note, however, that if you edit the generated SQL, you won’t be available to edit this query visually in Query Builder any more.
When working with the database data, use the SQL syntax of the corresponding database. For cloud applications, use SQLite SQL syntax.
Running Your Query
After you finished creating your query, whether visually or by typing SQL code, you can run your query by clicking the Run button. Skyvia will load the queried data to the Google Sheets document. Note that it can take some time, depending on the number of the records, query complexity, and the speed of the data source itself.
Skyvia Query Add-on remembers the query you have used for the sheet. Whenever necessary, you can refresh the queried data from the data source. For this, on the Add-ons menu, point Skyvia Query and click Refresh Current Sheet or Refresh All Sheets.
Modifying the Query
You can edit the query in Skyvia Query editor in the following way: on the Add-ons menu, point Skyvia Query and click Query. This query will open in the Query Editor, and you may modify it, set parameter values, re-execute it, etc.
Skyvia Account Connection
If you share this workbook, your data source connection is not shared with it. Your data source connection is stored on Skyvia and is linked to your Skyvia account. If the users you share your workbook with have Skyvia Query Google Sheets Add-on installed, they will be able to see the query SQL code, but they won’t be able to change it or refresh data, unless they have the same connection in their own Skyvia accounts.
Saving Query for Future Use
If you want to re-use the query you created for future use in other Google Sheets workbooks, you can save it in our Query Gallery. Saved queries are stored in Skyvia, linked to your Skyvia account as well as connections. To save your query for future use, perform the following actions:
Click Save in the Query Editor. If you use an already saved query, you may then click Save to overwrite it or Save As to save the current query under a new name.
Enter the name of the query to the Name box.
Optionally select an existing folder in the Folder list. You can rename and delete folders in Skyvia, in the Query Gallery.
Optionally specify a description for the query in the Description box.
You can also open query from Query Gallery in Google Sheets Add-on.
Note that the Gallery already contains some predefined queries for different data sources for common use cases, which you can use directly or study in order to learn how to create your own queries.