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 the 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 Skyvia before you start using the add-on. If you are not logged in yet, on the Add-ons menu, select Skyvia Query and click Login.
In the open Login to Skyvia window, click Login. After this, in the open 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 for free.
2. Open Query Editor
When you are already logged in to Skyvia, on the Add-ons menu, select Skyvia Query and click Query to open Query Editor.
In the Query Editor, you select a workspace, select a connection to the required 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. Select Workspace
You need to select a workspace to work with. Workspace is a working area of a user or a team of users. Workspace may represent your private workspace if your work alone or may represent the workspace of the company/team you were invited to. Workspace combines all the objects (connections, packages, etc), created by you or by company, in one place.
To select a workspace, click the first drop-down list on the top of the Skyvia Query window and select a necessary workspace.
4. Select Connection
To query data, first you need to connect to the data source you want to query data from. For this, click the second drop-down list on the top of the Skyvia Query window.
If you have already created the necessary connection in Skyvia, select this connection from the list. You may enter part of the connection name to filter the list and find the necessary connection quicker. A connection allows Skyvia to access your data in a database or cloud application.
If you haven’t created the necessary connection yet, create it by clicking New at the bottom of the drop-down list.
The Select Connector page will open. On this page, you will see all the data sources supported by Skyvia. Read more on how to manage connections to your data sources here.
You may filter data sources by selecting the data source category on the left. After you click a certain data source, the Connection Editor window will open. In this window, you specify the connection name and enter other parameters. The parameters you need to enter depend strictly on the cloud app or database you select. You can find more details about specific connections and their parameters in the Cloud Sources and Databases topics. After you create a connection, it will become 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. Configure 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’ve selected a connection to query data from, you need to select the necessary table or cloud object in the Object list. Then simply select checkboxes 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 objects, Skyvia Query Add-on automatically joins these objects by the corresponding foreign keys.
Columns List
The Columns list displays the selected object columns, as well as the columns of other objects, to which the current one has foreign keys. The columns are grouped by their objects. You can select or clear checkboxes for the columns to include or exclude columns from the query.
Note that the groups correspond to the foreign keys/relations, not to the objects. If an object has multiple foreign keys to the same object, 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 objects are joined.
By default, the current object 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 checkbox 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.
Applying Aggregations
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 checkbox 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 checkbox for the Invoice table. We don’t need all the Invoice fields, only the TxnDate and TotalAmt fields.
- Select checkboxes for the TxnDate and TotalAmt fields.
-
For the TotalAmt field, click the value link and select Sum in the list.
-
For the TxnDate 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.
Sorting Data
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.
Filtering Data
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 kinds, 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.
SQL Syntax
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.
What’s Next
Refreshing Data
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, select 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, select 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 specify a description for the query in the Description box.
You can open or delete a saved query in Skyvia later. 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.