Add Formula Feature

Add Formula Feature will allow you to apply spreadsheet formulas while exporting the data. You’ll need to use “$1” as a placeholder in the formula.

As there are many use cases, we’ll take a look at how we can use it to export different

• Date Formats
• Number & Currency Formats
• Text Format
• Math Operations
• Advanced – Rendering Images

By Default, the format of the date exported in digixport is YYYY-MM-DD. You can use the “text” formula to change the date formats.

The syntax of the formula is: TEXT(number, format)

Use the “$1” notation in the formula box as a placeholder for that field, so under “date” field the formula would look like: =text(“$1”, “yyyy/mm/dd”) 

Below are the other formats you can use.  As per Google Sheets, TEXT supports the following date and time patterns:

• d for the day of the month as one or two digits.
• dd for the day of the month as two digits.
• ddd for the short name of the day of the week.
• dddd for the full name of the day of the week.
• m for the month of the year as one or two digits or the number of minutes in a time. Month will be used unless this code is provided with hours or seconds as part of a time.
• mm for the month of the year as two digits or the number of minutes in a time. Month will be used unless this code is provided with hours or seconds as part of a time.
• mmm for the short name of the month of the year.
• mmmm for the full name of the month of the year.
• mmmmm for the first letter in the month of the year.
• yy for the year as two digits.
• yyyy for the year as four digits.

You can try different formats as per your needs (Eg: yyyy/mm/dd, yyyy/dd/mm, mmm dd, yy,  etc)

You can use the same “text” formula to change the currency formats. The syntax is the same. =Text(“$1″,”$0.00”)

In the above example, we have formatted the “spend” field with 0 decimal ‘$’ format & “cpc” with 2 decimal ‘$’ format.

For currency using “,” separator instead of “.” (dot), you can replace the dot with comma by the formula in the spend field: =REPLACE($1,FIND(“.”,$1),1,“,”) . Output: $543.56 -> $543,46.

To round off the currency use =TEXT($1,”$0″). Output: $543.66 -> $544.

For other number formats as below, use these formulas:

• 1,000 -> =TEXT($1,”0,00″)
• 12.32% -> =TEXT($1,”0.00%”)
• 12.3% -> =TEXT($1,”0.0%”)
• 12 -> =TEXT($1,”0″)
• 4 1/2 -> =TEXT(4.5 ,“# ?/?”)
• 5.32E+05 -> =TEXT(532000,“0.00E+00”)

Here’s the google sheet doc for TEXT formula:

• 0 forces display of zeros if a number has fewer digits than the format specifies. For example, TEXT(12.3,"000.00") produces 012.30. Numbers which have more digits to the right of the decimal point than the pattern are rounded to the indicated number of places. For example, TEXT(12.305,"00.00")results in 12.31.
• # is similar to 0 but does not force the display of zeros on either side of the decimal point. For example, TEXT(12.3,"###.##") produces 12.3.

You can use formulas like replace, lower, upper & more to modify the output field’s text. Say you want to lower case all the campaign names.

You can select the campaign name & add the formula: =Lower(“$1”)

Other use cases would be

• Capitalize Campaign Name -> =Upper(“$1”)
• Concatenate String To Campaign Name -> =$1&”String”
• Check IF Campaign Name Contains a String (Say “Shoes”) -> =REGEXMATCH(“$1”,“Shoes”)
• Extract A String From Campaign Name -> =REGEXEXTRACT(“$1”,“regular_expression”)

You can perform multiplication operation on number type fields.

Say you want to convert the currency, you can select the spend & add the formula: =$1*cuurencyConversionNumber

Other use cases would be

• Adding -> =$1+100
• Substracting -> =$1-100
• Multiply -> =$1*100
• Divide -> =$1/100
• Combination -> ($1*100)/120

If you want to render the image (say facebook ad image) in the sheet, you can do so by using “Image” formula. Make sure to select Ad image URL field.

Formula: =IMAGE(“$1”,2)

As per google sheet docs, here’s the syntax for =image

IMAGE(url, [mode], [height], [width])

• url – The URL of the image, including protocol (e.g. http://).
  • The value for url must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
• mode – [ OPTIONAL – 1 by default ] – The sizing mode for the image
  • 1 resizes the image to fit inside the cell, maintaining aspect ratio.
  • 2 stretches or compresses the image to fit inside the cell, ignoring aspect ratio.
  • 3 leaves the image at original size, which may cause cropping.
  • 4 allows the specification of a custom size.
  • Note that no mode causes the cell to be resized to fit the image.
• height – [ OPTIONAL ] – The height of the image in pixels. mode must be set to 4 in order to set a custom height.
• width – [ OPTIONAL ] – The width of the image in pixels. mode must be set to 4 in order to set a custom width.

Note: You cannot add more formulas than the number of selected fields. Only use $1 as a placeholder for selected fields