Ultorg supports reading and updating of tabular data in Google Sheets. This enables a range of useful workflows:

• You can create a complete relational database out of Google Sheets worksheets. This lets you use all of Ultorg's functionality, for project management, customer relationship management, inventory tracking, or other tasks, without an external SQL database.
• You can combine data from Google Sheets with data in other data sources.
• You may have tables of data that you share with other users. Some users may be accessing data from Ultorg, and others from the Google Sheets web app.
• You can collect data using Google Forms. Signup forms, order forms, support requests...
• You can integrate with software that has connectors for Google Sheets (e.g. Salesforce).

Your spreadsheet works like a real relational database. You can query your data by creating a perspective, and make data edits that are written back to the sheet. Behind the scenes, Ultorg synchronizes your data to a PostgreSQL database that runs on your local computer.

Spreadsheet Format

To make a Google Sheets spreadsheet work well with Ultorg, organize it as follows:

• Each worksheet (spreadsheet tab) should contain a single table of data.
• The top row in the worksheet, should contain names for each column.

  Alternatively, if you have frozen multiple rows via View→Freeze in Google Sheets, the last frozen row should contain column names.

• Each table column should contain data of one type only: text, numbers, dates, times, timestamps, or true/false. If you are designing a new table, you can provide a row of example data so that Ultorg can detect the column types.

Additionally, if you wish to edit the data from Ultorg, there is one more requirement:

• The leftmost column should contain values that uniquely define each row. These identifiers can be numbers (1, 2, 3, ...) or text (airport codes, JIRA tickets etc.).

  If the name of the first column is ID, and you use numeric identifiers, then Ultorg will assign auto-incremented values when you insert new rows. Prefixed numbers, such as TICKET-00872, can also be auto-incremented.

The requirements above are the same for both Google Sheets and Microsoft Excel.

For examples of properly organized Google Sheets spreadsheets, see the links below. To try data editing with these in Ultorg, first make a copy to your own Google Drive.

• Simple CRM Database: Tables illustrating how Ultorg could be used as a Customer Relationship Management tool, letting you track customers and interactions with each customer (emails, meetings etc.). There is also a table containing data collected via Google Forms, which can be joined to the Customers table via the Email Address field.
• Course Catalog: A database of academic courses, with instructors, meetings, and reading lists. About 12,500 rows across 5 related tables.

Here is a screenshot of a properly formatted spreadsheet, from the first example above:

Designing an Editable Spreadsheet Database

The use of an ID column in each table—a so-called primary key—ensures that data can be edited safely by multiple users at once, even as rows are inserted, deleted, and reordered. Tables without a primary key column can still be used in Ultorg, but are not editable.

In Ultorg, a table's primary key column is indicated by a key icon () in the Fields sidebar:

In a relational database, it is common to have the IDs of one table be referenced by columns in other tables. For instance, a Customer Interactions table might have a column called “Customer ID”, which references the “ID” column of the “Customers” table. This is a powerful concept that allows you to model all kind of business relationships in a standardized way.

You can use Google Sheets (or Excel) to design your tables, and then tell Ultorg about relationships between them using the Custom Join action:

With joins configured between tables, you can tailor Ultorg perspectives for your various business tasks, and use the same perspectives for data entry. For example, you can log a new customer interaction by placing the cursor on a cell under Customer Interactions, next to the desired parent row in Customers, and invoking Insert Record (Ctrl+Shift+Plus):

Fields that are joined against another table's primary key column will provide a dropdown menu when edited (as shown in the demo animation at the top of this page).

Connecting and Refreshing

To connect to a Google Sheets workbook in Ultorg, click Add Data Source→Connect to Google Sheets and follow the instructions.

You will be asked to authenticate with your Google account. You can connect to multiple spreadsheets at a time, as long as they are accessible from the same Google account.

To edit data, you may be asked to authenticate again, as Ultorg will initially request only read access to spreadsheets in your Google Drive.

Refreshing Data

If you change the structure of a database table, such as data types, column names, or the format of the primary key column, click Data→Refresh Table List/Metadata to have Ultorg detect the changes. To refresh data only, use Refresh Data (, Ctrl+R/⌘R).

For convenience, there is also an Open in Google Sheets action in the context menu (see above), which will open the spreadsheet in your default web browser.

Other Details

• The Apply Edits action will perform sanity checks on affected sheets before proceeding to make changes. This includes checking for the continued presence of relevant rows and columns, and preventing duplicate values in the primary key column.
• Ultorg's Google Sheets integration is designed to permit concurrent edits by multiple users, provided that column definitions and primary key values remain stable. Blank or reordered rows may occur in rare cases; this is normal.
• Auto-incremented IDs may skip numbers that were previously assigned to now-deleted rows. This is by design. Very large gaps are avoided.
• Editing of dates, times, and timestamps should work regardless of spreadsheet locale.
• The actual execution of database queries happens on your local computer. Interactions stay fast even on slow Internet connections. You can make queries and stage edits even while temporarily offline.

Data Privacy

Connections to Google Sheets from the Ultorg desktop app are private to you; the desktop app connects directly to Google's servers, without revealing data to Ultorg's developers.

Authentication tokens are stored in the home directory of your local computer, or in your “roaming” home directory in certain corporate environments.