TIBCO Data Virtualization® Adapter Online Help > TDV Google Sheets Adapter > Using Spreadsheets as Tables > Stored Procedures
 
Stored Procedures
Stored Procedures are available to complement the data available from the Using Spreadsheets as Tables. Sometimes it is necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Google Sheets Adapter Stored Procedures
Name
Description
AddSheet
Add a worksheet to an existing Google spreadsheet.
CopySheet
Copies a sheet from a spreadsheet to another spreadsheet.
CreateSchema
Creates a schema file for the specified table or view.
CreateSpreadsheet
Creates a spreadsheet in the user's Google Drive.
DeleteSheet
Deletes a worksheet in an existing Google spreadsheet.
DeleteSpreadsheet
Deletes a spreadsheet.
DownloadDocument
Downloads a file from the user's Google Drive.
FormatRange
Format cells in a specific range
GetOAuthAccessToken
Obtains the OAuth access token to be used for authentication with various Google services.
GetOAuthAuthorizationURL
Obtains the OAuth authorization URL for authentication with various Google services.
RefreshOAuthAccessToken
Obtains the OAuth access token to be used for authentication with various Google services.
UpdateSheet
Add a worksheet to an existing Google spreadsheet.
UploadDocument
Uploads a file to the user's Google Drive.
AddSheet
Add a worksheet to an existing Google spreadsheet.
Input
Name
Type
Description
SpreadsheetId
String
The ID of the spreadsheet.
SheetId
String
The ID of the sheet. Must be non-negative. This field cannot be changed once set.
Title
String
The name of the sheet.
Index
String
The index of the sheet within the spreadsheet.
SheetType
String
The type of sheet. Defaults to GRID. This field cannot be changed once set.
The allowed values are GRID, OBJECT.
The default value is GRID.
RowCount
String
The number of rows in the grid.
ColumnCount
String
The number of columns in the grid.
FrozenRowCount
String
The number of rows that are frozen in the grid.
FrozenColumnCount
String
The number of columns that are frozen in the grid.
HideGridlines
String
True if the grid is not showing gridlines in the UI.
The allowed values are true, false.
Hidden
String
True if the sheet is hidden in the UI, false if it is visible.
The allowed values are true, false.
RightToLeft
String
True if the sheet is an RTL sheet instead of an LTR sheet.
The allowed values are true, false.
Output
Name
Type
Description
Success
String
This value shows whether the operation was successful or not.
CopySheet
Copies a sheet from a spreadsheet to another spreadsheet.
Input
Name
Type
Description
SpreadsheetId
String
The ID of the spreadsheet containing the sheet to copy.
SheetId
String
The ID of the sheet to copy
DestinationSpreadsheetId
String
The ID of the spreadsheet where it will be copied to.
SheetName
String
Optional parameter. The new name you want to set to the newly created sheet.
Result Set Columns
Name
Type
Description
Success
String
This value shows whether the operation was successful or not.
SheetId
String
The Id of the newly created sheet
CreateSchema
Creates a schema file for the specified table or view.
Input
Name
Type
Description
TableName
String
The name of the table or view.
FileName
String
The full file path and name of the schema to generate.
Output
Name
Type
Description
Result
String
Returns Success or Failure.
CreateSpreadsheet
Creates a spreadsheet in the user's Google Drive.
Input
Name
Type
Description
Title
String
The title for the spreadsheet.
Description
String
The description for the spreadsheet.
Hidden
String
This parameter sets whether or not the resource is hidden.
The allowed values are TRUE, FALSE.
The default value is FALSE.
Restricted
String
This parameter sets whether or not the resource is restricted.
The allowed values are TRUE, FALSE.
The default value is FALSE.
Starred
String
This parameter sets whether or not the resource is starred.
The allowed values are TRUE, FALSE.
The default value is FALSE.
Parents
String
The Ids of the parent folders for the created spreadsheet.
Output
Name
Type
Description
Success
String
This parameter indicates whether the operation was successful or not.
Id
String
The Id of the new spreadsheet.
DeleteSheet
Deletes a worksheet in an existing Google spreadsheet.
Input
Name
Type
Description
SpreadsheetId
String
The ID of the spreadsheet.
SheetId
String
The ID of the sheet.
Output
Name
Type
Description
Success
String
This value shows whether the operation was successful or not.
DeleteSpreadsheet
Deletes a spreadsheet.
Input
Name
Type
Description
SpreadsheetId
String
The ID of the spreadsheet.
Output
Name
Type
Description
Success
String
This value shows whether the operation was successful or not.
DownloadDocument
Downloads a file from the user's Google Drive.
Input
Name
Type
Description
Id
String
The Id of the resource to be downloaded.
FileFormat
String
The file format to be applied when saving the file, such as text/plain.
The default value is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.
LocalFile
String
The local file path including the file name for the location where the file will be saved on disk. Leave empty to keep the file in memory.
Encoding
String
If the LocalFile input is left empty, the data will be output to FileData in the specified encoding.
The allowed values are NONE, BASE64.
The default value is BASE64.
Overwrite
String
What to do when downloaded file exists. Set true to overwrite.
The allowed values are true, false.
The default value is false.
Output
Name
Type
Description
FileData
String
If the LocalFile input is empty, file data will be output in the format specified by the Encoding input.
Success
String
This value shows a boolean indication of whether the operation was successful or not.
FormatRange
Format cells in a specific range
Input
Name
Type
Description
SpreadsheetId
String
The ID of the spreadsheet.
SheetId
String
The ID of the sheet. Must be non-negative.
Range
String
The range of the cells to format. The format should be, for example A1:C3.
BackgroundColor
String
Specify background color by providing RGB values for Red, Green, Blue. For example 255,0,0.
Bold
String
Bold the text of the cells. Accepts true or false.
FontSize
String
Set font size by providing an int value.
FontFamily
String
Set the font used by providing its name.
Result Set Columns
Name
Type
Description
Success
String
This value shows whether the operation was successful or not.
GetOAuthAccessToken
Obtains the OAuth access token to be used for authentication with various Google services.
Input
Name
Type
Description
AuthMode
String
The type of authentication mode to use.
The allowed values are APP, WEB.
The default value is WEB.
Verifier
String
The verifier code returned by Google after permissions have been granted for the app to connect. WEB AuthMode only.
Scope
String
The scope of access to Google APIs. By default, access to all APIs used by this data provider will be specified.
The default value is https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets.
CallbackURL
String
Determines where the response is sent. The value of this parameter must exactly match one of the values registered in the APIs Console (including the HTTP or HTTPS schemes, capitalization, and trailing '/').
ApprovalPrompt
String
Indicates if the user should be re-prompted for consent. The default is AUTO, so a given user should only see the consent page for a given set of scopes the first time through the sequence. If the value is FORCE, then the user sees a consent page even if they have previously given consent to your application for a given set of scopes.
The allowed values are AUTO, FORCE.
The default value is AUTO.
AccessType
String
Indicates if your application needs to access a Google API when the user is not present at the browser. This parameter defaults to OFFLINE. If your application needs to refresh access tokens when the user is not present at the browser, then use OFFLINE. This will result in your application obtaining a refresh token the first time your application exchanges an authorization code for a user.
The allowed values are ONLINE, OFFLINE.
The default value is OFFLINE.
State
String
Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Google authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.
 
Output
Name
Type
Description
OAuthAccessToken
String
The authentication token returned from Google. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken
String
A token that may be used to obtain a new access token.
ExpiresIn
String
The remaining lifetime on the access token.
GetOAuthAuthorizationURL
Obtains the OAuth authorization URL for authentication with various Google services.
Input
Name
Type
Description
Scope
String
The scope of access to Google APIs. By default, access to all APIs used by this data provider will be specified.
The default value is https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets.
CallbackURL
String
Determines where the response is sent. The value of this parameter must exactly match one of the values registered in the APIs Console (including the HTTP or HTTPS schemes, capitalization, and trailing '/').
ApprovalPrompt
String
Indicates if the user should be re-prompted for consent. The default is AUTO, so a given user should only see the consent page for a given set of scopes the first time through the sequence. If the value is FORCE, then the user sees a consent page even if they have previously given consent to your application for a given set of scopes.
The allowed values are AUTO, FORCE.
The default value is AUTO.
AccessType
String
Indicates if your application needs to access a Google API when the user is not present at the browser. This parameter defaults to OFFLINE. If your application needs to refresh access tokens when the user is not present at the browser, then use OFFLINE. This will result in your application obtaining a refresh token the first time your application exchanges an authorization code for a user.
The allowed values are ONLINE, OFFLINE.
The default value is OFFLINE.
State
String
Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Google authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Output
Name
Type
Description
URL
String
The URL to complete user authentication.
RefreshOAuthAccessToken
Obtains the OAuth access token to be used for authentication with various Google services.
Input
Name
Type
Description
OAuthRefreshToken
String
The refresh token returned from the original authorization code exchange.
Output
Name
Type
Description
OAuthAccessToken
String
The authentication token returned from Google. This can be used in subsequent calls to other operations for this particular service.
ExpiresIn
String
The remaining lifetime on the access token.
UpdateSheet
Add a worksheet to an existing Google spreadsheet.
Input
Name
Type
Description
SpreadsheetId
String
The ID of the spreadsheet.
SheetId
String
The ID of the sheet. Must be non-negative.
Title
String
The name of the sheet.
Index
String
The index of the sheet within the spreadsheet.
RowCount
String
The number of rows in the grid.
ColumnCount
String
The number of columns in the grid.
FrozenRowCount
String
The number of rows that are frozen in the grid.
FrozenColumnCount
String
The number of columns that are frozen in the grid.
HideGridlines
String
True if the grid is not showing gridlines in the UI.
The allowed values are true, false.
Hidden
String
True if the sheet is hidden in the UI, false if it is visible.
The allowed values are true, false.
RightToLeft
String
True if the sheet is an RTL sheet instead of an LTR sheet.
The allowed values are true, false.
Output
Name
Type
Description
Success
String
This value shows whether the operation was successful or not.
UploadDocument
Uploads a file to the user's Google Drive.
Input
Name
Type
Description
Id
String
The Id for the file. Only needs to be set when updating an existing document.
Name
String
The name for the file, including the extension.
Description
String
The description for the file.
Starred
String
This parameter sets whether or not the resource is starred.
The allowed values are TRUE, FALSE.
The default value is FALSE.
Parents
String
The Ids of the parent folders for the uploaded document.
MIMEType
String
The MIME type of the file.
The default value is application/vnd.google-apps.spreadsheet.
LocalFile
String
The local file path including the file name of the file to be uploaded. A value for this field is required when FileData is not specified.
FileData
String
If the LocalFile input is empty, the file data will be output to a file in the format specified by the Encoding parameter.
Encoding
String
The FileData input encoding type.
The allowed values are NONE, BASE64.
The default value is BASE64.
 
Output
Name
Type
Description
Id
String
The id of the file.
Success
String
This parameter sets whether the operation was successful or not.