# ERC20 in sheets /
#### For whom is this tutoriał? Why do we write this?
This tutorial is for you. We want you to understand how to create and implement ERC20 tokens in a sheet.
Meet John (now please, imagine a random regular Joe sitting next to you).\
John always heard from his IT friends about cryptocurrencies, but he couldn't understand why it's a thing and how those things work. He wanted to understand the operations behind this but believed that because he never came into contact with any code, he would not get a thing. If you are like John, this tutorial will definitely help.
> Dude, you got any of those tokens everybody keeps talkin' about?
> [name=John]
In this tutorial, you will learn how to create and implement ERC20 tokens in a ~~sheep~~ sheet. This is theoretical and practical knowledge in a nutshell. If you will make it to the end, you will understand how ERC20-based tokens work and you will understand the schemes on which some cryptocurrencies are based. Finally, you will know how to implement those tokens in a Google Sheet- it's like a walk in the park ;)
## Theoretical introduction
#### What is ERC20?
The ERC20 stands for Ethereum Request for Comments - number 20, which is the standard of tokens. It standardizes and provides a basis for building token contracts on the blockchain with smart contract capabilities. It contains a set of commands and rules, so it can be integrated with other users' wallets and markets. Those functions are:
* **BalanceOf**
A balance of tokens for every user can be checked using this function.
In ERC20 model, every user gets his own "account" which keeps a number of its tokens. Balances change after transactions, which users make.
* **Transfer**
Every user can transfer tokens to other accounts. The transferred amount can not be greater than the number of owned tokens. This way **Transfer** function never creates new tokens.
*Example 1*
*John wants to make a transfer to his friend Vicky. If the number of tokens, which John wants to send to Vicky is right (John has this number of tokens in his account or more), then Vicky will get those tokens on her balance. John's balance will decrement.*
* **Approve**
Using **Approve** function it's possible to allow the spending user's tokens by the other account. The amount allowed can be greater than the amount of owned tokens.
*Example 2*
*Vicky would like to make a transaction with tokens. However, there are no such amount of tokens on her balance. Thanks to the approve function, John can grant permission to Vicky to spend a given number of tokens from his account.*
* **TransferFrom**
Every user, if has approved tokens from someone else, can transfer them to a third account.
*Example 3*
*Vicky wants to send some tokens to Gregor. She, as the spender, can make an operation that is called TransferFrom. Tokens will be transferred from John's balance, as he is the owner of them, and will be sent to Gregor, who is a recipient of those tokens. The previously approved amount is decremented.*
* **Allowance**
Similar to the **BalanceOf**, this function can be used to check approved amounts between any two accounts.
*Example 4*
*Thanks to the Allowance we can see how much John allowed Vicky to spend in his name.*
* **Mint**
This function can increment the balance of every user by a given amount of tokens. Keep in mind, **that this is not a part of the ERC20** standard, but its supporting function. Very often access to it is restricted, so it can be triggered only by the admin account.
*Example 5*
*Thanks to the Mint function John as the admin can mint new tokens for Vicky.*
## Sheet implementation
Now we can move on to the implementation of ERC20 in a sheet.
**The whole goal of this excercise is to show how to calculate users' balances and allowances from a list of transactions. This will reveal to you the links behind all the functions.**
### Sheet 1 - Transactions
We start with creating a sheet with transactions.
*Image 1: Sheet with transactions*
* Column A will be one of the function names, which were defined in the theoretical introduction (mint, transfer, approve, and transfer_from).
* Columns B, C, D, and E will be columns for arguments. Every formula needs to have arguments, so it can happen. Every column represents the next argument. Column B is for the first argument, C for the second one, and so on.
The remaining two functions "balance_of" and "allowance", which were already described in the theoretical introduction, are not changing the state. Those functions are read-only, and they will be visible in *Sheet number 2 - Balances* and *Sheet number 3 - Allowance*.
***Image 2: Sheet with example transactions* ?**
Image 2 shows the example transactions that can be made in that sheet. Now we will explain those transactions and arguments.
* If we want to use transaction **Mint**, we need 2 arguments. How does it work? A recipient account (arg1) gets the number of tokens (arg2). Looking at our example from Image 2, we see that John will get 100 tokens.
* If we want to use **Transfer**, we need 3 arguments. How does it work? Spender (arg1) transfers tokens to the recipient (arg2) with several tokens (arg3). Looking at our example from Image 2, we see John transferring 30 tokens to Vicky.
* If we want to use **Approve**, we need 3 arguments. How does it work? User (arg1) approves another user (arg2) to spend the set number of tokens (arg3). Looking at our example from Image 2, we see John approving Gregor to spend 5 tokens.
* If we want to use **TransferFrom**, we need 4 arguments. How does it work? Spender (arg2) will transfer tokens (arg4) of the owner (arg1) to the recipient (arg3). Looking at our example from Image 2, we see Gregor (arg2) spending 5 tokens (arg4) from John's account (arg1) to Vicky's one (arg3).
### Sheet 2 - Balances
This is where the fun begins. Sheet 2 shows the current balances for every account. We will get through it step by step.
*Image 3: Sheet with balances of every account*
#### What is the Balance?
The balance of an account is a sum of all incomes, substracted by all outcomes connected to the account:
Incomes:
* tokens minted for an account using **Mint** (column B),
* tokens transferred to that account using **Transfer** (column D),
* tokens transferred to that account using **TransferFrom** (column F).
Outcomes:
* tokens transferred out of the account using **Transfer** (column C),
* tokens transferred out of that account, by another allowed account using **TransferFrom** (column E).
#### SUMIF function.
Now, we need to learn a single function from the spreadsheet, that is in the basis of the majority of our calculations in Sheet 2. It is only used in this sheet called ''Balances''.
Google Spreadsheet documentation represents `SUMIF` as:
```
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
```
* sum_range - it sums the given range,
* criteria_range - it sums the given range for criterion,
* criterion - the pattern to use for criteria_range.
Let's see how it looks in practice (using the spreadsheet from Image 3).
```
SUMIFS(B1:B10, C1:C10, ">20")
```
That example sums up the range from A1 to A10, if the selected cell from the B column is bigger than 20, it yields the result of 100.
Now we will discuss how it works for every column the Sheet 2.
**Column A - Account**
Column A is the name of the user's account. To simplify the implementation it needs to be a list of all accounts used in Sheet 1 typed by hand.
**Column B - Minted**
```
=SUMIFS(
transactions!C$2:C;
transactions!A$2:A; "mint";
transactions!B$2:B; A2
)
```
This formula sums up all minted tokens for a given account. It sums numbers from the column C in *Sheet 1 - Transactions* if:
* Column A (function name) in *Sheet 1 - Transactions* is the word *mint*.
* Column (tokens recipient) in *Sheet 1 - Transactions* is the same as a user listed in Column A in *Sheet 2 - Balances*.
**Column C - OUT Transfer**
```
=SUMIFS(
transactions!D$2:D;
transactions!A$2:A; "transfer";
transactions!B$2:B; A2
)
```
It shows how many tokens are taken from the user's account using the **Transfer** function. It sums numbers from the column D in *Sheet 1 - Transactions* if:
* Column A (function name) in *Sheet 1 - Transactions* is the word *transfer*.
* Column B (tokens sender) in *Sheet 1 - Transactions* are the same as a user listed in Column A in *Sheet 2 - Balances*.
**Column D - IN Transfer**
```
=SUMIFS(
transactions!D$2:D;
transactions!A$2:A; "transfer";
transactions!C$2:C; A2
)
```
It shows how many tokens are given to the user's account using the **Transfer** function. It sums numbers from the column D in *Sheet 1 - Transactions* if:
* Column A (function name) in *Sheet 1 - Transactions* is the word *transfer*.
* Column C (tokens recipient) in *Sheet 1 - Transactions* are the same as a user listed in Column A in *Sheet 2 - Balances*.
**Column E - OUT Transfer From**
```
=SUMIFS(
transactions!E$2:E;
transactions!A$2:A; "transfer_from";
transactions!B$2:B; A2
)
```
It shows how many tokens are taken from the owner’s account. It sums numbers from the column E if *Sheet 1 - Transactions* if:
* Column A (function name) in *Sheet 1 - Transactions* is the word *transfer_from*.
* Column B (tokens owner) in *Sheet 1 - Transactions* are the same as a user listed in Column A in *Sheet 2 - Balances*.
**Column F - In Transfer From**
```
=SUMIFS(
transactions!E$2:E;
transactions!A$2:A; "transfer_from";
transactions!D$2:D; A2
)
```
It shows how many tokens are given to the recipient's account. It sums numbers from the column E if *Sheet 1 - Transactions* if:
* Column A (function name) in *Sheet 1 - Transactions* is the word *transfer_from*.
* Column D (tokens recipient) in *Sheet 1 - Transactions* are the same as a user listed in Column A in *Sheet 2 - Balances*.
**Column G - Sum**
```
=B2+D2+F2-C2-E2
```
The final balance for the user in row 2 is a sum of all incomes subtracted by a sum of outcomes.
### Sheet 3 - Allowance
Before explanation of *Sheet 3 - Allowance*, we need to learn a few functions from the Google Spreadsheet. This is more complicated than the two previous sheets, but we will guide you step by step through that.
#### IF function.
Google Spreadsheet documentation represents `IF` as:
```
IF(logical_expression, value_if_true, value_if_false)
```
* logical_expression - it is an expression that we check for being true or false,
* value_if_true - it is the value that it's returned by the function if logical_expression is TRUE,
* value_if_false - it is the value that it's returned by the function if logical_expression is F.
Let's see how it looks in practice.
```
=IF(B2>10; C2+B2; C2-B2)
```
That example checks the expression that the value of the B2 cell is bigger than 10. If it is, then it is added to the value of C2. If it is not, then it is subtracted from the value of C2.
#### OR function.
Google Spreadsheet documentation represents `OR` as:
```
OR(logical_expression1, [logical_expression2, ...])
```
* logical_expression1, logical_expression2 - those are expressions that we check for being true or false, at least one expression is needed in this formula.
Let's see how it looks in practice.
```
OR(B1>25, C1>25)
```
That example checks if cells B1 or C1 are bigger than 25. If it's true for at least one checked logical expression, then the function will show up TRUE. If it's false for all logical expressions, then it will show up FALSE.
#### CONCAT function.
Google Spreadsheet documentation represents `CONCAT` as:
```
CONCAT(value1, value2)
```
* value1, value2 - those are two values, which will be appended to each other.
Let's see how it looks in practice.
```
=CONCAT("erc"; "20")
```
That example adds strings "erc" and "20", so the formula will return the value "erc20".
#### SORT function.
Google Spreadsheet documentation represents `SORT` as:
```
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
```
* range - the given data, that will be sorted,
* sort_column - the index of the column containing the values by which to sort.,
* is_ascending - this shows whether to sort in ascending order (TRUE) or in descending order (FALSE).
Let's see how it looks in practice.
```
sort(E2:E5; A2:A5; TRUE)
```
That example sorts column E by the values given in column A. It sorts in ascending order.
#### LOOKUP function.
Google Spreadsheet documentation represents `LOOKUP` as:
```
LOOKUP(search_key, search_range, [result_range])
```
* search_key - it is the value that we look up in the row or column,
* search_range - it looks in the given range for the search_key,
* result_range - it is the range from which to return a result, this is an optional function.
Let's see how it looks in practice.
```
=LOOKUP("cat"; A1:A100; B1:B200)
```
That example looks up for the word "cat" in the given range from A1 to A100.
If it founds a "cat" in `A66`, then it returns, a value from `result_range`, which will be `B66`.
#### COUNTIF function.
Google Spreadsheet documentation represents `COUNTIF` as:
```
COUNTIF(range, criterion)
```
* range - the range that is tested against criterion,
* criterion - the pattern to apply to a range.
Let's see how it looks in practice.
```
=COUNTIF(A1:A5; ">10")
```
That example counts in column A. Every time it will exceed the value of 10, 1 will be added to the final score.
#### COLUMNS
Before we get to the grid of the current state of allowances, we need to introduce some supporting columns. It's getting a little bit complicated here, so try to focus.
*Image 4: Sheet with supporting columns to calculate the current state of allowances*
**Column A - Key**
```
=if(
OR(
transactions!A4="approve";
transactions!A4="transfer_from"
);
CONCAT(trasactions!B4;trasactions!C4);
""
)
```
Column A looks for the key. Key is unique for every combination of approving and transfer_from methods. It allows to append names to each other and group them. It shows the key, when:
* the function will find phrases *approve* or *transfer_from* in *Sheet 1 - Transactions*.
* the accounts involved in the transaction by the *approve* or *transfer_from* from *Sheet 1 - Transactions* will append to each other by CONCAT function.
**Column B - Method**
```
=if(
transactions!A2="approve";
transactions!A2;
if(
transactions!A2="transfer_from";
transactions!A2;
""
)
)
```
Column B looks for the *approve* or *transfer_from* in *Sheet 1 - Transactions* and copies it to the column from that sheet for clarity. It shows the method, when:
* The function will find the phrase *approve* in *Sheet 1 - Transactions* and copy it.
* if the function will not find the previous phrase it looks up now for phrase *transfer_from* and if it finds it, then it will copy it.
**Column C - Approve value**
```
=IF(
transactions!A4="approve";
transactions!D4;
0
)
```
Column C looks up for the *approve* in *Sheet 1 - Transactions* and then it copies its value. If it does not find that value, it returns zero.
**Column D - Transfer from value**
```
=IF(
transactions!A2="transfer_from";
transactions!E2;
0
)
```
Column D looks up for the *transfer from* in *Sheet 1 - Transactions* and then it copies its value. If it does not find that value, it returns zero.
**Column E - Approve**
```
=if(
B5="approve";
C5;
if(
B5="transfer_from";
LOOKUP(
A5;
sort(A$2:A4);
sort(E$2:E4;A$2:A4; TRUE)
)-D5;
0
)
)
```
Column E is the current state of the approved tokens at that time.
* If column B shows the *approve* word, then it copies approve value from column C. That's the current approved value, as it was not modified by any other `transfer_from` method.
* If column B shows *transfer_from*, then `LOOKUP` method needs to be used to find the last approved value for the given key (column A). Then it needs to be subtracted by the value of transferred tokens.
* If column B doesn't show anything, column E shows 0, as we can skip this transaction.
**Grid of Allowances**
Now we can create a grid with allowances. Vertically, we write the names of the token owners. Horizontally, we write the names allowed token spenders.
*Image 5: Creation of the grid*
We fill up the cells with the formula, which is described below.
*Image 6: Current state of Allowances*
```
=if(
countif(
$A$2:$A$15;
concat($H4;I$3)
)=0;
0;
lookup(
concat($H4;I$3);
sort($A$2:$A$15);
sort($E$2:$E$15;
$A$2:$A$15;
TRUE)
)
)
```
This formula enables us to see the current state of allowed tokens.
* First *countif* formula checks if a key of `CONCAT(owner, spender)` appears in the transactions. If not it means the owner never approved the spender, so the value should be zero.
* In the opposite situation, the *lookup* formula looks for the key in column A and then it sorts it in ascending order, so it selects the last value from that key, which is the current allowance state.
Sign in with Wallet
Connect another wallet