Connecting a Sheet to a Data Source

Spreadsheet Connectors allows you to pull information directly from an external database and update a sheet directly from the data source with one click. If you have Spreadsheets and Controls Management, this feature is ready for you to use.

To access the Connectors feature, open the spreadsheet you want to connect to a data source, and click the Data tab.

Protecting Formulas and Data

Before you connect to a data source, lock cells in your sheet to prevent formulas or data from being overwritten when importing from the data source.

For example, if your sheet has column E locked and the data source also has a column E, that locked column E remains unchanged in your sheet. Data from the source in that location will not be imported.

To lock the cells, open the sheet you want to connect to a data source, select the cells you want to protect, and click Lock in the toolbar.

image alt text

To see which cells are locked, click the View tab and click Overlay. Locked cells are shaded grey, and unlocked cells are shaded blue. An indicator will also appear in the bottom left to inform you that an overlay is currently enabled.

image alt text

Connecting to a Data Source

You can connect a sheet in a spreadsheet to a single data source. A spreadsheet document can contain many connected sheets, each connected to the same or different data sources.

From the list of sheets in the document outline, select the sheet you want to connect to a data source. Click Connect in the toolbar and select the data source you want to connect to.

image alt text

In the Choose Wdesk Database Data Set dialog box, find and select the data set you want to connect and click Next to import it. If you have any locked cells, the corresponding data is not imported.

NOTE: The amount of time it takes to import your data can vary based on the performance of the data source. You can continue to use Wdesk while your import is in progress.

image alt text

After the import process is completed, icons appear next to each sheet that is connected with a data source. Once the sheet is connected to a data source, you can build views and reports, add cross-sheet formulas, and perform lookups.

image alt text

Linking

As a best practice, avoid directly linking from a connected sheet to other documents. Links are based on cell coordinates, and this means that changes to the structure of the data source may change which data occupies a specific coordinate. This may cause your link to reference an incorrect value.

Updating Your Data

When there is new data, you will want to update the sheet to make sure you have the most current data from the data source.

Select the sheet you want to update from the left navigation panel and click Update in the toolbar. This updates only the sheet you selected. If you have multiple sheets and documents, you need to update each sheet individually.

A status message appears in the lower right corner of Wdesk letting you know which data source and data set is serving as the source for the update

image alt text

Another message appears in the lower-right corner when the update is complete.

NOTE: You cannot undo data updates or imports, but previous revisions are available in the History tab if you need to view an older version of your document.

Viewing Properties and History

You can view the Properties tab in the right-hand panel to see additional information about the sheet connection and the History tab in the right-hand panel to view revision history and authorship.

Properties

View Properties by clicking the gear icon on the right to open the Properties panel. The information in this panel shows which data set the sheet is connected to, when the last update took place and who did it, and any customization which is currently applied.

image alt text

Revision History

View revision history by clicking the clock icon in the right panel. In the History panel, you can see a list of all revisions and who made the changes. For more information on properties and history, see the Collaboration in Spreadsheets article.

image alt text

Disconnecting from a Data Source

You can disconnect a sheet from a data source any time. Right-click the connected icon for the sheet you want to disconnect and click Disconnect Data Set.

image alt text

Customizing Data Sets

You can adjust the data that is pulled from the data source by changing the values in the properties panel. Customizations vary per data set and can include dates, periods, accounts, or departments. You will need to update the data set to save any edits to the customized value.

Contact your CSM if you need to make changes to available customizations.

image alt text

Rolling Forward Connected Sheets

You can use data set customizations to roll forward your data. First, perform the normal roll forward process using Project Copy. This creates a disconnected copy of your sheet. Then, under the Customize Data Set section, type the new period or date that you want to roll forward to and click Update to Apply Changes.

image alt text