**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 |
| `["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"]
``` |

This page has no comments.