# 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!