Skip to main content

Googlesheet datasource

Googlesheet datasources allow you to fetch and display data from Google Sheets directly in your widgets and templates. The system automatically processes spreadsheet data and makes it available for dynamic content display with real-time updates.

Prerequisites​

Before creating a Googlesheet datasource, ensure you have:

  • A Google account with access to Google Sheets
  • The target Google Sheet with proper sharing permissions
  • Understanding of your spreadsheet structure and data layout
  • Administrative access to configure Google credentials in Wallboard
  • Network connectivity to Google's APIs

Supported Google Sheets Features​

The Googlesheet datasource supports:

Data Types:

  • Text, numbers, dates, and formulas
  • Multiple sheets within a single spreadsheet
  • Dynamic ranges that expand with new data
  • Formatted cells and calculated values

Limitations:

  • Maximum of 10,000 cells per sheet for optimal performance
  • Charts and images are not imported (data only)
  • Conditional formatting is not preserved
  • Complex formulas may show calculated results only

Data Structure Examples:

Simple Table Format:

| Name     | Department | Salary   | Start Date |
|----------|------------|----------|------------|
| John Doe | Sales      | $50,000  | 2023-01-15 |
| Jane Smith| Marketing  | $55,000  | 2023-02-01 |

List Format:

| Product Name | Stock | Price |
|--------------|-------|-------|
| Widget A     | 150   | $25   |
| Widget B     | 89    | $30   |
| Widget C     | 203   | $18   |

Google Credentials Setup​

To create a Googlesheet datasource you need to create and authenticate your Google credential in the Wallboard system.

Creating Google Credentials​

1. Navigate to Credentials
Go to Settings > Cloud Integration > Google credentials

2. Add New Credential
Click Add new and select the Spreadsheet scope from the dropdown

3. Team Assignment (Optional)
You can assign the credential to specific teams if your organization requires access control

4. Authentication Process
After filling the required fields:

  • Click the Sign in with Google button
  • Complete the Google OAuth authentication flow
  • Grant the necessary permissions for spreadsheet access
  • Verify the authentication was successful

Once authenticated, you can create external datasources using the acquired Google Sheets access.

Google Permissions Required

The Wallboard system requires read access to your Google Sheets. During authentication, you'll be prompted to grant:

  • View your Google Sheets
  • Read spreadsheet data and structure

Create Datasource​

Go to Datasources > Click Add new in the External datasource tab > Choose the Googlesheet type.

Basic Configuration​

1. Update Schedule
Decide how your datasource updates from the original spreadsheet:

  • Select a Refresh Frequency for regular intervals (every minute, hour, day, etc.)
  • Specify a Cron Expression for custom scheduling
Example Cron Expressions
  • 0 */15 * * * * - Every 15 minutes
  • 0 0 */2 * * * - Every 2 hours
  • 0 0 8 * * 1-5 - Weekdays at 8:00 AM
Googlesheet Refreshing

For more information about external datasource refresh options, see the External Datasource Refreshing documentation.

2. Source Selection
For Credential, follow these steps:

  • Select the Google credential you created previously
  • Choose the appropriate Folder from your Google Drive
  • Select the target spreadsheet from the available files
  • Choose the specific sheet (tab) within the spreadsheet

Advanced Configuration Options​

3. Spreadsheet Settings
Configure how the system interprets your spreadsheet data:

  • First row is header: Enable this if your first row contains column names that should be used as field identifiers
  • Legacy format: Use older data processing format for compatibility with existing templates
  • Active: Enable/disable the datasource without deleting the configuration
  • Ignore Error counter: Continue operation even if temporary errors occur during data fetching

4. Editor Settings
Configure how the system processes spreadsheet content:

  • Cache external resources: Store external content (images, files) referenced in cells locally for faster access
  • Remove broken external resource references: Automatically clean up invalid links found in spreadsheet data
  • Rotate cache on every update: Clear and rebuild cache with each data refresh to ensure current content
  • Exchange internal resource references: Update internal file paths when content is moved or reorganized
  • Request settings: Configure timeout, retry options, and other request parameters

5. Array Processing
Enable Randomize arrays to shuffle the order of spreadsheet rows each time the datasource updates. This is useful for:

  • Rotating through different data entries
  • Displaying varied information on each refresh
  • Creating dynamic content presentation from static spreadsheet data

Authentication and Access Settings​

The system uses the Google credential you configured earlier to access spreadsheet data. Ensure:

  1. The credential has not expired or been revoked
  2. The target spreadsheet is accessible by the authenticated Google account
  3. Sharing permissions allow the service account to read the data
  4. The spreadsheet URL has not changed if using direct links

6. Save Configuration
Once all settings are configured according to your spreadsheet requirements, click Save to create the datasource.

Spreadsheet Access Verification

After saving, the system will attempt to fetch data from your Google Sheet. Check the datasource status to confirm successful connection and data retrieval.

Using Spreadsheet Data in Widgets​

After creating the Googlesheet datasource, you can bind the spreadsheet data to widgets:

Header Row Access (when "First row is header" is enabled):

  • data.Name, data.Department, data.Salary
  • data[0].Name, data[1].Department for specific rows

Index-Based Access (when headers are not used):

  • data[0][0] for cell A1 content
  • data[1][2] for cell C2 content
  • data.length for total number of rows

Dynamic Data Binding Examples:

  • Employee roster: data[0].Name - data[0].Department
  • Product pricing: data.ProductName: data.Price
  • Inventory levels: Stock remaining: data.Stock

Common Use Cases​

Employee Directories: Staff information, contact details, organizational charts
Product Catalogs: Inventory lists, pricing tables, product specifications
Event Schedules: Meeting rooms, event calendars, resource booking
Financial Reports: Budget tracking, expense reports, KPI dashboards
Content Management: Article lists, publication schedules, editorial calendars
Data Dashboards: Real-time metrics, performance indicators, status reports

Data Formatting

For optimal display, structure your Google Sheets with consistent data types in each column and clear headers. This ensures reliable data binding and consistent widget presentation.