Using formulas in spreadsheets
Conduit offers two ways to transfer data to spreadsheets:
Building block “Save dataset to Google Sheets”
Formulas
In this article, I will talk about using formulas.
Conduit supports two formulas to be used in Google Sheets.
REPORT - Place a table to a spreadsheet
METRIC - Place a single metric to a spreadsheet
The Report formula copies a table from Conduit into a spreadsheet. It has four parameters:
report
start_date
days
row_count
The name of a data source is the first parameter of the formula. The source of data can be any building block in the workflow. The screenshot below shows 4 data sources:
table1 - Raw data from Facebook
report8 - Data from Facebook aggregated by Ad Account
csv3 - Raw data from a spreadsheet
union4 - The result of merging rows from table1 and rows from csv3
The formula can use any data source from any workflow.
The Report formula always retrieves data for a range of dates. This range can be selected in two ways:
Explicitly in the formula, e.g. =report(“table1”, "2022-05-01", 7)
Implicitly: the time range selected in the workflow is used
We recommend using explicit date ranges.
To select dates, you can use cell references, e.g. =report(“table1”,B1,C1)
More information you can find here.
Conduit has an auxiliary UI that helps to build a formula.
In this window, the user can choose parameters for the formula, copy and paste the formula to a cell in the spreadsheet.
After the user inserts the formula for the first time, Google Sheets stores the response from Conduit server for a few days. Changes to data in Conduit don’t automatically change data in the spreadsheet. This is a known limitation of Google Sheets.
For example, you changed the dimensions set in the construction block Create Report. It doesn’t mean that the spreadsheet will be automatically updated.
To update the spreadsheet, you have to change the formula and press Enter.
When Google Sheets sees a new formula, it re-queries data from Conduit. E.g. you can select the name of another report and then get the previous name back.
Let’s have a look at the example.
Let’s create a new workflow.
Drag and drop a new Pull Data block.
Select Facebook (Demo) as a data source. This data source should be connected first.
Rename the block as ‘table1’.
Create a new spreadsheet
Connect Conduit's Google Add On to the spreadsheet.
To do this, click Launch, just once.
Now, we can enter =report(“table1”) in cell A1 and hit Enter.
In a second, the heading, column names, and data will appear in the spreadsheet.
Total rows shows how many data rows there are in table1 report.
Rows shows how many rows are now downloaded to the spreadsheet.
You can turn the header off. To turn it off, go to Settings
and deselect the checkboxes.
More than one Report formula can be used for one spreadsheet.
Let’s create the second block with the name ‘table2’.
Enter =report(“table2”) in cell A14 and hit Enter.
Now, the spreadsheet contains two tables.
In this case, check the dataset name in the formula
This message warns that the dataset is bigger than free space in the spreadsheet.
Let’s look at the screenshot below.
The formula says that it wants to take 100 rows from the dataset, but there is another formula in Row 14. This is why the maximum number of rows for the formula is 12 (10 data rows and 2 heading rows).
The Metric formula copies a cell from Conduit to a sheet. It has four parameters:
report
metric
start_date
days
Report is the source of data. This parameter is similar to the Report formula.
Metric is the name of a column in the data source.
You can look through all possible names in three ways:
Click Metrics
Export data from the data source and open the CSV file in Excel or Numbers.
Also, you can open Formula Builder and the the list of formulas:
Building block “Save dataset to Google Sheets”
Formulas
In this article, I will talk about using formulas.
Conduit supports two formulas to be used in Google Sheets.
REPORT - Place a table to a spreadsheet
METRIC - Place a single metric to a spreadsheet
REPORT
The Report formula copies a table from Conduit into a spreadsheet. It has four parameters:
report
start_date
days
row_count
report- data source name
The name of a data source is the first parameter of the formula. The source of data can be any building block in the workflow. The screenshot below shows 4 data sources:
table1 - Raw data from Facebook
report8 - Data from Facebook aggregated by Ad Account
csv3 - Raw data from a spreadsheet
union4 - The result of merging rows from table1 and rows from csv3
The formula can use any data source from any workflow.
Date range
The Report formula always retrieves data for a range of dates. This range can be selected in two ways:
Explicitly in the formula, e.g. =report(“table1”, "2022-05-01", 7)
Implicitly: the time range selected in the workflow is used
We recommend using explicit date ranges.
To select dates, you can use cell references, e.g. =report(“table1”,B1,C1)
More information you can find here.
Using Formula Builder
Conduit has an auxiliary UI that helps to build a formula.
In this window, the user can choose parameters for the formula, copy and paste the formula to a cell in the spreadsheet.
Spreadsheet updates
After the user inserts the formula for the first time, Google Sheets stores the response from Conduit server for a few days. Changes to data in Conduit don’t automatically change data in the spreadsheet. This is a known limitation of Google Sheets.
For example, you changed the dimensions set in the construction block Create Report. It doesn’t mean that the spreadsheet will be automatically updated.
To update the spreadsheet, you have to change the formula and press Enter.
When Google Sheets sees a new formula, it re-queries data from Conduit. E.g. you can select the name of another report and then get the previous name back.
Demo: How the Report formula is used
Let’s have a look at the example.
Let’s create a new workflow.
Drag and drop a new Pull Data block.
Select Facebook (Demo) as a data source. This data source should be connected first.
Rename the block as ‘table1’.
Create a new spreadsheet
Connect Conduit's Google Add On to the spreadsheet.
To do this, click Launch, just once.
Now, we can enter =report(“table1”) in cell A1 and hit Enter.
In a second, the heading, column names, and data will appear in the spreadsheet.
Total rows shows how many data rows there are in table1 report.
Rows shows how many rows are now downloaded to the spreadsheet.
You can turn the header off. To turn it off, go to Settings
and deselect the checkboxes.
More than one Report formula can be used for one spreadsheet.
Let’s create the second block with the name ‘table2’.
Enter =report(“table2”) in cell A14 and hit Enter.
Now, the spreadsheet contains two tables.
Errors in formulas and their correction
Dataset with name {} not found.
In this case, check the dataset name in the formula
Array result was not expanded because it would overwrite data in {}.
This message warns that the dataset is bigger than free space in the spreadsheet.
Let’s look at the screenshot below.
The formula says that it wants to take 100 rows from the dataset, but there is another formula in Row 14. This is why the maximum number of rows for the formula is 12 (10 data rows and 2 heading rows).
METRIC
The Metric formula copies a cell from Conduit to a sheet. It has four parameters:
report
metric
start_date
days
Report is the source of data. This parameter is similar to the Report formula.
Metric is the name of a column in the data source.
You can look through all possible names in three ways:
Click Metrics
Export data from the data source and open the CSV file in Excel or Numbers.
Also, you can open Formula Builder and the the list of formulas:
Updated on: 12/07/2023
Thank you!