Using Formulas in Spreadsheets

PreviousWriting data to spreadsheets: Overwrite and Update NextThe Time Range in formulas

Last updated 11 months ago

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

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. Our objective is to 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.

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.

Using Multiple Formulas within the Same 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.

How to Fix Errors in Formulas

Error: Dataset with name {} not found.

In this case, please check the dataset name in the formula

Error: 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: 1. Click Metrics