Reading from and writing to IOTA MAM channel using Google Sheets and

in iota •  last year 

IOTA is a revolutionary technology which will be one of the pillars of the dawn of IoT. Many market leaders are already working on implementations based on the IOTA DLT. This is only the beginning, though, and in the next few years we should see thousands of new projects being developed. One of the most important advantages of IOTA is that it solves the scaling problem of the blockchain, it does not become slower with more transactions. Another one is that the transactions are completely free and because of this it's sensible to not only send micropayments but also data transactions with no value at all. IOTA is the only DLT which makes it possible.

Some interesting 2nd level protocols are being developed on top of the tangle. The one I'd like to discuss here is the MAM channel. It has many interesting properties and is meant to create a data channel (a stream of data transactions) where a publisher can share their data points with the listeners. It's a much better solution than simply sending data transactions to a single address as this protocol takes care of things such as data integrity, guarantee of correct ordering, and data encryption. It's not possible for a 3rd party (including the listeners who have read access to the channels) to interfere with the messages, e.g. by spamming the channel. There are 3 privacy modes: public, restricted and private. The public mode is used for data which can be publically viewable by anyone, the private mode is obviously private (not for external listeners) and the restricted mode allows anyone with the authentication key to listen to the stream. The authentication key can be changed at any time by the sender, revoking the access to the data to any party which doesn't have the new key.

The MAM channels have a huge usage potential, also for small research projects, but the barriers to entry for small projects can be somewhat high: access to a stable IOTA node is needed (own node is by far the best solution) and a developer who will do the implementation. This is what is aiming to solve: making it easy to interact with the tangle, including MAM channels, for non-developers using tools such as Google Sheets (or DynamoDB from AWS), or for developers who want to quickly create a working prototype.

In this tutorial we'll create a "writer" Google sheet from which data will be sent to a MAM channel and another "reader" sheet where the data points from our MAM channel will automatically appear as soon as it's published. In order to continue, we need to create a Google account and a account.

Writer spreadsheet

Now let's create the "writer" spreadsheet from which we'll publish the weather data simply by adding new data rows. The columns can contain any data and as long as you stay under 26 columns (A..Z) and you use the default sheet. Your column labels must also start with row number 1 and data rows must follow immediately in the lower rows.

Screen Shot 2018-05-30 at 09.55.25.png

The last column, here called "Transaction Status" is necessary so that Totangle will know when a data row is ready to be sent to the MAM channel (you wouldn't want to send a half-filled row to be sent). You'll need to place the text send or ok in this column to indicate this. The column also gives feedback about the synchronization status, when the data has been sent to the MAM channel, the last column will say sent ABC where ABC is the unique transaction ID in Totangle.

Once we have the first few data rows ready to be sent, let's go to Totangle and configure the MAM channel. Totangle configuration involves creating one or more rules which define the interaction between your cloud and the tangle. Each rule has a trigger and an action and works based on the idea "if this then that": if the trigger is fired, the action will be performed. Totangle always has the Tangle on one side of a rule, either as a trigger or as an action. In case of our writer, the trigger will come from a new Google Sheets row and the action will be a new data point published to a MAM channel.

In the Totangle backend, on blue menu on the left, go to Rules and add a new rule. Choose Google Sheets as the trigger.

Screen Shot 2018-05-30 at 09.56.59.png

Then, click the blue button to allow Totangle to access your Google account.

Screen Shot 2018-05-30 at 10.25.48.png

Choose the Google account which has access to your spreadsheet.

Screen Shot 2018-05-30 at 09.57.53.png

The next screen will tell you which access type is requested. The View metadata access is needed so that we can fetch the list of your Google Sheet spreadsheets and the View and manage spreadsheets access is needed to read from and write to your chosen spreadsheet.

Screen Shot 2018-05-30 at 10.20.03.png

Now, click once again on the Google Sheets trigger in the rule configuration:

Screen Shot 2018-05-30 at 10.20.34.png

Now, select the spreadsheet document to be used. When you do this, you'll see the list of columns read. Indicate which column is the status column, in the case of this tutorial, it's called Transaction status.

Screen Shot 2018-05-30 at 10.20.53.png

Submit the configuration screen, then skip the second step of the configuration (filters) and in the third step select the IOTA MAM Channel action. This will open the IOTA MAM Channel configuration screen. Privacy mode should be sent to the desired value, here we'll use a private channel. Note that in case of a restricted channel, you will also need to specify an authorization key (use only characters A..Z and the number 9 when creating the key). Transaction type should be set to Data transaction (JSON) so that each column is sent separately in the JSON format. You wall also need to name the JSON fields sent, they can have the same names as the spreadsheet columns (Totangle doesn't enforce the same naming so you can have more flexibility in choosing the format).

Screen Shot 2018-05-30 at 10.21.17.png

In the fourth step, you need to map the trigger fields (columns in the spreadsheet) to the JSON fields which will be sent in the data transaction on the tangle. Use the tags $(...) which will be replaced with the spreadsheet data (there are also other available tags other information, such as current timestamp or the Totangle transaction ID). You can also specify any other text you wish or put multiple tags in a single field.

Screen Shot 2018-05-30 at 10.21.38.png

Now activate the rule and allow Totangle a few minutes to export your data rows to the MAM channel.

Screen Shot 2018-05-30 at 10.22.02.png

You will see this is done by either watching the transaction log in Totangle or by opening the spreadsheet and seeing the entries send change to sent followed by the Totangle transaction ID:

Screen Shot 2018-05-30 at 10.22.21.png

Now go back to the rule configuration screen and in step 5 you will now see the MAM channel ID (note that the channel ID is displayed only after the rule was used for the first time to create the channel):

Screen Shot 2018-05-30 at 10.22.40.png

This is it. The MAM channel is now configured and you can easily send new data to it. Totangle allows you to create the data in other ways as well, in practice you will usually want to use an HTTP POST trigger to send this data from the actual source in an automated way.

Note that you will find a very detailed log of each transaction in the Totangle backend:

Screen Shot 2018-05-30 at 10.23.02.png

Reader spreadsheet

Although in case of the writer it may not make much sense to use a spreadsheet, there are many cases where it makes a lot of sense for the reader. You can process this data in a meaningful way, create statistics etc. In case of large volumes, you may want to use the AWS DynamoDB action, or the HTTP POST action which will send webhooks to a script on your server whenever a new data record is available.

First, let's create the reader spreadsheet - it can include all or some of the columns from the writer spreadsheet. Note that no status column is needed in this case.

Screen Shot 2018-05-30 at 10.23.25.png

Now, let's create a new rule in the Totangle backend. Choose IOTA MAM Channel as your trigger. You must use the same privacy mode as in the other rule (private) and you must insert the same MAM channel ID as the one created by the previous rule. Since the rules are completely independent of each other, you need to specify the JSON fields which will be imported from the data records. You must use the same names as in the other rule, here: Temperature, Humidity, Pressure.

Screen Shot 2018-05-30 at 10.23.41.png

In step 3 of the new rule's configuration screen choose Google Sheets and select the reader spreadsheet.

Screen Shot 2018-05-30 at 10.24.00.png

As with the previous rule, in step 4 you need to map the trigger fields to the action counterparts.

Screen Shot 2018-05-30 at 10.24.16.png

Click Activate to activate the new rule. After a few minutes you should see all records from the writer spreadsheet appear in your reader spreadsheet.

Screen Shot 2018-05-30 at 10.39.09.png

Note that Totangle will always add new rows to the bottom of the sheet, so if want to manually insert some cells in the reader spreadsheet (e.g. for your statistics or other calculations) you should do this using other columns or best using another sheet.

Now that the reader spreadsheet is connected to the writer spreadsheet, you can of course keep adding new rows at any time, they will automatically appear in the reader spreadsheet as long as both of your Totangle rules are active.

Hopefully this tutorial will give you an opportunity to experiment with MAM channels and make MAM channels more accessible.

You will find detailed information about MAM on the official IOTA blog at

There's also an excellent video by at

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

You have a minor misspelling in the following sentence:

The public mode is used for data which can be publically viewable by anyone, the private mode is obviously private (not for external listeners) and the restricted mode allows anyone with the authentication key to listen to the stream.
It should be publicly instead of publically.

You have a minor misspelling in the following sentence:

The public mode is used for data which can be publically viewable by anyone, the private mode is obviously private (not for external listeners) and the restricted mode allows anyone with the authentication key to listen to the stream.
It should be publicly instead of publically.

Congratulations @zwieciu! You received a personal award!

1 Year on Steemit

Click here to view your Board

Support SteemitBoard's project! Vote for its witness and get one more award!