ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Relation for Google sheets
2
This sheet has user defined functions to make simple relational algebra.
3
It uses the same syntax as Relation for Excel, with the following exception: expressions are made in Javascript, not in formula language.
4
The fun part: The functions work also on Sheets on iPad.
5
You can save a copy of the sheet to use the functions
6
This is work in progress
7
Matthias Bürcher
8
7.1.2018
9
matti@belle-nuit.com
10
https://www.belle-nuit.com/relation-for-google-sheets
11
12
Data
13
codenamecodenameprice
manufacturer
14
1Sony1Hard drive2405
15
2Creative Labs2Memory1206
16
3Hewlett Packard3ZIP drive1504
17
4Iomega4Floppy disk56
18
5Fujitsu5Monitor2401
19
6Winchester6DVD drive1802
20
7CD drive902
21
8Printer2703
22
9
Toner cartridge
663
23
10DVD burner1802
24
Functions
25
relRange
code::name
1::Sony
2::Creative Labs
3::Hewlett Packard
4::Iomega
5::Fujitsu
6::Winchester
code::name::price::manufacturer
1::Hard drive::240::5
2::Memory::120::6
3::ZIP drive::150::4
4::Floppy disk::5::6
5::Monitor::240::1
6::DVD drive::180::2
7::CD drive::90::2
8::Printer::270::3
9::Toner cartridge::66::3
10::DVD burner::180::2
26
27
relSelect
code::name
4::Iomega
5::Fujitsu
6::Winchester
code::name::price::manufacturer
2::Memory::120::6
5::Monitor::240::1
code::name::price::manufacturer
9::Toner cartridge::66::3
javascript has to be used for expressions, not google sheet formula
28
29
relProjectcode
4
5
6
name::price
Memory::120
Monitor::240
price_SUM
360
30
31
relExtendcode::code2
4::8
5::10
6::12
name::price::init
Memory::120::Me
Monitor::240::Mo
32
33
relRenamecode::name
4::8
5::10
6::12
cp::product::price::code
2::Memory::120::6
5::Monitor::240::1
34
35
relUnion
code::name
4::8
5::10
6::12
4::Iomega
5::Fujitsu
6::Winchester
code::name::price::manufacturer
9::Toner cartridge::66::3
2::Memory::120::6
5::Monitor::240::1
36
37
relDifference
code::name
4::Iomega
5::Fujitsu
6::Winchester
code::name::price::manufacturer
9::Toner cartridge::66::3
38
39
relIntersectioncode::name
code::name::price::manufacturer
2::Memory::120::6
5::Monitor::240::1
40
41
relJoin
code::name::cp::product::price
1::Sony::5::Monitor::240
6::Winchester::2::Memory::120
code::name::cp::product::price
1::Sony::5::Monitor::240
6::Winchester::2::Memory::120
2::Creative Labs::::::
3::Hewlett Packard::::::
4::Iomega::::::
5::Fujitsu::::::
42
43
relOrder
code::name::cp::product::price
6::Winchester::2::Memory::120
1::Sony::5::Monitor::240
code::name::cp::product::price
2::Creative Labs::::::
5::Fujitsu::::::
3::Hewlett Packard::::::
4::Iomega::::::
1::Sony::5::Monitor::240
6::Winchester::2::Memory::120
44
45
relLimit
code::name::cp::product::price
1::Sony::5::Monitor::240
code::name::cp::product::price
1::Sony::5::Monitor::240
6::Winchester::2::Memory::120
46
47
relCellcodenamecpproductprice
48
1Sony5Monitor240
49
6Winchester2Memory120
50
51
relFilter
name
Sony
Creative Labs
Hewlett Packard
name
Creative Labs
Hewlett Packard
Sony
name
Hewlett Packard
Sony
name_COUNT
3
code_SUM::code_COUNT::price_COUNT::price_AVG
21::6::2::180
52
53
relAssertTRUETRUETRUE
54
FALSE
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100