Skip to main content
Skip table of contents

Google Sheets

LAST UPDATED: 06/11/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:

D3 SOAR

V15.2.3.0+

Category

ITSM

Deployment Options

Option II, Option IV

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
  1. 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.

  2. Click the hamburger menu in the top left corner to reveal the sidebar menu. Navigate to APIs and services. In its submenu, select Credentials.

  1. Click + CREATE CREDENTIALS, and select Service account.

  1. Input the Service account name and description, then click CREATE AND CONTINUE.

  2. Assign a role to the service account (e.g. owner), then click CONTINUE.

  1. You can opt to grant users access to this service account. Click DONE to confirm your configurations.

  2. You will find the service account you have just created on the credentials page. Click and open the service account.

  3. In the KEYS tab, click ADD KEY, then select Create new key.

  4. Choose JSON as the key type, then click CREATE.

  5. The Service Account JSON file (Private key) will automatically be downloaded to your computer.

    Frame 1 (94)-20240612-010159.png
  6. 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.

  7. Search and select Google Drive API.

  8. 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.

  1. 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.

  2. Click Add new to add a new API client.

  3. 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.

  4. 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

  1. Log in to D3 SOAR.

  2. Find the Google Sheets integration.

    1. Navigate to Configuration on the top header menu.

    2. Click on the Integration icon on the left sidebar.

    3. Type Google Sheets in the search box to find the integration, then click it to select it.

    4. Click New Connection, on the right side of the Connections section. A new connection window will appear.

  3. Configure the following fields to create a connection to Google Sheets.

    1. Connection Name: The desired name for the connection.

    2. 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.

    3. 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.

    4. 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.

    5. Description (Optional): Add your desired description for the connection.

    6. Configure User Permissions: Defines which users have access to the connection.

    7. Active: Check the tick box to ensure the connection is available for use.

      Frame 2 (35)-20240612-010944.png
    8. System: This section contains the parameters defined specifically for the integration. These parameters must be configured to create the integration connection.
      1. Input your Admin Email.
      2. Input your saved Service Account JSON.
      3. Input the API Version. The default value is v4.

    9. 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.

    10. 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.

  4. Test the connection.

    1. 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.

    2. Click OK to close the alert window.

    3. 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

Return Data

Indicates one of the possible command execution states: Successful or Failed.

The Failed state can be triggered by any of the following errors:

  • A connection issue with the integration

  • The API returned an error message

  • No response from the API

You can view more details about an error in the Error tab.

Return Data can be passed down directly to a subsequent command or used to create conditional tasks in playbooks.

SAMPLE DATA

CODE
Successful
Raw Data

The primary response data from the API request.

SAMPLE DATA

JSON
{
    "spreadsheetId": "*****",
    "tableRange": "testSheet1!A1:C14",
    "updates": {
        "spreadsheetId": "*****",
        "updatedRange": "testSheet1!A15:C16",
        "updatedRows": 2,
        "updatedColumns": 3,
        "updatedCells": 6
    }
}
Result

Provides a brief summary of outputs in an HTML formatted table.

SAMPLE DATA

CODE
No Sample Data

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

Return Data

Indicates one of the possible command execution states: Successful or Failed.

The Failed state can be triggered by any of the following errors:

  • A connection issue with the integration

  • The API returned an error message

  • No response from the API

You can view more details about an error in the Error tab.

Return Data can be passed down directly to a subsequent command or used to create conditional tasks in playbooks.

SAMPLE DATA

CODE
Successful
Raw Data

The primary response data from the API request.

SAMPLE DATA

JSON
{
    "spreadsheetId": "*****",
    "properties": {
        "title": "Sample Spreadsheet",
        "locale": "en_GB",
        "autoRecalc": "ON_CHANGE",
        "timeZone": "Etc/GMT",
        "defaultFormat": {
            "backgroundColor": {
                "red": 1,
                "green": 1,
                "blue": 1
            },
            "padding": {
                "top": 2,
                "right": 3,
                "bottom": 2,
                "left": 3
            },
            "verticalAlignment": "BOTTOM",
            "wrapStrategy": "OVERFLOW_CELL",
            "textFormat": {
                "foregroundColor": {},
                "fontFamily": "arial,sans,sans-serif",
                "fontSize": 10,
                "bold": false,
                "italic": false,
                "strikethrough": false,
                "underline": false,
                "foregroundColorStyle": {
                    "rgbColor": {}
                }
            },
            "backgroundColorStyle": {
                "rgbColor": {
                    "red": 1,
                    "green": 1,
                    "blue": 1
                }
            }
        },
        "spreadsheetTheme": {
            "primaryFontFamily": "Arial",
            "themeColors": [
                {
                    "colorType": "TEXT",
                    "color": {
                        "rgbColor": {}
                    }
                },
                {
                    "colorType": "BACKGROUND",
                    "color": {
                        "rgbColor": {
                            "red": 1,
                            "green": 1,
                            "blue": 1
                        }
                    }
                },
                {
                    "colorType": "ACCENT1",
                    "color": {
                        "rgbColor": {
                            "red": 0.25882354,
                            "green": 0.52156866,
                            "blue": 0.95686275
                        }
                    }
                },
                {
                    "colorType": "ACCENT2",
                    "color": {
                        "rgbColor": {
                            "red": 0.91764706,
                            "green": 0.2627451,
                            "blue": 0.20784314
                        }
                    }
                },
                {
                    "colorType": "ACCENT3",
                    "color": {
                        "rgbColor": {
                            "red": 0.9843137,
                            "green": 0.7372549,
                            "blue": 0.015686275
                        }
                    }
                },
                {
                    "colorType": "ACCENT4",
                    "color": {
                        "rgbColor": {
                            "red": 0.20392157,
                            "green": 0.65882355,
                            "blue": 0.3254902
                        }
                    }
                },
                {
                    "colorType": "ACCENT5",
                    "color": {
                        "rgbColor": {
                            "red": 1,
                            "green": 0.42745098,
                            "blue": 0.003921569
                        }
                    }
                },
                {
                    "colorType": "ACCENT6",
                    "color": {
                        "rgbColor": {
                            "red": 0.27450982,
                            "green": 0.7411765,
                            "blue": 0.7764706
                        }
                    }
                },
                {
                    "colorType": "LINK",
                    "color": {
                        "rgbColor": {
                            "red": 0.06666667,
                            "green": 0.33333334,
                            "blue": 0.8
                        }
                    }
                }
            ]
        }
    },
    "sheets": [
        {
            "properties": {
                "sheetId": *****,
                "title": "SampleSheet1",
                "index": 0,
                "sheetType": "GRID",
                "gridProperties": {
                    "rowCount": 1000,
                    "columnCount": 26
                }
            }
        },
        {
            "properties": {
                "sheetId": *****,
                "title": "SampleSheet2",
                "index": 1,
                "sheetType": "GRID",
                "gridProperties": {
                    "rowCount": 1000,
                    "columnCount": 26
                }
            }
        }
    ],
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/*****/edit?ouid=*****"
}
Key Fields

Common cyber security indicators such as unique IDs, file hash values, CVE numbers, IP addresses, etc., will be extracted from Raw Data as Key Fields.

The system stores these key fields in the path $.[playbookTask].outputData. You can use these key-value pairs as data points for playbook task inputs.

SAMPLE DATA

CODE
{
  "SpreadsheetID": *****,
  "SpreadsheetURL": docs.google.com/spreadsheets/d/*****/edit?ouid=*****
}
Result

Provides a brief summary of outputs in an HTML formatted table.

SAMPLE DATA

CODE
No Sample Data

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

Return Data

Indicates one of the possible command execution states: Successful or Failed.

The Failed state can be triggered by any of the following errors:

  • A connection issue with the integration

  • The API returned an error message

  • No response from the API

You can view more details about an error in the Error tab.

Return Data can be passed down directly to a subsequent command or used to create conditional tasks in playbooks.

SAMPLE DATA

CODE
Successful
Raw Data

The primary response data from the API request.

SAMPLE DATA

JSON
{
    "spreadsheetId": "*****",
    "properties": {
        "title": "APItest123",
        "locale": "en_GB",
        "autoRecalc": "ON_CHANGE",
        "timeZone": "Etc/GMT",
        "defaultFormat": {
            "backgroundColor": {
                "red": 1,
                "green": 1,
                "blue": 1
            },
            "padding": {
                "top": 2,
                "right": 3,
                "bottom": 2,
                "left": 3
            },
            "verticalAlignment": "BOTTOM",
            "wrapStrategy": "OVERFLOW_CELL",
            "textFormat": {
                "foregroundColor": {},
                "fontFamily": "arial,sans,sans-serif",
                "fontSize": 10,
                "bold": false,
                "italic": false,
                "strikethrough": false,
                "underline": false,
                "foregroundColorStyle": {
                    "rgbColor": {}
                }
            },
            "backgroundColorStyle": {
                "rgbColor": {
                    "red": 1,
                    "green": 1,
                    "blue": 1
                }
            }
        },
        "spreadsheetTheme": {
            "primaryFontFamily": "Arial",
            "themeColors": [
                {
                    "colorType": "TEXT",
                    "color": {
                        "rgbColor": {}
                    }
                },
                {
                    "colorType": "BACKGROUND",
                    "color": {
                        "rgbColor": {
                            "red": 1,
                            "green": 1,
                            "blue": 1
                        }
                    }
                },
                {
                    "colorType": "ACCENT1",
                    "color": {
                        "rgbColor": {
                            "red": 0.25882354,
                            "green": 0.52156866,
                            "blue": 0.95686275
                        }
                    }
                },
                {
                    "colorType": "ACCENT2",
                    "color": {
                        "rgbColor": {
                            "red": 0.91764706,
                            "green": 0.2627451,
                            "blue": 0.20784314
                        }
                    }
                },
                {
                    "colorType": "ACCENT3",
                    "color": {
                        "rgbColor": {
                            "red": 0.9843137,
                            "green": 0.7372549,
                            "blue": 0.015686275
                        }
                    }
                },
                {
                    "colorType": "ACCENT4",
                    "color": {
                        "rgbColor": {
                            "red": 0.20392157,
                            "green": 0.65882355,
                            "blue": 0.3254902
                        }
                    }
                },
                {
                    "colorType": "ACCENT5",
                    "color": {
                        "rgbColor": {
                            "red": 1,
                            "green": 0.42745098,
                            "blue": 0.003921569
                        }
                    }
                },
                {
                    "colorType": "ACCENT6",
                    "color": {
                        "rgbColor": {
                            "red": 0.27450982,
                            "green": 0.7411765,
                            "blue": 0.7764706
                        }
                    }
                },
                {
                    "colorType": "LINK",
                    "color": {
                        "rgbColor": {
                            "red": 0.06666667,
                            "green": 0.33333334,
                            "blue": 0.8
                        }
                    }
                }
            ]
        }
    },
    "sheets": [
        {
            "properties": {
                "sheetId": *****,
                "title": "testSheet1",
                "index": 0,
                "sheetType": "GRID",
                "gridProperties": {
                    "rowCount": 1006,
                    "columnCount": 26
                }
            }
        },
        {
            "properties": {
                "sheetId": *****,
                "title": "testSheet2",
                "index": 1,
                "sheetType": "GRID",
                "gridProperties": {
                    "rowCount": 1000,
                    "columnCount": 26
                }
            }
        }
    ],
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/*****/edit?ouid=*****"
}
Key Fields

Common cyber security indicators such as unique IDs, file hash values, CVE numbers, IP addresses, etc., will be extracted from Raw Data as Key Fields.

The system stores these key fields in the path $.[playbookTask].outputData. You can use these key-value pairs as data points for playbook task inputs.

SAMPLE DATA

CODE
{
  "Sheet": ["testSheet1","testSheet2"],
  "RowData": ["testSheet1","testSheet2"]  
}
Result

Provides a brief summary of outputs in an HTML formatted table.

SAMPLE DATA

CODE
No Sample Data

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

Return Data

Indicates one of the possible command execution states: Successful or Failed.

The Failed state can be triggered by any of the following errors:

  • A connection issue with the integration

  • The API returned an error message

  • No response from the API

You can view more details about an error in the Error tab.

Return Data can be passed down directly to a subsequent command or used to create conditional tasks in playbooks.

SAMPLE DATA

CODE
Successful
Raw Data

The primary response data from the API request.

SAMPLE DATA

JSON
{
    "files": [
        {
            "id": "*****",
            "name": "APItest123"
        }
    ]
}
Result

Provides a brief summary of outputs in an HTML formatted table.

SAMPLE DATA

CODE
No Sample Data

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

Return Data

Indicates one of the possible command execution states: Successful or Failed.

The Failed state can be triggered by any of the following errors:

  • A connection issue with the integration

  • The API returned an error message

  • No response from the API

You can view more details about an error in the Error tab.

Return Data can be passed down directly to a subsequent command or used to create conditional tasks in playbooks.

SAMPLE DATA

CODE
Successful
Raw Data

The primary response data from the API request.

SAMPLE DATA

JSON
{
    "spreadsheetId": "*****",
    "updatedRange": "testSheet1!A1:C2",
    "updatedRows": 2,
    "updatedColumns": 3,
    "updatedCells": 6
}
Result

Provides a brief summary of outputs in an HTML formatted table.

SAMPLE DATA

CODE
No Sample Data

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

Return Data

Indicates one of the possible command execution states: Successful or Failed.

The Failed state can be triggered by any of the following errors:

  • A connection issue with the integration

  • The API returned an error message

  • No response from the API

You can view more details about an error in the Error tab.

SAMPLE DATA

CODE
Successful

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.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.