GoogleSheet Component

Properties Methods Events Configuration Settings Errors

The GoogleSheet component is used to work with Google spreadsheets.

Syntax

nsoftware.InSpreadsheet.Googlesheet

Remarks

The GoogleSheet component provides an easy-to-use interface to create and modify Google spreadsheets.

This service supports authentication via OAuth 2.0. First, perform OAuth authentication using the OAuth component or a separate process. Once complete you should have an authorization string which looks like:

Bearer ya29.AHES6ZSZEJzATdZYjeihDn5W-VrXSsxEZu5p0pclxGdKKQ

Assign this value to the Authorization property before attempting any operations. Consult the Google documentation for this particular service for more information about supported scope values and more details on OAuth authentication.

Creating and Loading Spreadsheets

The component supports creating new spreadsheets and loading existing spreadsheets from Google drive.

Creating a new spreadsheet is as simple as calling Create. To upload an existing spreadsheet (.xlsx) set LocalFile before calling Create.

To load an existing spreadsheet in Google drive simply call Load.

Working with Sheets

After a spreadsheet is loaded the first sheet will automatically be loaded. Set SheetIndex to select a sheet. After selecting a sheet SheetName will be populated with the sheet's name. Other methods such as AddSheet, DeleteSheet, and RenameSheet can be used to further manage sheets.

Working with Cells

After a spreadsheet is loaded set Cell using A1 notation to select a cell. The following properties will be populated:

To set a new value for a cell set CellValue and call Save.

Rows and columns may be managed by using the AddRows, AddColumns, DeleteRows, and DeleteColumns methods.

Spreadsheet Management

The component also provides functionality to manage spreadsheets. To list available spreadsheets call ListSpreadsheets. This populates the Spreadsheets collection with information about the spreadsheets including Title and Id.

The methods Create, Delete, Load, Rename, and Save may be used to manage spreadsheets.

Property List

The following is the full list of the properties of the component with short descriptions. Click on the links for further details.


Authorization	OAuth 2.0 Authorization Token.
Cell	The selected cell in A1 notation.
CellNumericValue	The numeric value of the cell, if any.
CellText	The displayed text of the cell.
CellValue	The value of the cell.
ColumnCount	The number of columns in the sheet.
Firewall	A set of properties related to firewall access.
LocalFile	The path to the localfile for upload.
Proxy	A set of properties related to proxy access.
RowCount	The number of rows in the sheet.
SheetCount	The number of sheets in the current spreadsheet.
SheetIndex	Specifies the currently selected sheet.
SheetName	The name of the sheet specified by SheetIndex.
Spreadsheets	A collection of spreadsheet information.
SSLAcceptServerCert	Instructs the component to unconditionally accept the server certificate that matches the supplied certificate.
SSLCert	The certificate to be used during SSL negotiation.
SSLServerCert	The server certificate for the last established connection.
Timeout	A timeout for the component.

Method List

The following is the full list of the methods of the component with short descriptions. Click on the links for further details.


AddColumns	Adds the specified number of columns.
AddRows	Adds the specified number of rows.
AddSheet	Adds a new sheet with the specified number of rows and columns.
Config	Sets or retrieves a configuration setting .
Create	Creates the sheet with the specified title.
Delete	Deletes the spreadsheet.
DeleteColumns	Deletes the specified number of columns.
DeleteRows	Deletes rows from the specified index.
DeleteSheet	Deletes the sheet specified by SheetIndex.
Interrupt	Interrupts the current HTTP operation.
ListSpreadsheets	Lists all spreadsheets.
Load	Loads a spreadsheet by title.
Rename	Renames a sheet.
RenameSheet	Renames the sheet specified by SheetIndex.
Reset	Reset the component.
Save	Saves the spreadsheet.

Event List

The following is the full list of the events fired by the component with short descriptions. Click on the links for further details.


Connected	Fired immediately after a connection completes (or fails).
Disconnected	Fired when a connection is closed.
EndTransfer	Fired when a document finishes transferring.
Error	Information about errors during data delivery.
Header	Fired every time a header line comes in.
SheetInfo	Fired for each sheet in the spreadsheet when Load is called.
SpreadsheetList	Fires once for each spreadsheet when ListSpreadsheets is called.
SSLServerAuthentication	Fired after the server presents its certificate to the client.
SSLStatus	Shows the progress of the secure connection.
StartTransfer	Fired when a document starts transferring (after the headers).
Status	Fired when the HTTP status line is received from the server.
Transfer	Fired while a document transfers (delivers document).

Configuration Settings

The following is a list of configuration settings for the component with short descriptions. Click on the links for further details.


Author	The author of spreadsheet.
AuthorFormat	The format of the Author information.
CellLastUpdatedTime	The last time the cell was updated.
DateFormat	The format string to be used for formatting the DateTime values received from the server.
Id	The unique Id of the spreadsheet.
LastUpdatedTime	The last time the spreadsheet was updated.
LoadNewSpreadsheet	Whether to automatically load a newly created spreadsheet.
PreloadCells	The size of an area to pre-load.
RowLastUpdatedTime	The last time the row was updated.
Title	The title of the spreadsheet.
LogRawData	Whether to include all request and response data for debugging.
RawRequest	The request data for debugging.
RawResponse	The response data for debugging.
AcceptEncoding	Used to tell the server which types of content encodings the client supports.
AllowHTTPCompression	This property enables HTTP compression for receiving data.
AllowIdenticalRedirectURL	Allow redirects to the same URL.
Append	Whether to append data to LocalFile.
Authorization	The Authorization string to be sent to the server.
BytesTransferred	Contains the number of bytes transferred in the response data.
EncodeURL	If set to true the URL will be encoded by the component.
FollowRedirects	Determines what happens when the server issues a redirect.
GetOn302Redirect	If set to true the component will perform a GET on the new location.
HTTPVersion	The version of HTTP used by the component.
IfModifiedSince	A date determining the maximum age of the desired document.
KeepAlive	Determines whether the HTTP connection is closed after completion of the request.
MaxRedirectAttempts	Limits the number of redirects that are followed in a request.
OtherHeaders	Other headers as determined by the user (optional).
ProxyAuthorization	The authorization string to be sent to the proxy server.
ProxyAuthScheme	The authorization scheme to be used for the proxy.
ProxyPassword	A password if authentication is to be used for the proxy.
ProxyPort	Port for the proxy server (default 80).
ProxyServer	Name or IP address of a proxy server (optional).
ProxyUser	A user name if authentication is to be used for the proxy.
TransferredDataLimit	The maximum number of incoming bytes to be stored by the component.
TransferredHeaders	The full set of headers as received from the server.
UseChunkedEncoding	Enables or Disables HTTP chunked encoding for transfers.
ChunkSize	Specifies the chunk size in bytes when using chunked encoding.
UserAgent	Information about the user agent (browser).
KerberosSPN	The Service Principal Name for the Kerberos Domain Controller.
ConnectionTimeout	Sets a separate timeout value for establishing a connection.
FirewallAutoDetect	Tells the component whether or not to automatically detect and use firewall system settings, if available.
FirewallHost	Name or IP address of firewall (optional).
FirewallPassword	Password to be used if authentication is to be used when connecting through the firewall.
FirewallPort	The TCP port for the FirewallHost;.
FirewallType	Determines the type of firewall to connect through.
FirewallUser	A user name if authentication is to be used connecting through a firewall.
KeepAliveTime	The inactivity time in milliseconds before a TCP keep-alive packet is sent.
KeepAliveInterval	The retry interval, in milliseconds, to be used when a TCP keep-alive packet is sent and no response is received.
Linger	When set to True, connections are terminated gracefully.
LingerTime	Time in seconds to have the connection linger.
LocalHost	The name of the local host through which connections are initiated or accepted.
LocalPort	The TCP port in the local host where the component binds.
MaxLineLength	The maximum amount of data to accumulate when no EOL is found.
MaxTransferRate	The transfer rate limit in bytes per second.
RecordLength	The length of received data records.
TCPKeepAlive	Determines whether or not the keep alive socket option is enabled.
UseIPv6	Whether to use IPv6.
TcpNoDelay	Whether or not to delay when sending packets.
ReuseSSLSession	Determines if the SSL session is reused.
SSLCipherStrength	The minimum cipher strength used for bulk encryption.
SSLEnabledProtocols	Used to enable/disable the supported security protocols.
SSLProvider	The name of the security provider to use.
SSLSecurityFlags	Flags that control certificate verification.
OpenSSLCADir	The path to a directory containing CA certificates.
OpenSSLCAFile	Name of the file containing the list of CA's trusted by your application.
OpenSSLCipherList	A string that controls the ciphers to be used by SSL.
OpenSSLPrngSeedData	The data to seed the pseudo random number generator (PRNG).
AbsoluteTimeout	Determines whether timeouts are inactivity timeouts or absolute timeouts.
FirewallData	Used to send extra data to the firewall.
InBufferSize	The size in bytes of the incoming queue of the socket.
OutBufferSize	The size in bytes of the outgoing queue of the socket.
CodePage	The system code page used for Unicode to Multibyte translations.