FAQ Q105: How to use IOTA MAM channels with Google Sheets and Totangle.com

Some interesting 2nd level protocols are being developed on top of the tangle. The one being discussed 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 totangle.com 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 Totangle.com 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.

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.

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

Choose the Google account which has access to your spreadsheet.

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.

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

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.

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).

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.

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

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:

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):

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:

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.

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.

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

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

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.

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.

Back to Frequently Asked Questions