EXAMPLE - Nested Functions

Contents:

On January 27, 2021, Google is changing the required permissions for attaching IAM roles to service accounts. If you are using IAM roles for your Google service accounts, please see Changes to User Management.

This simple example illustrates how the following functions operate on nested data.

ARRAYCONCAT - Concatenate multiple arrays together. See ARRAYCONCAT Function.
ARRAYINTERSECT - Find the intersection of elements between multiple arrays. See ARRAYINTERSECT Function.
ARRAYCROSS - Compute the cross product of multiple arrays. See ARRAYCROSS Function.
ARRAYUNIQUE - Generate unique values across multiple arrays. See ARRAYUNIQUE Function.

Source:

Code formatting has been applied to improve legibility.

Item	ArrayA	ArrayB
Item1	`["A","B","C"]`	`["1","2","3"]`
Item2	`["A","B"]`	`["A","B","C"]`
Item3	`["D","E","F"]`	`["4","5","6"]`

Transformation:

You can apply the following transforms in the following order. Note that the column names must be different from the transform name, which is a reserved word.

Transformation Name	`New formula`
Parameter: Formula type	`Single row formula`
Parameter: Formula	`ARRAYCONCAT([Letters,Numerals])`
Parameter: New column name	`'concat2'`

Transformation Name	`New formula`
Parameter: Formula type	`Single row formula`
Parameter: Formula	`ARRAYINTERSECT([Letters,Numerals])`
Parameter: New column name	`'intersection2'`

Transformation Name	`New formula`
Parameter: Formula type	`Single row formula`
Parameter: Formula	`ARRAYCROSS([Letters,Numerals])`
Parameter: New column name	`'cross2'`

Transformation Name	`New formula`
Parameter: Formula type	`Single row formula`
Parameter: Formula	`ARRAYUNIQUE([Letters,Numerals])`
Parameter: New column name	`'unique2'`

Results:

For display purposes, the results table has been broken down into three separate sets of columns.

Column set 1:

Item	ArrayA	ArrayB	concat2	intersection2
Item1	`["A","B","C"]`	`["1","2","3"]`	`["A","B","C","1","2","3"]`	`[ ]`
Item2	`["A","B"]`	`["A","B","C"]`	`["A","B","A","B","C"]`	`["A","B"]`
Item3	`["D","E","F"]`	`["4","5","6"]`	`["D","E","F","4","5","6"]`	`[ ]`

Column set 2:

Item	cross2
Item1	`[["A","1"],["A","2"],["A","3"],["B","1"],["B","2"],["B","3"],["C","1"],["C","2"],["C","3"]]`
Item2	`[["A","A"],["A","B"],["A","C"],["B","A"],["B","B"],["B","C"]]`
Item3	`[["D","4"],["D","5"],["D","6"],["E","4"],["E","5"],["E","6"], ["F","4"],["F","5"],["F","6"]]`

Column set 3:

Item	unique2
Item1	`["A","B","C","1","2","3"]`
Item2	`["A","B","C"]`
Item3	`["D","E","F","4","5","6"]`

Send Feedback

Page tree

EXAMPLE - Nested Functions