Google Sheets
LAST UPDATED: OCT 31, 2024
Overview
Google Sheets is a spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google. The integration with Google Sheets includes commands such as CreateSpreadsheett, Get Spread Sheet, Append Sheet Value, and Update Sheet Value. Google Sheets is tied to Google Drive. Please add Google Drive scope to the selected account for Test Connection and Get Spreadsheet to work properly.
D3 SOAR is providing REST operations to function with Google Sheets.
Google Sheets is available for use in:
Connection
To connect to Google Sheets from D3 SOAR, please follow this part to collect the required information below:
Parameter | Description | Example |
Admin Email | The user email for authentication. | sysint@d3cyberlab.com |
Service Account JSON | The contents of the service account JSON file. For more information on obtaining the service account JSON file, refer to the document at Cybersecrutiy Help > API Integrations > API Key Generation. You may also refer to Using OAuth 2.0 for Server to Server Applications from Google’s documentation. | { "type": "***", "project_id": "***", "private_key_id": "***", "private_key": "***", "client_email": "***", "client_id": "***", "auth_uri": "***", "token_uri": "***", "auth_provider_x509_cert_url": "***", "client_secret": "***", "redirect_uris": [ "***" ] } |
API Version | The API version to use for the connection. | v4 |
Permission Requirements
Each endpoint in the Google Sheets API requires a certain permission scope. The following are required scopes for the commands in this integration:
Command | Required Permission |
Append Value to Spreadsheet | https://www.googleapis.com/auth/spreadsheets |
Create Spreadsheet | https://www.googleapis.com/auth/spreadsheets |
Get Spreadsheet | https://www.googleapis.com/auth/spreadsheets |
List Spreadsheets | https://www.googleapis.com/auth/drive |
Update Spreadsheet Value | https://www.googleapis.com/auth/spreadsheets |
Test Connection | https://www.googleapis.com/auth/drive |
Configuring Google Sheets to Work with D3 SOAR
Obtaining the Service Account JSON
To connect to Google Sheets with D3 SOAR, you need to configure the Service Account JSON file. First, log in to the Google Cloud Platform(GCP) console with admin credentials.
Click the hamburger menu in the top left corner to reveal the sidebar menu. Navigate to APIs and services. In its submenu, select Credentials.
Click + CREATE CREDENTIALS, and select Service account.
Input the Service account name and description, then click CREATE AND CONTINUE.
Assign a role to the service account (e.g. owner), then click CONTINUE.
You can opt to grant users access to this service account. Click DONE to confirm your configurations.
You will find the service account you have just created on the credentials page. Click and open the service account.
In the KEYS tab, click ADD KEY, then select Create new key.
Choose JSON as the key type, then click CREATE.
The Service Account JSON file (Private key) will automatically be downloaded to your computer.
If this is your first time using the Google Drive API, you will need to enable it from the Google API Library. On the Google Cloud Platform, click the hamburger menu in the top left to reveal the sidebar menu. Navigate to APIs and services. In its submenu, select Library.
Search and select Google Drive API.
Click the ENABLE button. You will see the Google Drive API enabled.
Configuring the Service Account Domain-wide Delegation
You will also need to enable the Google Workspace domain-wide delegation for your created service account. Please follow the steps below.
Log in to the Google Workspace Admin Console with admin credentials. Navigate to Security > Access and data control > API controls. Scroll down and click MANAGE DOMAIN-WIDE DELEGATION.
Click Add new to add a new API client.
Find your Client ID in the service account you created and paste it into the Client ID field. Input your desired scope into the OAuth scopes field. Please refer to the Permission Requirements. Then click AUTHORISE.
The service account domain-wide delegation can now be found on the API controls page. Your created service account is now ready to use.
Configuring D3 SOAR to Work with Google Sheets
Log in to D3 SOAR.
Find the Google Sheets integration.
Navigate to Configuration on the top header menu.
Click on the Integration icon on the left sidebar.
Type Google Sheets in the search box to find the integration, then click it to select it.
Click New Connection, on the right side of the Connections section. A new connection window will appear.
Configure the following fields to create a connection to Google Sheets.
Connection Name: The desired name for the connection.
Site: Specifies the site to use the integration connection. Use the drop-down menu to select the site. The Share to Internal Sites option enables all sites defined as internal sites to use the connection. Selecting a specific site will only enable that site to use the connection.
Recipient site for events from connections Shared to Internal Sites: This field appears if you selected Share to Internal Sites for Site to let you select the internal site to deploy the integration connection.
Agent Name (Optional): Specifies the proxy agent required to build the connection. Use the dropdown menu to select the proxy agent from a list of previously configured proxy agents.
Description (Optional): Add your desired description for the connection.
Tenant (Optional): When configuring the connection from a master tenant site, you have the option to choose the specific tenant sites you want to share the connection with. Once you enable this setting, you can filter and select the desired tenant sites from the drop-downs to share the connection.
Configure User Permissions: Defines which users have access to the connection.
Active: Check the tick box to ensure the connection is available for use.
System: This section contains the parameters defined specifically for the integration. These parameters must be configured to create the integration connection.
1. Input the Admin Email.
2. Input the saved Service Account JSON.
3. Input the API Version. The default value is v4.Enable Password Vault: An optional feature that allows users to take the stored credentials from their own password vault. Please refer to the password vault connection guide if needed.
Connection Health Check: Updates the connection status you have created. A connection health check is done by scheduling the Test Connection command of this integration. This can only be done when the connection is active.
To set up a connection health check, check the Connection Health Check tickbox. You can customize the interval (minutes) for scheduling the health check. An email notification can be set up after a specified number of failed connection attempts.
Test the connection.
Click Test Connection to verify the account credentials and network connection. If the Test Connection Passed alert window appears, the test connection is successful. You will see Passed with a green checkmark appear beside the Test Connection button. If the test connection fails, please check your connection parameters and try again.
Click OK to close the alert window.
Click Add to create and add the configured connection.
Commands
Google Sheets includes the following executable commands for users to set up schedules or create playbook workflows. With the Test Command, you can execute these commands independently for playbook troubleshooting.
Integration API Note
For more information about the Google Sheets API, please refer to the Google Sheets API reference.
READER NOTE
Certain permissions are required for each command. Please refer to the Permission Requirements and Configuring Google Sheets to Work with D3 SOAR for details.
Append Value to Spreadsheet
Appends values to a spreadsheet. For API’s append logic in a spreadsheet, see the Appending Values section from the Reading & Writing Cell Values Google Sheets API document.
READER NOTE
Notes for parameters:
Spreadsheet ID is a required parameter to run this command.
Run the List Spreadsheet command to obtain the Spreadsheet ID. Spreadsheet IDs can be found in the raw data at the path $.files[*].id.
Range is a required parameter to run this command.
Please refer to the A1 notation section of Google Sheets API Overview for more information on the syntax.
Only the first notation will be used to define the append table. For example, if you input Sheet1!A1:C2, your table will start from A1, and the table range will be defined by your input value. If your input value is [[123,"abc", 456], [1234,"abcd", 4565], [123,"abc", 456], [1234,"abcd", 4565]], then the table will span from A1 to D4. The C2 range will not limit your table value. It is recommended to only input the upper leftmost cell of the range. The remaining cells of the table will be appended accordingly.
This range parameter is different from the range parameter in the Get Spreadsheet command. Please follow the A1 notation syntax.
Value Input Option is an optional parameter to run this command.
There are two options for this parameter: Raw and User Entered.
Raw: The input is not parsed and is simply inserted as a string, so an input value of "=1+2" places the string "=1+2" in the cell, instead of a formula. (Non-string values like booleans or numbers are always handled as RAW.)
User Entered: The input is parsed exactly as if it were entered into the Google Sheets UI, so "Mar 1 2016" becomes a date, and "=1+2" becomes a formula. Formats may also be inferred, so "$100.15" becomes a number with currency formatting.
Major Dimension is an Optional parameter to run this command.
The majorDimension parameter does not control whether data is added as rows or columns to the table. Data will always be added to subsequent rows. The parameter only controls how the input data is read.
Value is a required parameter to run this command.
Empty array [] can be accepted, but nothing will be updated.
You may input “” inside the value string to make some specific cells empty.
For more information about the API’s append logic in a spreadsheet, see the Appending Values section from the Reading & Writing Cell Values Google Sheets API document.
Input
Input Parameter | Required/Optional | Description | Example |
Spreadsheet ID | Required | The ID of spreadsheet to append values. Spreadsheet ID can be obtained using the List Spreadsheets command. | ***** |
Range | Required | The A1 notation of a range to search for a logical table of data. Values are appended after the last row of the table. For more information regarding the A1 notation, please refer to Google Sheets API Overview from Google’s API documentation. | testSheet1!A1:C2 |
Value Input Option | Optional | How the input data should be interpreted. Raw: The values the user has entered will not be parsed and will be stored as-is. User Entered: The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. | Raw |
Major Dimension | Optional | The input values in the Values parameter are by rows or columns. If not this field is not defined, the default value will be ROWS. If this value is set to ROWS, an input value of [[1,2],[3,4]] for the Values parameter with a defined range of A1:B2 will result in the following output cells in the spreadsheet: A1=1,B1=2,A2=3,B2=4. If this value is set to COLUMNS, an input value of [[1,2],[3,4]] for the Values parameter with a defined range of A1:B2 will result in the following output cells in the spreadsheet: A1=1,B1=3,A2=2,B2=4. | Rows |
Values | Optional | The data to append to the spread sheet. The input format is an array of arrays, with the outer array representing all the data and each inner array representing the specified major dimension (i.e. rows or columns). Each item in the inner array corresponds with one cell in the spreadsheet. The supported input value types are bool, string, and double. Null values will be ignored. To set a cell to an empty value, set the string value to an empty string. | [[123,"abc", 456], [1234,"abcd", 4565]] |
Output
Error Handling
If the Return Data is Failed, an Error tab will appear in the Test Result window.
The error tab contains the details responded from D3 SOAR or third-party API calls, including Failure Indicator, Status Code, and Message. This can help you locate the root cause of a command failure.
Parts in Error | Description | Example |
Failure Indicator | Indicates the command failure that happened at a specific input and/or API call. | Append Value to Spreadsheet failed. |
Status Code | The response code issued by the third-party API server or the D3 SOAR system that can be used to locate the corresponding error category. For example, if the returned status code is 401, the selected connection is unauthorized to run the command. The user or system support would need to check the permission setting in the Google Sheets portal. Refer to the HTTP Status Code Registry for details. | Status Code: 404. |
Message | The raw data or captured key error message from the integration API server about the API request failure. | Message: Spreadsheet ID not found. |
Error Sample Data Append Value to Spreadsheet failed. Status Code: 404. Message: Spreadsheet ID not found. |
Create Spreadsheet
Creates a spreadsheet in the drive.
READER NOTE
If no input parameters are defined, running this command will create an untitled spreadsheet with one sheet titled “Sheet1”
Input
Input Parameter | Required/Optional | Description | Example |
Spreadsheet Title | Optional | The title of the spreadsheet. | Sample Spreadsheet |
Sheet Titles | Optional | The individual sheet title(s) in a spreadsheet. | ["SampleSheet1","SampleSheet2"] |
Output
Error Handling
If the Return Data is Failed, an Error tab will appear in the Test Result window.
The error tab contains the details responded from D3 SOAR or third-party API calls, including Failure Indicator, Status Code, and Message. This can help you locate the root cause of a command failure.
Parts in Error | Description | Example |
Failure Indicator | Indicates the command failure that happened at a specific input and/or API call. | Create Spreadsheet failed. |
Status Code | The response code issued by the third-party API server or the D3 SOAR system that can be used to locate the corresponding error category. For example, if the returned status code is 401, the selected connection is unauthorized to run the command. The user or system support would need to check the permission setting in the Google Sheets portal. Refer to the HTTP Status Code Registry for details. | Status Code: 400. |
Message | The raw data or captured key error message from the integration API server about the API request failure. | Message: Invalid email or User ID. |
Error Sample Data Create Spreadsheet failed. Status Code: 400. Message: Invalid email or User ID. |
Get Spreadsheet
Retrieves the metadata and/or grid data of a spreadsheet.
READER NOTE
Spreadsheet ID is a required parameter to run this command.
Run the List Spreadsheet command to obtain the Spreadsheet ID. Spreadsheet IDs can be found in the raw data at the path $.files[*].id.
Input
Input Parameter | Required/Optional | Description | Example |
Spreadsheet ID | Required | The ID of the spreadsheet to return. Spreadsheet ID can be obtained using the List Spreadsheets command. | ***** |
Include Grid Data | Optional | Whether the Grid Data will be included in the spreadsheet. | True |
Range | Optional | The ranges to retrieve a subset of data from the spreadsheet. Ranges are specified using the A1 notation. You can define a single cell (e.g., A1) or multiple cells (e.g., A1:D5). You can also get cells from other sheets within the same spreadsheet (e.g., Sheet2!A1:C4) or retrieve multiple ranges at once (e.g., ?ranges=A1:D5&ranges=Sheet2!A1:C4). Limiting the range to return only the portions of the spreadsheet that intersect the requested ranges. Please note that for large spreadsheets, it is best practice to retrieve only the specific spreadsheet fields that you require. | ["testSheet1!A1:C2", "testSheet2!A1:C2"] |
Output
Error Handling
If the Return Data is Failed, an Error tab will appear in the Test Result window.
The error tab contains the details responded from D3 SOAR or third-party API calls, including Failure Indicator, Status Code, and Message. This can help you locate the root cause of a command failure.
Parts in Error | Description | Example |
Failure Indicator | Indicates the command failure that happened at a specific input and/or API call. | Get Spreadsheet failed. |
Status Code | The response code issued by the third-party API server or the D3 SOAR system that can be used to locate the corresponding error category. For example, if the returned status code is 401, the selected connection is unauthorized to run the command. The user or system support would need to check the permission setting in the Google Sheets portal. Refer to the HTTP Status Code Registry for details. | Status Code: 404. |
Message | The raw data or captured key error message from the integration API server about the API request failure. | Message: Spreadsheet ID not found. |
Error Sample Data Get Spreadsheet failed. Status Code: 404. Message: Spreadsheet ID not found. |
List Spreadsheets
List spreadsheet (s) stored in My Drive or All Drives (including shared drives). To list and search other file formats in Google Drive, use the List Files command from the Google Drive integration. For this command to work, the Google Drive scope must be added to the selected account.
READER NOTE
If no input parameters are defined, running this command will return up to 100 spreadsheets, listed in ascending order.
Due to the API's limitation, only characters at the beginning of the spreadsheet's file name can be queried. A query filter beginning with characters after the first character of the file name will not work. For instance, to search for a spreadsheet with the filename MyTestSpreadsheet, the search filter key name contains "Spreadsheet" will not work. However, the following query filters will correctly return MyTestSpreadsheet:
name contains "My"
name contains "MyTest"
name = "MyTestSpreadsheet"
Input
Input Parameter | Required/Optional | Description | Example |
Filter | Optional | The query to filter the returned list of spreadsheets. Please refer to Search for files and folders from Google Drive’s API documentation for more information for the query syntax. | name = "APItest123" |
Page Size | Optional | The maximum number of files to return per page. Partial or empty result pages are possible even before the end of the files list has been reached. The valid range of input values are 1 to 1000, inclusive. The default value is 100. | 10 |
Include Items From All Drives | Optional | If both spreadsheets stored both My Drive and All Drives will be included in the results. The default value is False. | False |
Order By | Optional | A comma-separated list of sort keys to order the listed spreadsheets. The valid keys are "createdTime", "folder", "modifiedByMeTime", "modifiedTime", "name", "name_natural", "quotaBytesUsed", "recency", "sharedWithMeTime", "starred", and "viewedByMeTime". Each key sorts in ascending order by default, but may be reversed with the "desc" modifier. An example input is: folder,modifiedTime desc,name. | modifiedTime desc |
Output
Error Handling
If the Return Data is Failed, an Error tab will appear in the Test Result window.
The error tab contains the details responded from D3 SOAR or third-party API calls, including Failure Indicator, Status Code, and Message. This can help you locate the root cause of a command failure.
Parts in Error | Description | Example |
Failure Indicator | Indicates the command failure that happened at a specific input and/or API call. | List Spreadsheet failed. |
Status Code | The response code issued by the third-party API server or the D3 SOAR system that can be used to locate the corresponding error category. For example, if the returned status code is 401, the selected connection is unauthorized to run the command. The user or system support would need to check the permission setting in the Google Sheets portal. Refer to the HTTP Status Code Registry for details. | Status Code: 400. |
Message | The raw data or captured key error message from the integration API server about the API request failure. | Message: Invalid email or User ID. |
Error Sample Data List Spreadsheet failed. Status Code: 400. Message: Invalid email or User ID. |
Update Spreadsheet Value
Updates values in a spreadsheet. Note: This command will overwrite any existing cell values. To insert values, use the Append Value to Spreadsheet command.
READER NOTE
Spreadsheet ID is a required parameter to run this command.
Run the List Spreadsheet command to obtain the Spreadsheet ID. Spreadsheet IDs can be found in the raw data at the path $.files[*].id.
Input
Input Parameter | Required/Optional | Description | Example |
Spreadsheet ID | Required | The ID of the spreadsheet to update values. Spreadsheet IDs can be obtained using the List Spreadsheets command. | ***** |
Range | Optional | The A1 notation of a range to search for a logical table of data. Values are appended after the last row of the table. For more information regarding the A1 notation, please refer to: Google Sheets API Overview from Google’s API documentation. | testSheet1!A1:C2 |
Value Input Option | Optional | How the input data should be interpreted. Raw: The values the user has entered will not be parsed and will be stored as-is. User Entered: The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. The default value of this field is Raw. | Raw |
Major Dimension | Optional | The input values in the Values parameter are by rows or columns. If not this field is not defined, the default value will be ROWS. If the value is set to ROWS, an input value of [[1,2],[3,4]] for the Values parameter with a defined range of A1:B2 will result in the following output cells in the spreadsheet: A1=1,B1=2,A2=3,B2=4. If the value is set to COLUMNS, an input value of [[1,2],[3,4]] for the Values parameter with a defined range of A1:B2 will result in the following output cells in the spreadsheet: A1=1,B1=3,A2=2,B2=4. | Rows |
Values | Required | The data to append to the spreadsheet. The input format is an array of arrays, with the outer array representing all the data and each inner array representing the specified major dimension (i.e. rows or columns). Each item in the inner array corresponds with one cell in the spreadsheet. The supported input value types are bool, string, and double. Null values will be ignored. To set a cell to an empty value, set the string value to an empty string. | [[123,"abc", 456], [1234,"abcd", 4565]] |
Output
Error Handling
If the Return Data is Failed, an Error tab will appear in the Test Result window.
The error tab contains the details responded from D3 SOAR or third-party API calls, including Failure Indicator, Status Code, and Message. This can help you locate the root cause of a command failure.
Parts in Error | Description | Example |
Failure Indicator | Indicates the command failure that happened at a specific input and/or API call. | Update Spreadsheet failed. |
Status Code | The response code issued by the third-party API server or the D3 SOAR system that can be used to locate the corresponding error category. For example, if the returned status code is 401, the selected connection is unauthorized to run the command. The user or system support would need to check the permission setting in the Google Sheets portal. Refer to the HTTP Status Code Registry for details. | Status Code: 404. |
Message | The raw data or captured key error message from the integration API server about the API request failure. | Message: Spreadsheet ID not found. |
Error Sample Data Update Spreadsheet failed. Status Code: 404. Message: Spreadsheet ID not found. |
Test Connection
Allows you to perform a health check on an integration connection. You can schedule a periodic health check by selecting Connection Health Check when editing an integration connection.
Input
N/A
Output
Error Handling
If the Return Data is Failed, an Error tab will appear in the Test Result window.
The error tab contains the responses from the third-party API calls including Failure Indicator, Status Code, and Message. This can help you locate the root cause of a command failure.
Parts in Error | Description | Example |
Failure Indicator | Indicates the command failure that happened at a specific input and/or API call. | Test Connection failed. Failed to check the connector. |
Status Code | The response code issued by the third-party API server or the D3 SOAR system that can be used to locate the corresponding error category. For example, if the returned status code is 401, the selected connection is unauthorized to run the command. The user or system support would need to check the permission setting in the Google Sheets portal. Refer to the HTTP Status Code Registry for details. | Status Code: 400. |
Message | The raw data or captured key error message from the integration API server about the API request failure. | Message: Invalid email or User ID. |
Error Sample Data Test Connection failed. Failed to check the connector. Status Code: 400. Message: Invalid email or User ID. |