Preparation

The Google Sheets API lets you perform administrative operations on accounts and computers stored on a Google Sheets file. The Google Sheets API must be enabled before it can be used.

To enable Google Sheets API, log into the Google API console, select the Google Sheets API and click ENABLE.

The agtgsheet connector supports OAuth 2.0, which requires a service account to call Google Sheets API. The service account must be set up in the Google API Console with scope of access granted. In order to connect through Google Sheets API an encryption file also must be generated and saved on the instance server.

For information on how to create a Google service account, enabling Google service account Domain-wide Delegation, assigning roles, or to generate a .P12 private key, refer to the "Console Help" in the Google API Console.

Your Google administrator may be able to provide the .P12 file and its private key’s password.

Once you have the encryption file, set the permissions to allow the instance to use the API:

You must write a script file to define the Google spreadsheet ID, sheet ID and column header mapping, which are used in the interaction between the Google Sheets file and the agtgsheet connector.

A sample script, agtgsheet.cfg , is in the samples\* directory. If you cannot find the sample file, try re-running setup to modify your installation. Sample files are automatically installed only with complete installations. You can select them in custom installations.

Note the sample script needs customizations to work with your Google Sheets.

# KVGROUP-V2.0
#
# Sample configuration file for agtgsheet.
#
agtgsheet = {
   

   # docid is required, and is the unique id of the Google spreadsheet.
   # This value can be pulled from the web-browser when viewing the
   # document, and it is in between the "/d/" and the "/edit" in the URL of
   # the spreadsheet. For example, consider the following URL that
   # references a Google spreadsheet:
   #
   # https://docs.google.com/spreadsheets/d/1_REoyijv6gpk_JrLLYyIPYGkclyoF8dkZmoJjk8grJc/edit#gid=0
   

  docid = 1_REoyijv6gpk_JrLLYyIPYGkclyoF8dkZmoJjk8grJc;

  # This section identifies the individual sheet within the Google
  # spreadsheet that holds user or account objects.
  #
  # If this section is removed, listing of accounts will be
  # considered an unsupported operation.
  #
   

  user sheet1 = {

    # Does the first row of the sheet contain the column headers for
   # the user attributes?  This configuration defaults to true, so the
    # connector will assume the first row contains the column headers.
    # If this is not the case this must be changed to false.
    #
    #
    # header = <true|false>;
    # If the first row contains headers (header = true; above), then map the columns
    # using the header names.  The connector has 4 pre-defined headers that are mapped
    # to internal values.   One of these is required, the other three are optional and
    # will be defaulted as described.
    #
    # o accountid - required.  This is the unique identifer for the
    #                          account or row.  When having header, if the column name happens to be accountid,
    #                          accountid=accountid; can be used for specifying the mapping.
    #
    # o shortid - optional.  If specified, this is the column that
    #                        represents the shortid (for auto
    #                        assoicate) if left empty, it will default
    #                        to the same value as accountid.
    #
    # o fullname - optional.  If specified represents the full name of
    #                         the user/account.  If not specified this
    #                         will be left blank.
    #
    # o stableid - opitonal.  Optional id for the row which does not
    #                         change if an account is renamed.  This
    #                         column can be used to track renames
    #                         within Bravura Security Fabric.
    #  stableid = "";  # no stableid default to accountid
   

                accountid = userid;
                shortid = userid;
                fullname = full_name;
   

    # Attributes.  Define the attribute column mappings.  The key
    # represents the name of the attribute within Bravura Security Fabric
    #  for the target system.  The value represents the column
    # where the value exists.  Attributes are optional.
    #
   

                attributes =  {
                  email = email;
                  phone = work_phone;
                  mobile = cell_phone;
                };
   

    # If the sheet does not leverage headers in the first row, then you must
    # identify each column by its column name on the sheet.  This will be the
    # letter value of the column (e.g. A, B, C...).
    #
    # Example with no headers:
   #
    # header = false;  # disable headers.
    #
    #
    # accountid = A;
    # shortid = C;
    # fullname = D;
    #
    # attributes = {
    #   email = H;
    #   phone = I;
    #   mobile = J;
    # };
    #
        };
   

  # Asset configuration is for listing servers or other computer objects.
  #
  # If this section is removed, listing of computers will be
  # considered an unsupported operation.
  #
   

  asset sheet2 = {

    # Does the first row of the sheet contain the column headers for
    # the user attributes?  This configuration defaults to true, so the
    # connector will assume the first row contains the column headers.
    # If this is not the case this must be changed to false.
    #
    #
    # header = <true|false>;
   

    # If the first row contains headers (header = true; above), then map the columns
    # using the header names.  The connector has 4 pre-defined headers that are mapped
    # to internal values.   One of these is required, the other three are optional and
    # will be defaulted as described.
    #
   # o id - This is the unique identifier for this asset object.
    #        This mapping is required.
    #
    #
    # o desc - Description for the asset object.  This mapping is
    #          optional.
    #
    # o status - Column which holds the status (enabled/disabled) of
    #            the object.  This mapping is optional and will
    #            default to enabled if not specified.
    #
    # "disabled" and "deleted" are for defining what values from
    # "status" column are treated as disabled and deleted for the
    # assets in this sheet.
    #
    # disabled = disabled;
    # deleted = deleted;
    #
    #
    # Attributes.  Define the attribute column mappings.  The key
    # represents the name of the attribute within Bravura Security Fabric
    #  for the target system.  The value represents the column
    # where the value exists.  Attributes are optional.
   

    #
    header = true;
    id=ID;
    desc=description;
                attributes =  {
                  operating_system = os;
                  brand = manufacturer;
                  version = ver;
                };
   

    # If the sheet does not leverage headers in the first row, then you must
    # identify each column by its column name on the sheet.  This will be the
    # letter value of the column (e.g. A, B, C...).
    #
    # Example with no headers:
    #
    # header = false;  # disable headers.
    #
    #
    # id = A;
    # desc = C;
    # status = D;
    #
    # disabled = disabled;
    # deleted = deleted;
    #
    # attributes = {
    #   operating_system = H;
    #   brand = I;
    #   version = J;
    # }
    #
  };
};

Once you have added the script to the script directory, you can add Google Sheets as a target system to the Bravura Security Fabric instance.