How to make a Kumu map that anyone with the link can edit

Using Google Sheets, Forms, and custom scripts

I’ve been having a lot of fun using Kumu to map relationships between people and projects in the Enspiral network. Along the way I’ve hacked together a method for making a map that can be edited by anyone, so I wanted to share the technique for other folks to copy.

Note: this post is more technical than usual. It assumes a working knowledge of Kumu, and at least a passing familiarity with custom scripts in Google Sheets.

A snapshot of the Enspiral network map showing connections between people and projects

Why I want a user-editable map

Because the map I’m working on is going to be used by a couple of hundred people, I want it to be effortless for anyone to update it: no user accounts, logins, passwords bleurgh. To be safe, it needs to preserve history: all changes should be reversible. And I want to consume data from lots of different places: surveys, Slack, Loomio, etc.

These design constraints led me to decide on a making a public map integrated with a Google Sheet. To get started, I manually entered some baseline data like names and emails from our org directory. To make it safe and easy for many people to update the map, I’ve linked a Google Form to the Sheet. This means that new data is processed and displayed on the map as soon as the form is submitted, and all data is preserved along the way.

Demo

To illustrate the technique, I’ve made this demo map, which is populated by this Sheet and this Form.

On the screenshot below, the right panel shows the map, and the left panel shows the information about one Element (me). I’m an Element of type Person, with a bunch of attributes: I have Keywords, a Profile, and a final attribute called Update This Person, which links to the form.

Demo map to illustrate the technique

Take a look, update an element, then reload the map to see the new data. To achieve this functionality, I needed three sweet hacks (i.e. cutting-and-pasting other people’s scripts).

1. Auto-populate a Google Form dropdown with formRanger

The first one is super easy, it’s called formRanger. With this little add-on, I can take a column from the Sheet, and use it to populate a dropdown option in the Form. So instead of using a text field and asking people to type their name exactly as it is known in the system, they can just pick their name from a validated list. Every time the master Sheet is updated (like when someone new joins the network), the form is updated too.

Google Form showing formRanger extension

2. Use “Reverse lookup” to find the latest entry for each person

So now I have a Form where users can submit new data. Every time the Form is submitted, it adds a new row to the Sheet. In this example screenshot, you can see what the spreadsheet looks like after I’ve entered data about myself twice:

Example spreadsheet showing form responses

Because I expect people to update the map repeatedly, I want to only send the latest entry over to Kumu. I need to use a lookup function (like VLOOKUP) to get the right data, but because the latest data is at the bottom of the sheet, the function needs to work in reverse. To do this, I found this sweet reverse lookup function from some other helpful Internet user. Given the data in the example spreadsheet, the formula for retrieving the latest Profile value for the Person named “Rich” is:

=index(B:D, max(filter(row(B:B), B:B="Rich")), 2)

3. Pre-fill the form with existing data

Okay so we’re sending the most up-to-date data over to the Kumu map. Next problem: every time you edit the form, you have to start from scratch.

Let’s say I submit the form with:

Name = Rich
Keywords = agile, feminism, writing

Then later on I get inspired and I want to add more information. So I load up the form again and submit:

Name = Rich
Profile = Find out about me on richdecibels.com

But because I’m only sending the most recent entry to Kumu, after I submit the form the second time, the map won’t know about the Keywords I entered the first time round. Boo!

So instead of sending users to a blank form, they should see a form pre-filled with any existing data.

With Google Forms you can generate “pre-filled links” (details here). So if I want someone to visit a form with their name already entered, I can send them to a special URL like: google.com/form/123456?Name=Sally

Okay that sounds easy enough, but what about when the data is complex, containing special characters that will break the URL?

To get around this, we need another custom script. Here’s one I found called encodeURIC, which takes a string and encodes it into URL-friendly characters.

function encodeURIC( r ) {
 if( r.constructor == Array ) {
  var out = r.slice();
  for( i=0; i< r.length; i++){
   for( j=0; j< r[i].length; j++){
    out[i][j] = encodeURIComponent( r[i][j].toString() );
   }
  }
  return out;
 }
 else{
  return encodeURIComponent( r.toString() )
 }
}

If you refer back to the demo image, at the bottom of the list of Element attributes is a link to “Update This Person”. I’ll unfurl that full URL and explain it piece by piece:

https://docs.google.com/forms/d/e/1FAIpQLScbeekhF3-2qRfsRMKAC4BOs4O5jt-65pcxwRM1_6M9XOwLcw/viewform?
&entry.1497981157=Rich
&entry.13419873=This%20is%20my%20latest%20user%20profile
&entry.1428945402=writing%2C%20blogs%2C%20all%2C%20day

The URL starts with the plain link to the form. Then you see “&entry.1497981157=Rich”. “1497981157” is the id of the Name field on my form (see how to find the field id). When you see “%20” and “%2C”, that’s the output of the encoding function, which has replaced the spaces and commas with URL-friendly codes.

Stringing it all together, this is what the Elements sheet looks like:

Columns A and B are manually copied in from our org directory. Columns C and D use the reverse lookup trick to find the latest data on record for each person. Column E then encodes the data in the previous 4 cells to generate the “Update this person” link, like this:

=concatenate("https://docs.google.com/forms/d/e/[[FormId]]/viewform?
&entry.[[NameFieldID]]=", encodeURIC(A2) ,
"&entry.[[ProfileFieldID]]=", encodeURIC(C2),
"&entry.[[KeywordsFieldID]]=", encodeURIC(D2))

So there you have it, a friendly user-editable network map all stuck together with duct tape and recycled code. Play around with it and let me know what you make 🤓

p.s. here’s another quick tip explaining how you can get people’s profile photos into Kumu without revealing email addresses

p.p.s. if you want to support my writing, you can throw some coins in my hat on Patreon 😍