Connecting Google Sheets to Matik provides users with a powerful yet simple way to get data into Matik. You can use your Google sheet as a simple data source and pull data from a single cell or range of cells. You can also enter input values into a sheet and capture any desired output calculations. This allows you to build out complex calculations within your dynamic content. Google Sheets makes it possible for Matik users to create pricing calculators, perform lookups on simple tables that are not in databases, and more.
Creating Google Sheet Dynamic Content
To create dynamic content from a Google Sheet, add the Google Sheet into your data sources if you have not done so. Then, create a new piece of dynamic content. Select "Data-Driven" and select your Google Sheet from the list of data sources. Note that all end users and admins that will use this content should have at least read access to the Google Sheet posted.
The next steps are to 1) Define inputs and 2) Define outputs. Inputs are the data you want to pass through to the Google Sheet. You do not need to add any inputs if you do not want to input any data into your google sheet. If you are not using inputs, skip to the Define Outputs section below.
Matik will place inputs into your Google Sheet at the time of generation and capture the output cells you define. Note that no changes are made to your original data source Google sheet. Consider the following sheet:
Let's imagine I have an input in Matik called "Number_of_Seats". I can assign the "Number_of_Seats" input into cell B2 and set cell B4 as my Sheet output. When a user creates a presentation that contains this dynamic content, they will fill out the "Number_of_Seats" input. That number will be passed into the Pricing calculator Sheet in Cell B2. Matik will then take the result in Cell B4 and insert that into the presentation.
You will walk through this wizard below when configuring your inputs and outputs. Note that a view of your Google Sheet data source will automatically pop up within the dynamic content creation screen:
To tell Matik which cells in your Google Sheet you want your input to go to, click the appropriate field in Matik under “Sheet Inputs," and then select the input cells in the Google Sheet view. For example, if you select cell B3 in the Google Sheet view, Matik will automatically enter this cell reference into the appropriate input field in Matik in the required format (e.g., Sheet1!B3).
If you would like to access other sheets within your Google Sheet data source, select it using the arrows in the bottom left of the sheet preview. Note that the bottom sheet tab will only display the current active sheet and does not display all sheets available.
In the box next to the input cell, enter the input you want to be inserted. You can use Matik inputs by entering
&: followed by the input name—to learn more, go to the Inputs page. You can also enter Dynamic Content as input(s) to a Google Sheet. Simply put the dynamic content name, surrounded by double curly brackets, into the input box.
Outputs are the data you want Matik to pull from the Google Sheet and insert into your finished presentation. Outputs can be a single cell or a range of cells.
To tell Matik which cells in your Google Sheet you want to be outputted into your finished presentation, click again into the "Cells in Sheet" field in Matik under “Sheet Output," then select the output cells in the Google Sheet view. For example, if you select cell D3 in the Google Sheet view, Matik will automatically enter this cell reference into the appropriate output field in Matik in the required format (e.g., Sheet1!D3). You can also add optional column names in the "Comma-separated list of column names" field.
If you would like to access other sheets within your Google Sheet data source, select them using the arrows in the bottom left of the sheet preview. Note that the bottom sheet tab will only display the current active sheet and does not display all sheets available.
Matik will automatically strip leading and trailing whitespace from output cells when inserting values into presentations. E.g. A cell with the value " Example Text " will be inserted into a presentation as "Example Text". If you wish to include whitespace, you can use an alternative whitespace or space character such as Unicode U+00A0 or " ". This can also be done with the
=UNICHAR(160) Google Sheets function.
Remember to test your new piece of content, and then press “Add” to save it to Matik.
Tagging Google Sheet Dynamic Content
Once you have created Dynamic Content, the next step is to identify where you want this dynamic content to appear in your templates with tagging. Navigate to the templates page and select the template you would like to tag dynamic content in. If you do not have a template yet, you can create one using these instructions.
Click on the slide you wish to tag your dynamic content. Within the preview of the slide, you can highlight the text where you want your dynamic content inserted. Click the plus icon that appears near the highlighted text. Next, select your dynamic content and click save.
Tagged Dynamic Content will appear in the left menu when previewing a template and its slides. You can now test your slide to ensure that it was tagged correctly by clicking on the "Test Slide" button above the slide preview and following the on-screen prompts.
For more on tagging, including how to tag graphs, images, and tables, see our Tagging Overview article.
Best Practices and Limitations
Large amounts of data, sheets, media, and complex formulas within your Google Sheets can result in long load times and performance issues with your Google Sheet-based dynamic content. We recommend removing all data and sheets not required for your dynamic content output.
Matik is not capable of tracking changes to columns and cell locations. When updating your Google Sheets, be conscious of the cells Matik uses to input and output data and change any linked Dynamic Content if these cells' locations are altered. We recommend creating a separate tab in your spreadsheet containing all of your input and output cells referenced in Matik so that any changes to your Google Sheet (adding or deleting rows and columns) will not affect the cells referenced in your spreadsheet-based dynamic content.
The IMPORTRANGE() formula is only compatible with Matik Dynamic Content if the referenced sheet is publicly accessible (Share Settings: Anyone with the Link can access). Please consider your organization's data-sharing policies when making any of your spreadsheets public.
Example Dynamic Content from a Google Sheet
The example below uses a Google Sheet as a pricing calculator. Let’s say this piece of content is “total_annual_cost," and you’ve tied an input to the content called “&:consumer_license_count." The end user would have to fill this input out when generating their presentation, and this data would then be passed into the Google Sheet calculator. You would tell Matik to send the value to cell C5 (in this example shown below, the cell with a red border) and then pull the contents of cell F7 as an output into the finished presentation.