Row Zero makes it easy to build big, connected spreadsheets that auto-update. You can use the built-in data connectors to connect directly to your data source, write a query, and import data to a connected table. You can also share connected spreadsheets and data sources with teammates to give easy access to live, updating data without them needing to write SQL. Everything built on connected tables automatically updates when source data is refreshed, including pivot tables, charts, calculations, etc.
In this post we'll share tips for working with connected tables and automating spreadsheet updates in Row Zero.
Build a connected spreadsheet in Row Zero
How to import connected data
You have two options for importing connected data into your spreadsheet:
Connect to a data source using the built-in connectors, write a query, and import to a connected table in your sheet. Here's instructions for how to create a connected spreadsheet and here's a video tutorial:
Row Zero currently has built-in connectors for Snowflake, Databricks, Redshift, BigQuery, and Postgres.
If a teammate has shared a data source with you, you can simply click a button to import the connected data source. Go to the Data sources section of your account and click on a tile to import to a new workbook.
Here's more information on how to create a shared data source.
Connected Tables 101
Connected data imports into the spreadsheet as a connected table. Connected tables include all of the imported data in a condensed view and can be refreshed to pull in new data. Everything built on connected tables stays in sync and up-to-date including pivot tables, charts, formulas, etc. You can schedule auto-updates or manually re-run for the latest data at any time. Learn more about working with connected tables.
Tips for working with connected tables
1. Right-click to open the table menu
Right-click inside the connected table to open a context menu with options for working with your data. You can edit your data source, manage columns, create a pivot table, cut/copy the table, explode to cells for full editing, and write-back to your data source. You can also double-click on the table to open the editor and re-run your query or schedule auto-updates.
2. Building out a connected spreadsheet
Row Zero works like Excel and Google Sheets - just a lot more powerful and connected to your data. Use the same spreadsheet functions and keyboard shortcuts you're used to. One key advantage is that pivot tables, charts, etc. dynamically update as source data is updated and filtered. Here's a breakdown of connected data features:
- Calculated columns - Add calculated columns by entering a formula in the first column to the right of the data table and referencing a column in the table. The formula will automatically apply to the full column.
- Dynamic pivot tables - Pivot tables dynamically update as source data is updated, filtered, or sorted. Row Zero pivot tables also have several unique features including group by date, count unique, and percentiles. You can easily build on top of pivot tables using your pivot table as source data, add calculated columns, and even create pivot tables from pivot tables.
- Dynamic Charts - Charts also dynamically update as source data is updated, filtered, or sorted.
- Dynamic filter and sort - Connected tables have built-in filter and sort in each column header. When you filter and sort your table, those filters and sorts are applied to pivot tables, charts, and dynamic formulas referencing the data table. When data updates, filters and sorts continue to be applied.
- Slicers - Row Zero slicers are portable, full-featured filters that can be placed anywhere in the workbook and can filter multiple charts and pivot tables at once when they share the same source table.
- Dynamic formulas - Formulas update with source data and you can also use the tilde operator to filter formulas in sync with filters and slicers. You can also leverage the python code window to build custom functions.
These features make it easy to build out connected spreadsheet dashboards, reports, and analysis and collaborate and share with teammates.
3. Updating data and auto-refresh
Double-click on your connected table to open the editor, where you can click ‘Run’ to re-run your query and update your table with the latest data. Everything built on top of your connected table will also update (pivot tables, charts, calculations, etc.). You can set up scheduled refresh to auto-update at a specific time each day by clicking the calendar icon in the editor.
When you click in a connected table, you can see when data was last updated in the bottom right of the workbook, along with the number of rows in the table.
4. Sharing connected data
You have two options for sharing connected data:
Share the spreadsheet - Click the blue share button in the top right of the workbook to share connected spreadsheets with teammates.
Share the data source - Shared data sources give teammates one-click access to run the query in their own workbook and schedule auto-updates. This is a good way to give business users easy access to governed, data warehouse data, while giving them the freedom to build out their own spreadsheets using the data.
5. Exporting spreadsheet data to your data warehouse
You can write-back spreadsheet data to your data warehouse by right-clicking on your table or selected cell range and selecting 'Export to". This creates a new table in your data warehouse and imports all of the data.
6. Explode data to cells for full editing
At any time you can explode your data table to cells, where they are fully editable. However, exploding to cells breaks the connection to your data source, so you won’t be able to further update your data. To explode to cells, right-click on your table and select 'Explode table'.
Conclusion
Connected spreadsheets can significantly improve the efficiency and security of your spreadsheets and ensure you are always working with updated data. Row Zero makes it easy to build connected spreadsheets using our built-in data connectors and/or shared data sources. Row Zero works like Excel and Google Sheets but can handle much larger datasets. Everything built on connected data stays in sync and automatically updates with source data. You can share both spreadsheets and data sources with teammates. To get started, open a new workbook and connect to a data source.
Build a connected spreadsheet in Row Zero
Related Content