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.
Requirements
- A Google account (a free Gmail account is sufficient).
- 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.
Note: You cannot export selected individual outputs from a page. All visible outputs on a page are exported. If you only want to export specific outputs, you can use Copy > Copy Data or Duplicate the outputs so they exist as standalone outputs in the Report tree.
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
- 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). If asked to name the project, call it 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.
Select: Advanced → Go to Displayr Google Sheets Connector (unsafe) and 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.
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 spreadsheet and refresh the page. A new Displayr menu will appear.
Your spreadsheet is now your template.
Leave this spreadsheet blank.
Each export creates a dated copy of this template, so the template itself is never modified.
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. |
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.
Refreshing an existing export
Once your Google Sheet is connected, you can refresh it whenever your Displayr document changes.
- Update your data in Displayr and/or create new tables.
- Open your connected Google Sheet.
- Select: Displayr > Refresh from Displayr.
The latest data will be imported while preserving your export settings.
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.
That's it, your Google Sheet now stays in sync with Displayr. Whenever your report changes, just select Displayr > Refresh from Displayr in the Google Sheet to bring it up to date.