Using the =Sage() Function

Demand Sage makes your HubSpot objects (such as deals, contacts, and companies) available via SQL queries. This allows advanced users to bring any table that can be described via SQL into Sheets and the data will automatically be kept up to date. This is accomplished through a function we have created: =Sage()

Usage

In Sheets, if you wanted to sum a range of cells you would use the =Sum function. For example, =Sum(A1:A10) will return the aggregate value of all the cells between A1 and A10. =Sage() is a similar function and can be called from any cell. You will see many of these calls in our Report Library reports.

The function takes several arguments:
=Sage(version, data source, query, refresher)

Function Arguments

• version: The version of the Sage API. This should be set to "v1".
• data source: The data source you wish to query. For HubSpot, use "hubspot"
• query: The actual Postgres SQL query you wish to execute, in quotes. See below for tips on creating this query.
• refresher: Google Sheets re-runs the query when any of the arguments change. So this cell lets you control when the query is updated. We provide a predefined value sage_refresh which will be changed every time we update your data.

Putting all this together, the following will create a table counting how many of your companies came from each source:
=sage("v1", "hubs", "select hs_analytics_source as ""source"", count(*) as ""companies"" from companies group by hs_analytics_source ", sage_refresh)

Quoting

Because the query is double quoted, you need to escape any double quotes you wish to use. This is done by adding a second double quote character inline with the first. In the example above we wrote: ""source"" instead of "source".

Using cell values in the query

The built in Sheets function Text() function allows you to include the value of other cells in your queries. This is accomplished using the & operator. For example, the above query can be modified to let the user filter by a lead source. If their chosen value appeared in cell G2, you could accomplish this with:

=sage("v1", "hubs", "select hs_analytics_source as ""source"", count(*) as ""companies"" from companies where hs_analytics_source = '"&Text(G2, "T")&"' group by hs_analytics_source", sage_refresh)

To understand what is happenning, examine '"&Text(G2, "T")&"' The expression we want to pass to SQL is going to be 'Some Value'. So we begin with the ' character. Then we want to use a function so we end the quoted string. Then the & operator concatenates the result of the Text function to the string. In the Text function, we specified we wanted the value from cell G2 and "T" specifies this as a Text value. Finally, we use the & operator one last time to concatenate the remaining portion of the string.

Common uses of cell values in queries are:

• Using Data Validation (Menu > Data > Data Validation) to create drop downs in the sheet with predefined values users can choose from.
• Using cells with dates (which Sheets will automatically create a calendar picker for) in WHERE clauses for changing date ranges of queries

Schema

For a list of tables and their columns, see the Overview of Tables.