Document Servers

The document servers dataport provides access to Google Sheets spreadsheets served by Google. The dataport is named generically since other document servers, such as Microsoft's Office 365 or other options, may be added in the future. The dataport takes an URL for a published Google Sheets spreadsheet and shows it as one or more tables in the Manifold project. The exposed data is read-only.

Google Sheets is a free, cloud-based spreadsheet application. Many people use Google Sheets both as an online spreadsheet and also as a way of publishing data, since a URL can be published to a public sheet in Google Sheets that anyone can access. Google Sheets can have any number of rows and up to approximately 18,000 columns so long as the total number of cells is 5 million or fewer cells. For example, a sheet with ten cells (ten columns) per row therefore could have 500,000 rows. The five million cell limitation is enough rows and columns for many data sets.

We can bring data from Google Sheets into Manifold using two, related techniques:

Create a new data source of type Web Server: documentserver using the File - Create - New Data Source command. This leaves the data in Google Sheets in what appears as a read-only table, and links that table into the project.

Create a new data source of type Web Server: documentserver using the File - Create - New Data Source command. Next, Copy tables from the Google Sheets data source and Paste them into the project as a local drawing and table. This copies the data from Google Sheets and saves it in the Manifold .map project. We can then add a key field and index to the table to make the drawing and table selectable and fully read/write.

Connecting to a Google Sheets spreadsheet:

Choose File - Create - New Data Source in the main menu, or right-click in the Project pane and choose New Data Source.
In the dropdown menu choose More... to launch the New Data Source dialog.
Choose Web Server: documentserver in the Type box.
Enter the URL for the Google Sheets spreadsheet in the Source box.
Default settings for other options will usually work for most Google Sheets data. Press Create Data Source.
A new data source appears in the project. Open it to see the tables that are linked in from the Google Sheets spreadsheet.

For example, connecting to a Google Sheets URL might produce a data source that we have named Google Sheets Transactions Example that has three tables in it.

Double-clicking open the Balance History table in the data source, as shown above, we see from the gray background that it is a read-only table. However, even though it is a read-only table we can still style the table fields, for example, specifying a currency style for the Balance field as we have done in the illustration above.

We can also Copy the table and Paste it into our local project, and we can right-click the table within the data source and choose Export to export it to a table format file.

Refreshing a Document Server Data Source

When we create a new data source of type Web Server: documentserver using the File - Create - New Data Source command the tables shown in that data source are resident on Google's servers. Those tables might change based on work other people do in their Google Sheets accounts.

We can update all tables in the data source by right-clicking on the data source name in the project and choosing Refresh. That will add any new tables added, delete those that have been deleted, and update any records in the tables.

When a table from the data source is open in a table window, we can update that single table by choosing View - Refresh with the focus on the open table window.

Create a Local Copy of Google Sheets Tables

Creating a new data source of type Web Server: documentserver using the File - Create - New Data Source command opens a connection to Google Sheets documents that are resident on Google's server. Those tables are read-only, cannot be edited and the rows within them are not selectable since they do not have any indexes. We cannot add an index to a linked Google Sheets table since the dataport is read only, and Google Sheets does not support indexes.

We can, however, add a key field and index to a local table that has been copied and pasted from a linked Google Sheets table. Such local tables are fully capable, native Manifold tables.

If desired, we can Copy tables from the Google Sheets data source and Paste them into the project as a local drawing and table. This copies the data from Google Sheets and saves it in the Manifold .map project.

Creating a local copy of the Google Sheets table:

Click the table linked in from Google Sheets to highlight it.
Press Ctrl-C or click the Copy button in the Project pane toolbar.
Click anywhere in the main, Manifold .map part of the project outside of the new data source's hierarchy.
Press Ctrl-V or click the Paste button in the Project pane toolbar.

We can easily add a key field and index to a local table to enable editing and selection in the table, using the procedure given in the Add an Index to a Table topic.

Adding an index to a table:

Double-click on the table to open it.
Choose Edit - Schema.
In the Schema dialog click the Add Identity button.
Press the Save Changes button.

Note that the local copy of a table from Google Sheets will be stored in the local project and will not be dynamically updated if the original Google Sheets table within Google is changed on the Google server.

New Data Source Dialog and Controls

In the main menu, choose File - Create - New Data Source. The dropdown menu provides a list of favorites to choose from as well as a More... option.

Choose More... to launch the New Data Source dialog. Choose Web Server: documentserver in the Type box.

Name	Name for the new data source, "Data Source" by default. Specify a more memorable name as desired. If we forget the origin of a data source we can hover the mouse over the data source name in the Project pane and a tool tip will provide connection information.
Type	Choose Web Server: documentserver in the Type box to connect to a Google Sheets spreadsheet over the web.
Source	A connection string to the Google Sheets spreadsheet. The default string is just a starter: we will have to enter the URL to the Google Sheets spreadsheet we want to use. This may be a simple URL as in the example above, which is just a URL to a file on the server, or a very lengthy, complex URL/connection string that embeds parameters such as keys that grant access or other parameters. The connection string can also be entered using the Web Login dialog launched by the [...] browse button.
Browse button	Click to launch the Web Login dialog, to allow use of a login and password plus use of a proxy server if desired. The Web Login dialog is also handy for providing a Test button that can be used to test the connection. We will have to use the Web Login dialog to provide a login and password if the Google Sheets spreadsheet we want to use is not publicly accessible.
Open as read-only	Open the data source read-only. Has no effect with Google Sheets spreadsheets since they are read-only in any event.
Cache data	Cache data downloaded from the server while the project is open. Provides better interactive performance and greater flexibility with read-only data sources.
Save cached data between sessions	Save the cached data for the next time this project is opened, within the .map project itself in a Cache sub-folder in the System Data hierarchy. Caution: checking this box can result in very large .map files when the results of browsing very large data from web servers are all saved. However, having such data cached in the .map is handy for offline browsing of the project.
Create Data Source	Create the new data source in the project pane and close the dialog.
Edit Query	Launch the Command Window loaded with a query that creates the data source using the given settings. A great way to learn how to use SQL to create data sources.
Cancel	Exit the dialog without doing anything.

Pressing the browse button next to the Source box launches the Web Login dialog, to specify a server and connection characteristics.

Server	A connection string to the Google Sheets spreadsheet. The default string is just a starter: we will have to enter the URL to the Google Sheets spreadsheet we want to use. This may be a simple URL as in the example above, which is just a URL to a file on the server, or a very lengthy, complex URL/connection string that embeds parameters such as keys that grant access or other parameters.
Use login and password	Check this box if the Google Sheets account requires logging in with a login name and a password, providing the required name and password in the Login and Password boxes.
Use proxy server	Check this box when connecting through a proxy server. The Proxy, Login, and Password boxes allow specifying the connection string to the proxy server as well as the login name and password required to use the proxy server.
User agent	Identifies what application (Manifold) is asking for a connection. Some servers want to know what client software is connecting, for compatibility or for business reasons. The default string optimizes compatibility (Mozilla is very generic) while also identifying Manifold Release 9 as the client. Users can adjust the string as necessary to comply with any special server requirements.
API key	Provide a key that authorizes use of an API when connecting to a proprietary data source that requires such a key. Not used with Google Sheets. This option is disabled for server dataports that do not use it.
Application key	A secondary application key or authentication code for those servers, such as here.com (also known as wego.here.com) that may require it. Not used with Google Sheets. This option is disabled for server dataports that do not use it.
Timeout	Specify a time in milliseconds to wait for connecting to the specified spreadsheet. Use 0 for the default timeout or specify whatever is the desired time to wait before giving up on the server.
Test	Press the Test button to try the connection using the specified parameters. If successful, a Connection established information dialog will pop open.

Notes

Problems connecting - Check the Log Window to see what is going on behind the scenes if an attempted connection does not work. The problem is usually a wrong connection string or URL, failure to provide required credentials such as an key string that authorizes access, wrong choice of protocol (the server uses WMS and the user picks something else), the server is not a FeatureServer or ImageServer or Mapserver, an incredibly slow server, a server that is offline or a server that is wrongly configured and which is not correctly using the protocol it claims to use.

Visit the Manifold community forum and talk out difficulties with other users. Make sure to post full information on what you are doing, the connection URL you used, all details of how you tried to connect (including all settings in the data source dialog), what happened, and what the Log Window reported. If other users cannot help you, spending a tech support incident will produce an authoritative analysis of the issue.

Try the URL in a browser - Checking the URL by launching it in a browser can reveal many problems with the URL or with the web server. If a URL does not work in a Manifold web server dataport, try exactly the same URL in a browser. If a browser cannot connect to the URL, the Manifold web server dataport will not be able to connect to it either. If a browser cannot connect to that URL, that indicates the problem is the URL or the web server. For example, the web server might be offline. Or, for example, If the browser connects to a page other than the actual web service endpoint, such as, to a web page that lists various options for web servers, that shows the URL is not a URL for a web server but a URL to some other sort of web page.

Connection problems are often caused by incorrect URLs. There might be a typographical error in the URL or the URL might not be an endpoint to a functioning server but instead a URL to some other web page. The server responding to the URL may have geographic restrictions (surprisingly common) that does not respond to connections from IP addresses that are thought to be in a canceled country. Trying the URL in a browser will fail in such cases. Web servers may also have other restrictions, such as only allowing connections from white listed IP addresses, from paying clients, or from those clients that use a special security scheme.