Google Sheets

Interact with Google Sheets

Setup

To use the A-ops integration for google-sheets, a google service account wiht API access to Google Drive API and Google Sheets API is required. To create and provide access to an account, follow the below steps:

  1. Login to https://developers.google.com/
  2. Head to Google Developers Console and create a new project (or select the one you already have).
  3. In the box labeled “Search for APIs and Services”, search for “Google Drive API” and enable it.
  4. In the box labeled “Search for APIs and Services”, search for “Google Sheets API” and enable it.
  5. Go to “APIs & Services > Credentials” and choose “Create credentials > Service account key”.
  6. Fill out the form
  7. Click “Create” and “Done”.
  8. Press “Manage service accounts” above Service Accounts.
  9. Press on ⋮ near recently created service account and select “Manage keys” and then click on “ADD KEY > Create new key”.
  10. Select JSON key type and press “Create”.

The JSON file should look similar to the one below:

{
    "type": "service_account",
    "project_id": "api-project-XXX",
    "private_key_id": "2cd … ba4",
    "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n",
    "client_email": "473000000000-yoursisdifferent@developer.gserviceaccount.com",
    "client_id": "473 … hd.apps.googleusercontent.com",
    ...
}
  1. Optional If you are using key-storage, you need to upload the JSON file in A-Ops storage. Instruction of how to set-up Storage please follow the guide: Storage Guide

for more information please refer to: https://docs.gspread.org/en/latest/oauth2.html#enable-api-access-for-a-project

Actions

Google Sheets Connect

Input

Name Description Type Required Syntax
service_user_key input False False

Output

Name Description Type always_present values
result Returns True when successful. boolean True {"True": {"description": "Successful."}, "False": {"description": "Failure."}}
rc Returns the exit code for the action. number True {"0": {"description": "Successful."}}

Get Google Sheet

Input

Name Description Type Required Syntax
sheet_id input False False
worksheets json-input False False
has headers checkbox False False
output dropdown False False
save to storage group-checkbox False False
storage id input False False
create new storage group-checkbox False False
always checkbox False False
storage name input False False
storage provider id input False False
storage comment input False False

Output

Name Description Type always_present values
result Returns True when successful. boolean True {"True": {"description": "Successful."}, "False": {"description": "Failure."}}
rc Returns the exit code for the action. number True {"0": {"description": "Successful."}}

Write Google Sheet

Input

Name Description Type Required Syntax
sheet_id input False False
worksheet input False False
input_data input False False
clear_worksheet checkbox False False
append checkbox False False

Output

Name Description Type always_present values
result Returns True when successful. boolean True {"True": {"description": "Successful."}, "False": {"description": "Failure."}}
rc Returns the exit code for the action. number True {"0": {"description": "Successful."}}

Google Sheets Insert

Inserts into an existing Google Docs sheet at a provided position i.e. A1

Input

Name Description Type Required Syntax
Sheet ID Google Docs sheet ID as found within the URL input True True
Worksheet Name of the target worksheet within the Google Docs sheet input True True
Start Position Start position for the insert i.e. A1 input True True
Data List of CSV of values to insert at the given position input True True

Output

Name Description Type always_present values
result Returns True when successful. boolean True {"True": {"description": "Successful."}, "False": {"description": "Failure."}}
rc Returns the exit code for the action. number True {"0": {"description": "Successful."}, "500" : { "description" : "No Google Sheet API connection found, ensure you first connect." }, "501" : { "description" : "Data was not in CSV or list format." }, "502" : { "description" : "Error." }}

Google Sheets Update Cell

Updates a cell with a provided value

Input

Name Description Type Required Syntax
Sheet ID Google Docs sheet ID as found within the URL input True True
Worksheet Name of the target worksheet within the Google Docs sheet input True True
Column Column number i.e. 1 would be column 'A' input True True
Row Row number i.e. 1 input True True
Value Value to update into the cellValue to update into the cell input True True

Output

Name Description Type always_present values
result Returns True when successful. boolean True {"True": {"description": "Successful."}, "False": {"description": "Failure."}}
rc Returns the exit code for the action. number True {"0": {"description": "Successful."}, "500" : { "description" : "No Google Sheet API connection found, ensure you first connect." }, "501" : { "description" : "Data was not in CSV or list format." }, "502" : { "description" : "Error." }}

Google Sheets Get Range

Get a list of values from a Google Docs sheet using a range i.e. A:B

Input

Name Description Type Required Syntax
Sheet ID Google Docs sheet ID as found within the URL input True True
Worksheet Name of the target worksheet within the Google Docs sheet input True True
Range Cell range i.e. A:B input True True

Output

Name Description Type always_present values
result Returns True when successful. boolean True {"True": {"description": "Successful."}, "False": {"description": "Failure."}}
rc Returns the exit code for the action. number True {"0": {"description": "Successful."}, "500" : { "description" : "No Google Sheet API connection found, ensure you first connect." }, "501" : { "description" : "Data was not in CSV or list format." }, "502" : { "description" : "Error." }}
data List of values list False []

Python Requirements

  • gspread

Default configuration requirements are automatically installed when an integration is installed.

Open Source Attributions

SecureAck integration includes the following additional python third-party software/licensing:

----------------
** gspread ( https://github.com/burnash/gspread )
MIT

Copyright (C) 2011-2023 Anton Burnashev

Permission is hereby granted, free of charge, to any person obtaining a copy of
this software and associated documentation files (the "Software"), to deal in
the Software without restriction, including without limitation the rights to
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
of the Software, and to permit persons to whom the Software is furnished to do
so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.