The ultimate guide to using Google Sheets with Kumu (2024)

When it comes to importing data into Kumu we like to think of Google Sheets as a secret superpower. Whether you’re running a survey, crowdsourcing data, or want to connect Kumu to a third-party app, the Google Sheets integration has your back!

Nonetheless, we noticed that some users struggle to get the most out of this feature. So we wrote this guide to take you through all the ways to use Google Sheets. Buckle up as we delve into how to use this format as your ultimate powerhouse for importing data into Kumu maps, unlocking a world of mapping possibilities.

Kumu offers three different formats for importing data: Excel, Google Sheets, and JSON. In this blog, we’ll only cover importing and maintaining data with Google Sheets. If you want to learn more about importing through the other two options (Excel or JSON), please visit our Import docs here.

If you’re already familiar with Kumu and/or Google Sheets, you can skip the basics and jump straight to the sections below. If not, let’s make sure we walk before we crawl, so keep on reading!

Skip to section:

• Create an external integration into Kumu
• Crowdsource data and feed into Kumu
• Visualize survey results in Kumu
• Show event attendance in real-time with Kumu
• Build a hive plot in Kumu (no code required!)
• Use custom formulas to structure data for Kumu

⚠️ Caveats:

Before we dive into all the ways Google Sheets can be used to connect Kumu, it’s important to remind you of a few important caveats:

• Elements and connections are read-only within Kumu — all edits to the underlying data must be made within the spreadsheet. You can’t make edits in Table either.
• Google Sheets provides a one-way synchronization — meaning that Kumu pulls data from Google Sheets into the Kumu project, but can’t send updated information back from Kumu to the Google Sheets (or any third-party platforms connected to that Google Sheet).
• Some Kumu features (such as pinning elements in place, popovers, and direct decorations) don’t work for maps connected to a Google Sheet.

Now let’s get into it!

Importing through Google Sheets is pretty straightforward. You just need to make sure that Kumu can read your data. For that to happen, the data must be structured in a specific way. We’ll show you the steps below!

1. Structuring your data for Kumu to understand

Kumu recognizes two entities that are the building blocks of each map: elements (circles) and connections (lines). When importing from a spreadsheet, Kumu searches for tabs that contain the information for these elements and connections. Usually, you’ll have one tab for elements, and one tab for connections. However, you can also have only an element tab, or only a connections tab.

A) Elements
First, let’s create a new blank Google Sheet and call that tab Elements. Here we’ll structure the data for Kumu. First, write the word Label in the first row of the first column (cell A1). Every subsequent row will create one element, and this column A will contain the unique label (name) for each element. Any data in the subsequent columns of the same row will be an additional data field added to that element’s profile. Kumu has some default fields (such as description, type, tags) that you can use, or you can create your own customized fields. Create your columns first and then fill out all your data in the rows.

C) Connections
Next, if you’re including connections between elements in your data, create another tab in the same file called Connections. If your data doesn’t contain information about relationships between data points, then you can skip this step. Write the word From in cell A1, and To in cell B1. Again, these two columns are mandatory for Kumu to understand which two elements it’s connecting. Any column after that can be customized to reflect any information you’d like to include in that connection’s profile. Once the columns are done, you can start filling out your data.

Finding it difficult to start a Google Sheet from scratch? Here’s a handy workaround! Simply create a blank Kumu project, then add a few elements and connections + their profile information with the green + button at the bottom of your map, and finally click Export > Export to XLS in the bottom-right corner to export your map to Excel. You’ll now have an example sheet tailored to your specific needs, that you can import directly into a Google Sheet.

2. Connecting your Google Sheet to Kumu

Once your formulas are set up and your tabs look like something that Kumu will understand, the hardest part is behind you and we can now import data into Kumu. Connecting a Google Sheet is easy-peasy when following these steps:

1. Click on “Share” in the top-right corner of your Google Sheet. Make sure to verify below the “General access” section whether your file is private (set to “restricted”), or public (set to “Anyone with the link”), and make a change if necessary. Then click “Copy link”
2. Open your Kumu project, click the green + button at the bottom of the screen, and choose “Import” from the popup
3. Paste the shareable link in the correct box below the text “Link map to public / private Google Sheet”
4. Click “Import spreadsheet”
5. Wait for Kumu to review your data, and then click Save Import
6. Voilà, your data will appear!

If Kumu is showing an error, or keeps loading indefinitely, then it wasn’t able to read your data correctly. Read the suggestions that Kumu gives to fix it, or read some common pitfalls here.

3. Adding, updating & editing data

Now that your Google Sheet and Kumu are connected, you can add, update, or delete any data directly in the sheet, and Kumu will reflect this change as soon as you refresh the page.

If your Google Sheets is fed by another external platform, then make your edits directly on that platform. If you’re crowdsourcing data, make sure that no one with access to your sheet rearranges column A of your Elements sheet, or column A and B of your Connections sheet, because that will lead to Kumu throwing an error.

Note: As soon as you unlink your Google Sheet from your Kumu project, the data in Kumu will disappear. You can unlink a Google Sheet by clicking on the green + button at the bottom of your project, choose Import, and click the Unlink Map button. Unlinked by accident? No worries! Repeat the directions in step 3 to bring your data back and it’ll look the same as before it was unlinked.

4. Create your Kumu map

Once the data is coming into Kumu automatically, you can start working on the visualization. Find a handy guide to get started here. Note that any visualization rules stay in Kumu only. This means that even if you unlink a map and then re-link it again, your map will look exactly the same. Similarly, any new data that is added later on will automatically take on the decoration rules that you’ve set in Kumu.

Amazing! You’ve learned the basics of importing through Google Sheets. Let’s now take it up a notch and look into some more advanced integrations you can create with this setup.

We often get the question whether we have a way to create an integration with external platforms, such as Airtable or Salesforce. Although the short answer is “no, we don’t have a public API”, all hope is not lost! There is a way to connect other platforms to Kumu through the use of Google Sheets. We’ll explain how to do this here.

1. Connect the platform to Google Sheets

It’s important that whatever platform you’re using allows you to export data into Google Sheets in real-time, or at least at regular intervals. Some platforms make it easy to export your data into Google Sheets through a simple add-on, while others require you to connect through Zapier.

Here are a few indicators for the most-requested integrations:

• Coupler for Airtable
• Data connector for Salesforce
• Tweet Archiver for Twitter
• Supermetrics for any marketing data (includes Linkedin!)

2. Structure the data for Kumu to understand

Now that you have your data going into Google Sheets automatically, it’s time to structure that data for Kumu to understand. Just having the raw data file isn’t enough, because Kumu can’t read it yet and will throw an error upon import. Make sure to follow the below instructions to structure your data:

A) Ignore raw data
You’ll likely have one or more tab(s) with raw data coming from your external database. The tab with raw data will not be structured yet, so we want to make sure that Kumu ignores that tab during import. To do this, simply add the text (ignore) to each tab that you don’t want Kumu to look at, e.g.: Sheet 1 (ignore).

B) Elements
Now, let’s create a new blank tab in the same file called Elements. Here we’ll re-structure the data for Kumu to understand, according to the rules we explained in the basics of importing through Google Sheets. First, set up the columns by writing the word Label in the first row of the first column (cell A1), and any other value in the subsequent columns. Remember that any data in the subsequent columns of the same row, will be an additional data field added to that element’s profile.

C) Connections
Next, if you’re including connections between elements in your raw data, create another tab in the same file called Connections. If your data doesn’t contain information about relationships between data points, then you can skip this step. Set up your columns by writing the word From in cell A1, and To in cell B1. Again, these two columns are mandatory for Kumu to understand which two elements it’s connecting. Any column after that can be customized to reflect any information you’d like to include in that connection’s profile.

D) Formulas & References
This is probably the trickiest part of importing data through Google Sheets, depending on how the raw data is structured. To make sure that your data automatically gets structured for Kumu to understand, we’re going to use references and formulas between your raw data tab, and your elements and connection tabs. Ready, set, go!

First, it’s important to determine what you want the elements in your Kumu map to represent. Usually, elements represent people, organizations, or projects, but they can also be tweets, events, tools, process steps. The possibilities are endless! Whatever you choose, go to cell A2 of your Elements sheet and reference a cell in your raw data tab. This is done by typing = in the cell, then navigating to your raw data tab, clicking on the cell you’d like to reference, and then hitting Enter on your keyboard. The reference will look something like this: =’(ignore) raw data’!A1.

Once the reference is part of the cell, click + hold the dot in the bottom-right corner of the cell, and drag it down as many rows as you need. This copies the reference to other cells, effectively creating all your elements automatically. It’s important here to anticipate future data being added, so make sure that you drag it down as far as you think the data will go. Repeat the above steps for all the columns and rows in your Elements sheet.

Wondering how much data Kumu can handle? Read about it here.

Does your data contain relationships between people or organizations in an adjacency matrix? Jump to this section on custom Kumu formulas.

If you have a knack for Google Sheets, or you simply love diving into formulas, you can also run some queries to get your data structure. This deep-dive is outside of the scope of this blog, but you can find more information here.

E) Dividing or merging cell contents

Perhaps your raw data contains values in the same cell that you wish to spread out over multiple cells. To not go too far down this rabbit hole, I’m going to refer to this guide here, which explains how to split your cells. Vice versa, you can also merge data from multiple cells. Find out how to do that through this guide here.

F) Commas & pipebars

Sometimes the format of data needs to be adjusted for Kumu to understand. One example is where commas ,or semi-colons ;are used to separate values, while Kumu uses pipebars |to separate values. It’s useful here to do a quick Find & Replace of those values. To do so, click the “Edit” tab in your Google Sheet, and choose “Find and Replace” from the menu. In the menu that pops up, indicate that any , should be replaced with a | in the range of one or specific columns (be careful with doing this for your entire sheet, because some columns actually do make use of commas, such as Description!), and then click “Replace All”.

You’re probably thinking right now “Ok, cool but how do I do this automatically?!”. If you’re willing to get into the weeds of Google Sheet formulas, you can find the syntax to automatically replace values here.

Finally, Google Sheets is incredibly powerful and lots can be automated. Much more than I’d be able to explain here. If you’re interested to learn more, make sure to Google Search certain functionalities or explore how to set up advanced macros.

Once the data is structured for Kumu to understand, import your data into Kumu as explained in steps 2–4 of the section above.

Google Sheets allows you to crowdsource data from a wider audience, without having to give everyone access to the Kumu map. Simply set up the Google Sheet with a public link and ask people to fill out their information.

An important thing to note here is that the data in the Google Sheet should always remain structured in the way that Kumu understands. If Kumu can’t read the data, it’ll throw an error. Here’s three options to make sure that others don’t mess with your data:

• Be very clear in your instructions and tell editors not to move column A of the Elements sheet and/or column A and B of the Connections sheet.
• Have people fill out data in one tab (raw data) and then automatically re-structure the data in another tab, as described in step 2a-2d. (An added bonus is that Kumu also reads hidden or protected tabs in a sheet, so you can even hide/protect the structured sheet, to make sure no one alters the structure for Kumu.)
• ‘Lock’ some columns, so they can’t be edited by anyone else than you, or whomever you explicitly give edit permissions. Learn how to protect cell data here.

If someone does accidentally make changes to the structured data, Kumu will no longer be able to read the Google Sheet and throw an error when loading. Don’t fret! This is easy to fix. Simply re-structure the sheet and refresh the Kumu page to bring the data back.

Once you have your Google Sheet set up to send out to the crowd, steps 2–5 are the same as indicated above, so navigate to that section to learn how to import it into Kumu.

Want to take crowdsourcing with Kumu to the next level? Take a look at this blog here by one of our users!

Similarly to how you can crowdsource data, you can also send out a survey and have it populate the answers in a Kumu map in real-time.

Preferably, the software you’d like to use can export data into a Google Sheet directly, automatically and continuously. For example, Google Forms does this. If it’s not possible to export into Google Sheets, then you can try to connect the external tool to Google Sheet through a third-party. Here are some suggestions for the most-used survey tools:

• The SurveyMonkey add-on for Google Sheets
• The native Typeform + Google Sheets integration
• Integrate JotForm with Google Sheets

Whichever one you pick, make sure that survey results are added to a designated Google Sheet doc on a continuous basis, and you’ll be all set.

Once you have your survey set up, steps 2–5 are the same as indicated above, so navigate to that section to learn how to structure your data and import it into Kumu.

Show event attendance in real-time with Kumu

A hive plot is a specific visualization method for drawing networks. They essentially untangle very dense “hairballs” of network data by placing the elements on axes and drawing curved connections that bend outward from the center of the map.

Hive plots are great for allowing map readers to compare different networks more easily, regardless of the networks’ size and complexity. And as an added bonus, they just look really cool! Luckily they are easier to create than they look, because Kumu does all the heavy lifting of visualizing the data.

Interested in building your own hive plot? Learn more in this blog.

We know that structuring data for Kumu to understand is not always as straightforward, or can be quite manual work. Especially when you’re working with an adjacency matrix that indicates relationships (see visual below), or need to write out labels for a list widget to work.

Luckily one of our Kumu advisors Alex Vipond created a Google Sheet with multiple custom Kumu formulas that make data structuring easier.

Find the handy tool here.

The sheet contains one tab for each of the following converters:

• Use KUMU_CONNECTION_LIST to convert your adjacency matrix into a list of connections (see visual below)
• Use KUMU_CLUSTER to turn elements with certain field data into clusters (elements that are connected by the same common field)
• Use KUMU_DEGREE to calculate how many connections an element has, and what their weight is
• Use KUMU_BRIDGE to find common elements that connect two other elements
• Use KUMU_COLLAPSE to collapse multiple connections between the same elements into one weighted connection
• Use KUMU_LIST_WIDGET_ELEMENT_CONNECTIONS to automatically write a connection widget based on each individual label (widgets allow you to easily create lists of elements and/or connections in a map description. Click here to learn more.)

One important thing to note is that these custom formulas only exist in this specific sheet. When you’d like to use them for your data, make sure to create a copy of the sheet and add/delete any tabs as necessary. You can also import any custom formulas into your own Google Sheet. To do so, make sure to visit this guide and scroll down to the “Import Named Functions Into Other Workbooks” section.

Looking for a visual example for the matrix expanders?
Check out this Quick Tip video.