Linking Lucidchart Diagrams to Spreadsheet Data

With our data linking capacities, you can link Lucidchart diagrams to live data located in Google Sheets. Your shape data will automatically update with any changes you make to your Sheets, allowing you to visualize your processes in real time!

The data linking feature is only available for Professional, Team, and Enterprise accounts. To upgrade, visit our pricing page or contact our sales team.

  1. Within a Lucidchart document, click File > Import Data > Data Linking (Sheets, Excel, CSV). 

    Screen_Shot_2018-04-17_at_5.36.51_PM.png 

    You can also click Screen_Shot_2018-04-17_at_5.31.21_PM.png in the dock on the bottom right of the canvas, and then click Screen_Shot_2018-04-17_at_5.34.06_PM.png.

    Screen_Shot_2018-04-17_at_5.35.32_PM.png

  2. If you have not already given Lucidchart permission to access your Google Sheets documents, a window will pop up asking you to do so. Click "Authenticate" and then "Allow" in the following window.

    Screen_Shot_2017-11-06_at_11.50.47_AM.png

  3. A window will pop up asking you what kind of spreadsheet you want to import. Select your desired spreadsheet type, and click Screen_Shot_2018-04-17_at_4.59.46_PM.png.

    Screen_Shot_2018-04-17_at_4.58.48_PM.png

    .
  4. Select the file that you would like to link to your Lucidchart document.

    pick_which_sheet_to_link.png

  5. Select which sheets from your selected document you want to link and click Screen_Shot_2018-04-17_at_5.05.09_PM.png.

    Screen_Shot_2018-04-17_at_5.01.10_PM.png

  6. Identify which row in your data set is the header row, or select "My data set doesn't contain a header row." You can identify a header row at any time after importing your data. Click Screen_Shot_2018-04-17_at_5.05.09_PM.png

    Screen_Shot_2018-04-17_at_5.05.01_PM.png

  7. Identify which column in your data set contains unique information. This column will serve as your reference key (see the reference key section below for more information). You can also select "My data set doesn't contain reference keys." You can change this at any time. Click Screen_Shot_2018-04-17_at_5.18.17_PM.png.

    Screen_Shot_2018-04-17_at_5.16.51_PM.png

Your data is now linked to Lucidchart! Your imported data will appear in the bottom right corner of your screen.

Note: Your Lucidchart data set will update periodically to show changes made in Google Sheets. If you don’t see the changes right away, click this button to sync.Screen_Shot_2017-01-18_at_2.00.05_PM.png

To manage your data sets, click on the upside-down triangle next to the name of your active linked sheet and a list of all of your linked sheets will appear.

Screen_Shot_2017-07-07_at_3.53.49_PM.png

Screen_Shot_2017-07-07_at_3.53.57_PM.png

You can easily link a new sheet to your document by clicking the “+ Data Set” button.

If you want to manage a data set that is already linked, right-click on the sheet’s name or click on the three dots that appear when you hover over it. A window will pop up inviting you to refresh, replace, or delete the data set. If you replace your data set with another one, your shape and page data will automatically update with the data from the new sheet.

Screen_Shot_2017-07-07_at_3.54.08_PM.png

Now that your data set is linked to your document, you can assign parts of it to shapes in Lucidchart. There are three different ways you can do this.

Option 1

  1. Drag a new shape onto the canvas OR select the preexisting shape onto which you would like to assign data.
  2. Drag a row or cell from your data set onto the shape OR onto the shape data panel, which will appear above your sheet data when a shape is selected. The dragged data will automatically be assigned to the shape.

    drag_sheet_data_onto_shape_or_into_panel.gif

Option 2

  1. Drag a row or cell from your data set and drop the placeholder shape anywhere on your canvas.
  2. When you release your cursor, a drop-down menu will appear, allowing you to pick between "Standard," "Flowchart," or "Shapes." Select your desired shape, and watch the data display inside of it.

    drag_data_onto_canvas.gif

    Note: The blue brackets that appear around text when you hover over a shape signify that the shape contains dynamic data.

Option 3

  1. Select the shape to which you would like to assign data.
  2. Click the "+" button in the top right corner of your shape data panel to add a new shape data field.
  3. In the "Label" column, type in a title for the field.
  4. In the "Value" column, link the field to a cell in your data set by typing "=" and then the reference to the cell, e.g. "A2" or "B4." Hit your "Enter" key and the reference will be replaced by the value from the referenced cell.

    manually_adding_a_reference.gif

Your data should now be linked to your shape! You will know that the data was successfully assigned to a shape if the data appears in the shape data panel when you select the shape.

After assigning data to a shape, you can save the linked shape to your custom shape library by right-clicking on the shape and then selecting "Add to Custom Shape Library" from the menu that appears. If you have more than one saved library, you can go on to select which one to add your shape to.

Screen_Shot_2017-07-06_at_2.59.43_PM.png

You can also assign data to an entire page of your document. To do so, select a row or cell and drag it up to the data panel.

assign_page_data.gif

Note: If you create a new page within your document, the data will not be assigned to that page unless you repeat this process.

After you have assigned data to a shape, you can call the data, making your shape display it, by using the command {{this.Label}}. For example, if I wanted to call the information found in cell A6 of my data set, I would type {{this.Creature}} into the shape.

While {{this.Label}} returns the value associated with a label, {{this.Label.label}} returns the label. So while {{this.Creature}} returns "Wildcat," {{this.Creature.label}} returns "Creature."

data_wildcat_row.png call_data_not_yet_changed.png creature_wildcat.png

To call data that is assigned to a page of your document, use the commands {{page.Label}} or {{page.Label.label}}. You can type these labels anywhere on your page to reference page data, including within shapes and text boxes.

You can also insert a label or value as text into a selected shape by clicking the "+T" icon that appears next to a label or value when you hover over it in your shape data panel. If you click on this icon when a shape is not selected, a new text box with the corresponding text will appear on your canvas.

add_text_to_shape_or_to_canvas.gif

Note: As mentioned earlier, the blue brackets that appear around text when you hover over a shape signify that the shape contains dynamic data.

To edit the label or value of data in your data panel, simply click on the text and type over it.

edit_shape_data_text.png

To add a new field or delete or move an existing one, right-click on a label or value or click on the three dots that appear when you hover over it. Select your desired action from the window that appears.

shape_data_options__Top_.png

If you try to add data to a shape or page that already has data with the same label names, a dialogue will appear on your screen asking whether you want to replace the existing data with the new data or keep both sets of data assigned to the shape or page. If you choose the latter option, the label names of the new data will each be appended with a “2.”

keep_or_replace.png

If you add fill color to a cell, columnm, or row of data in your linked Google Sheet, the color will automatically display in your Lucidchart data set as well as in the data panel. If you do not see the color right away, click the refresh button to sync.Screen_Shot_2017-01-18_at_2.00.05_PM.png

Screen_Shot_2017-07-06_at_3.47.06_PM.png

Screen_Shot_2017-07-06_at_3.47.21_PM.png

To display the fill color associated with a piece of your data on the canvas, click on the three dots that appear when you hover over a label or value box in the data panel. Scroll to the bottom of the drop-down menu that appears, and click on "Apply Imported Fill Color." Your fill color should appear on the selected shape, or on the background of your canvas if you are working with page data.

add_color_to_shape.gif

When you share a linked diagram with other Lucidchart users, they will have access to the linked data sheet. You can prevent them from having this access by deleting the linked sheet from your document prior to sharing by following the steps above, under the "Manage Linked Sheets" heading. When you do this, your data values and inserted text will be preserved.

To export your linked data as a CSV file, go to “File” and then click “Download As.”

file_download_as.png

In the pop-up window that appears, select “CSV of Shape Data” as your format, and then hit “Download.” The exported data will include shape name, shape library, text fields, and data.

download_CSV.png

After your data is linked to your diagram, follow these steps to create graphs from your linked data.

  1. Open the data panel and click on the graph icon.

    graph_icon.png
  2. In the window that pops up, provide a title for your graph and then select your desired graph type. We currently offer line graphs and vertical bar graphs (column graphs).

    customize_graph_info.png
  3. In the "Value Range" section, enter the range in which your data appears. For example, if you had the following data and wanted to create a bar graph showing the demand for each store, you would enter the range B2:C4.

    example_data_sheet.png

    When you are done inputting your data range, Click "Next."
  4. In the next dialog page, you will be prompted to choose the columns or rows on which you want your x- and y-axis labels to appear. In the example below, to display the store names on the x-axis you would choose Column "A" for the x-axis labels. If you want to display ‘A’ and ‘B’ as your y-axis labels/legend labels for each bar, you would choose Row "1".

    choose_labels_for_your_graph.png

    Add subtitles to your axes (if you'd like) and click "Next".
  5. In the final page of the dialog box, you will be invited to adjust the axes scale and/or color scheme.

    add_scale_and_color_to_your_graph.png

    We will automatically choose a scale for your y-axis that best fits your data. However, if you would like to use a specific scale (say, 0 to 100), then you can enter it in here.

    We have three color themes for you to choose from:

    graph_color_options.png

    Once you’ve selected a color theme for your bars/lines, you can choose the primary color. This will be the color of the first line or bar in your graph.

  6. Click "Finish" and we will produce a graph for you!

    This graph acts just like any other Lucidchart shape. You can rotate it, change its background color, adjust its transparency, etc. Additionally, any changes to your attached data will automatically show up in the graph!
Reference Keys

With reference keys in Lucidchart, you can select a row of your data to function as a unique identifier for the contents of that row. Reference keys allow Lucidchart to keep track of your data even after you make structural changes to it, for instance, if you remove, add, or reorder rows.

Screen_Shot_2018-04-17_at_6.07.35_PM.png

When you import your data into Lucidchart, you will be prompted to select a column of data to serve as the reference key column. You can opt out of selecting one, but please keep in mind that doing so will prevent Lucidchart from keeping track of your data rows if you move them around in your data set.

Screen_Shot_2018-04-17_at_5.16.51_PM.png

After you import your data, your reference key column will be highlighted in grey in your data set.

Screen_Shot_2018-04-17_at_6.42.26_PM.png

At any time, you can make a new column your reference key column by right-clicking on the header cell and selecting “make reference key column.” Similarly, you can remove the reference key label by right-clicking on the header cell of your current reference key column and selecting “remove reference key.”

Screen_Shot_2018-04-17_at_6.14.55_PM.png

When you populate a shape or other aspect of your diagram with a row containing a reference key, that shape will remain tied to the data in that row even if you rearrange the rows of your data set. The reference key data will appear at the top of the data panel.

For instance, if I identify the “Name” column as my reference key column and drag out the “Murphy” row onto a blue rectangle, my data panel will look like this:

Screen_Shot_2018-04-17_at_6.45.06_PM.png

The blue rectangle is tied to “Murphy” and all other cells in that row. If I sort my data or add or delete rows, the blue rectangle will remain tied to “Murphy,” “Doggo,” “Flying,” etc.

If I had not selected a reference key, my data would be tied to a row number instead. The blue rectangle would be tied to row 3 and all other cells in that row. If I were to add, delete, or reorder columns, "Murphy," “Doggo," and “Flying” would remain in row 1 and therefore stay tied to the blue rectangle. However, if I were to add, delete, or reorder rows, the data in row 1 may change, causing the blue rectangle to lose its tie to the “Murphy” row.

Custom Data

If you do not have a data set to link to your diagram, you can create custom data fields directly in the data panel and link that data to shapes or other parts of your diagram. To create custom data fields, select a part of your diagram and click Screen_Shot_2018-04-17_at_7.01.00_PM.png or Screen_Shot_2018-04-17_at_7.01.06_PM.png in the top right of the data panel. Fill out the custom fields, and click Screen_Shot_2018-04-17_at_7.01.06_PM.png for each new field you would like to add. Your custom data will look something like this:

Screen_Shot_2018-04-17_at_7.04.16_PM.png

When you tie custom data to a part of your diagram, Lucidchart will identify that data via its location formula. This means that each field of data is associated with a specific location in your data set (row, column). If you make any structural changes to your data set, by reordering, adding, or deleting rows OR columns, the linked parts of your diagram would not retain their associations.


Related Articles
Conditional Formatting and Icon Sets
Import and Export Your Diagrams
Account Types
Get Started with Lucidchart