Viridis Security

viridis security logo cybersecurity consulting and managed services
Menu

Vendor (3rd Party) Assurance Tracker – Part 2

Vendor (3rd Party) Assurance Tracker – Part 2

If you haven’t already done so, please review Part 1 of this blog where we describe all the fields included in the Vendor Assurance Google form and add some additional helper fields via code.

In Part 2 we will be adding the ability to dynamically adjust the list of users/admins and the roles/permissions on the Vendor Assurance Google Form discussed in Part 1. In addition, we will adjust an optional field called Default Groups. Default Groups allows you to track which departments/functions should automatically get access to the vendor when they join the organization. This is a way to build a list of Application Grants that are needed when new employees join the company.

User List and Roles to Track

Let’s start with User/Role changes. On page 3 of the form there is a field to track key users and the roles they have with the vendor.

vendor assurance form owner admin fields

You might limit the list of users or you might included all employees. Owner and Admin are the default roles but what if you also want to track who has general access and who has no access?

Create New Sheet & Columns

vendor assurance tracker spreadsheet sheet and column changes

First we need to add some values to our spreadsheet.

  1. Add new sheet named “Access”
  2. Add three new columns
    • Rows-Employees
    • Columns-Rights
    • Default Groups (will be used in the next step)

Let’s move into Apps Script editor for the form so we can add the code:

vendor compliance tracker form script editor access

Add function in code

In the script editor, paste the new function for formConnect()

vendor assurance code addition formConnect
/*Copyright 2023 Viridis Security

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.
*/

function formConnect() {
  var ssapp = SpreadsheetApp.openById(vendorForm.getDestinationId())
  var accessSheet = ssapp.getSheetByName("Access");
  var accessData = accessSheet.getRange(2,1,accessSheet.getLastRow(),2).getValues();
  var employees = accessData.map(function(column){ 
    return column[0];
  });
  var accessRights = accessData.map(function(column){ 
    return column[1];
  });
  employees = employees.filter(row => row !== '')
  accessRights = accessRights.filter(row => row !== '')
  //Logger.log(employees)
  //Logger.log(accessRights)
  var accessGrid = vendorForm.getItemById(1209086974)
  accessGrid.asCheckboxGridItem().setColumns(accessRights)
  accessGrid.asCheckboxGridItem().setRows(employees)

}

To test the code click the Run button in the Apps Script view. You should then see any new employees and roles appear in the form.

formConnect Trigger Creation

Next we need to create a trigger so that this script will always run when you open the form.

vendor assurance code triggers

Choose to create a new Trigger in bottom right corner.

In the pop-up:

  1. Choose the new function that was just added: Form Connect
  2. Select Event Type: On Open
  3. Save the Trigger
vendor assurance trigger settings

Access Grants for new employees

Another column I found useful is “Default Groups” which is added to the form by default. This field allows you to record which groups in the organization should automatically get access to the vendor when they join the company. This allowed us to create a list of applications for all new employees depending on what group they joined and we automatically created access grant tickets from the list.

If you don’t feel you need this field you can delete it.

If you believe it would be useful you can customize the group values by using the “Default Groups” column on the “Access” sheet as shown above.

Now add this code to the formConnect functions in the Apps Script editor:

  var accessData = accessSheet.getRange(2,3,accessSheet.getLastRow(),1).getValues()
  var groupData = accessData.map(function(column){ 
    return column[0];
  });

  groupData = groupData.filter(row => row != '')
  var groupAccess = vendorForm.getItemById(607511661)
  groupAccess.asCheckboxItem().setChoiceValues(groupData)

Make sure the code is added inside the last curly brace of the formConnect function.

Use the run button to test. The trigger is already created to run that function.

Video Tutorial

I hope you find this tool useful. Many Compliance Automation tools include vendor tracking and assurance including customized questionnaire generation. There are also tools specific designed for this purpose like UpGuard.

Tagged