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:
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)
- 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_refreshwhich 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)
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.
A good habit to get into, is to try your =Text() calls on their own to see what value they are actually passing to the query. If the format doesnt' match the input it will return #N/A which usually breaks the SQL query
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
For a list of tables and their columns, see the Overview of Tables.