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.
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.
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.
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.
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.
Data from a connection cannot be modified in the spreadsheet and must be changed at the source.
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
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.
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.
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.
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.
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.
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.