The Displayr Google Sheets Connector lets you export tables directly from a Displayr document into Google Sheets while preserving:
- banners
- significance indicators
- NET and SUM rows
- footers
- table of contents
- statistic formatting
Unlike exporting to Excel or PDF, the Google Sheet remains connected to the Displayr document. When your Displayr report changes, you can refresh the Google Sheet with a single click.
This article contains the following sections:
- Step 1: Create your Google Sheets template
- Step 2: Add the Displayr QScript
- Step 3: Run your first export
- Step 4: Configure the export
- Refresh an existing export
- Additional Notes
Requirements
- A Google account (a free Gmail account is sufficient).
- An Enterprise-level Displayr account.
- A Displayr document with at least one table.
- Tables to be exported must not be hidden.
- Download the Displayr Google Sheets Connector.zip file and extract its contents. The ZIP file contains both the Google Apps Script and the Displayr QScript.
Step 1: Create your Google Sheets template
You only need to complete this section once for each Google account.
1. Create a blank spreadsheet
- Open Google Drive.
- In Google Drive, click + New > Google Sheets > Blank spreadsheet (or press Alt + C, then S).
- Rename the new spreadsheet Displayr Google Sheets Connector and press Enter.
2. Install the Apps Script
- In the toolbar, select Extensions > Apps Script.
- Select all the existing code in the editor (Ctrl+A / Cmd+A) and delete it.
- Open Displayr Google Sheets Connector (Google Apps Script).gs from the ZIP file in a text editor, such as Visual Studio Code or Notepad.
- Copy the contents of the file and paste them into the Apps Script editor.
- Click the save icon (or Ctrl+S / Cmd+S).
- Name the project Displayr Google Sheets Connector.
3. Authorize the Apps Script
-
In the toolbar, select onOpen from the function.
- Click Run.
- Review and accept the permissions.
If Google displays Google hasn't verified this app, this is expected. Click on Advanced > Go to Displayr Google Sheets Connector (unsafe) and continue.
The next window titled Displayr Google Sheets Connector wants access to your Google Account. Check the box next to Select all, scroll to the end, and click on Continue
- The Google Sheets Apps Script is now authorized. This authorization only needs to be completed once for each Google account.
- Close the Apps Script editor and return to your spreadsheet.
A note about Company Google Workspace accounts:
Some organizations block unverified Apps Scripts. If this happens, ask your Google Workspace administrator to approve the script.
4. Finish setup
Return to your Google Sheet and refresh the page. A new Displayr menu will appear.
This spreadsheet is now your template. Copy and save the URL, as you'll need it later when configuring the Displayr QScript.
Leave this template blank. Displayr uses it as the master template and does not write data directly to it. Instead, each export creates a new dated copy of the template containing your exported tables. This ensures your template always remains unchanged and is ready to use for future exports.
Step 2: Add the Displayr QScript
Open the Displayr document you want to export.
Create a new QScript by hovering in the Report tree and selecting + > Calculation > QScript > New.
Open the QScript included in the ZIP file using a text editor, such as Visual Studio Code or Notepad.
Copy and paste the entire file into the QScript editor.
-
Locate:
var MY_TEMPLATE_SHEET = ""; -
Copy the full URL of your Google Sheets template and paste it into the quotation marks.
For example:var MY_TEMPLATE_SHEET = "https://docs.google.com/..."This only needs to be configured once unless you delete or replace your template.
Click Save.
Give the QScript a name of: Displayr Google Sheets Connector.
Click Close.
Step 3: Run your first export
Select the pages and/or standalone outputs you want to export.
Run the QScript by hovering in the Report tree and selecting + > Calculation > QScript > Displayr Google Sheets Connector.
When the script finishes:
- Click OK.
- Go to the Exports page in the Report tree. The Exports page contains the links used to create and connect Google Sheets for this export.
- Click Click here to make a copy.
- Select Make a copy.
This creates a new blank Google Sheet connected to your Displayr document. You will populate it in the next step.
Your original template remains unchanged and can be reused for future exports.
Step 4: Configure the export
Wait a few seconds for the Displayr menu to appear in the toolbar.
Before importing your tables, configure any options you want from the Displayr menu.
| Setting | What it does |
|---|---|
| Export layout | Choose how tables are arranged in Google Sheets: create one worksheet for each table, combine all tables onto a single worksheet, or create one worksheet for each folder (grouped by the page each table belongs to in Displayr). Combining tables onto a single worksheet is generally faster for large exports. |
| Table spacing | Specify the spacing between tables on the same worksheet. You can define the number of rows between the bottom of one table and the top of the next, or use a fixed number of rows between the start of each table. |
| Ignore NET and SUM | Exclude NET and SUM rows and columns from the export. |
| Include table of contents | Add a worksheet with links to each exported table, so you can quickly navigate the workbook. |
| Repeat headings | When multiple tables are exported to the same worksheet, choose whether each table repeats its column headings or only the first table includes them. |
| Include significance indicators | Include the significance results shown in Displayr. Tables using significance arrows will export arrows, while tables using column comparisons will export comparison letters. |
| Remove sheets for tables no longer selected | Enabled by default. If you re-run the export with a smaller table selection, sheets for tables that are no longer included are removed. Turn this off if you want to add a different table selection to your existing worksheet instead of replacing it. |
| Enable automatic refresh | Automatically refreshes the spreadsheet from Displayr in the background, without opening it or clicking Refresh. The spreadsheet must already be connected (refreshed manually at least once) before this can be turned on. |
| Change refresh frequency | Sets how often automatic refresh runs: every few minutes, hours, or days. Defaults to once a day. |
These settings are saved with the spreadsheet, so you normally only need to configure them once.
Select Displayr > Refresh from Displayr to import your tables. You may need to reauthorize the script as in 3. Authorize the Apps Script.
Once the refresh finishes, your Google Sheet will be populated with the exported tables.
Refresh an existing export
Once your Google Sheet is connected, you can refresh it whenever your Displayr document changes.
Refresh with updated data
Use this option when you have an updated data set.
- Update your data in Displayr.
- Open your connected Google Sheet that was exported in Step 4: Configure the export.
- Select: Displayr > Refresh from Displayr.
Alternatively, once a spreadsheet has been refreshed manually at least once, you can turn on Displayr > Enable automatic refresh so the spreadsheet updates itself on a schedule, without needing to open it or click Refresh each time. The very first refresh always has to be done manually — a background refresh can't complete Google's one-time authorization on its own.
Refresh with different tables/standalone outputs selected
If you added new tables/standalone outputs or want to change which tables/standalone outputs are exported, repeat the steps in Step 3: Run your first export and Step 4: Configure the export.
Additional Notes
- Apps Script authorization is only required once per Google account.
- Connected spreadsheets do not need to be reconnected after each refresh.
- If a spreadsheet has not been refreshed for more than seven days, you'll be prompted to refresh it when it opens.
- Large exports may take several minutes to complete because each table is generated and formatted individually before being written to Google Sheets.
- If a connected spreadsheet shows "This sheet's Displayr link is out of date," open the Exports page in Displayr and copy the link shown under Reconnecting an existing sheet, then paste it into Displayr > Change Displayr link in that spreadsheet.
That's it - your Google Sheet now stays in sync with Displayr. Whenever your report changes, select Displayr > Refresh from Displayr in the Google Sheet to bring it up to date - or let it happen automatically on a schedule with Displayr > Enable automatic refresh.