Vendor (3rd Party) Assurance Tracker

In the evolving landscape of cybersecurity, vendor assurance has come under the spotlight like never before. Recent high-profile breaches have laid bare a troubling reality: it’s often not the direct controls of a company that falter but those within its supply chain. Third-party vendors, integral to operations, can inadvertently become the weakest link, emphasizing the critical importance of rigorous vendor assurance programs.

For any organization, knowing your vendors — how they serve your business, what data they handle, and the level of access they possess — is non-negotiable. Yet, despite the plethora of tools available, finding one that offers the granularity and depth needed for a comprehensive vendor assurance program can be challenging and somewhat costly. This tool can be used as a precursor to tracking in a specialized tool like Upguard or SecurityScorecard. Also, many of the automated compliance tools included a vendor management component.

That’s why I decided to take matters into my own hands. I developed a Google Form tailored for vendor assurance that covers all the essential fields required for a robust evaluation. The field list can be used as a comparison against commercially available tools or it can be used by smaller organizations to create a vendor assurance program without needing to spend money on tooling for this purpose.

Vendor assurance is not just a checkbox for compliance; it’s a strategic imperative. With the right approach and tools, you can protect your business from becoming a cautionary tale in the cybersecurity annals.

In this shared folder you’ll find the vendor tracker form that you can copy to your own Google Drive.

I recommend watching the YouTube recording of how the form works, including how to set up the additional “smart” fields that I have added to the spreadsheet to enhance the form.

Field Breakdown

Vendor Name: A starting point for vendor identification.
Vendor Service Description: What services do they provide? This helps in understanding the vendor’s role and the potential impact on your operations.
Vendor Contact Info: Critical for maintaining communication channels. Example: sales contact, support contact – emails and phone numbers where applicable.
Vendor Website & Security Page: The vendor’s main page and public-facing page describing their commitment to security.
Vendor Status: Keeps track of active engagements only.
Default Group Access: Outlines which access groups within your organization can interact with the vendor’s systems or data. This field is especially helpful when onboarding new employees and can be used for automated creation of access grant tickets when a new employee joins the organization. Automating creation of this list will be covered in part 2 of this blog post.
Customer Data
- Secret | Confidential – PII or other Customer Confidential data, passwords
- Publicly Available – Names, emails, phone numbers which may have privacy implications but not specific security concerns.
Internal Data: Identifies whether vendors have access to sensitive information.
- Company Internal | IP
- Company not-sensitive
- Employee Information
Compliance Requirements: What compliance related data does the vendor hold
- PII – HIPAA | PCI
- Privacy – GDPR
Subprocessor: This field specifically relates to GDPR and can help in generating a subprocessor list as required by this compliance standard.
Operational Risk Rating: Operational risk refers to the impact on your business should the vendor go down, fail in regards to data integrity, or need to be replace. How dependent is your business on this vendor?
Determined Risk Ratings: This is a risk level that combines Operational Risk Rating and the data and compliance impacts already identified. There is a script based “Calculated Risk Rating” described below that can be used to ensure that overall risk is not underestimated.
Vendor Owner/Admins: This is a grid control that combines a list of employees as rows and a list of roles as columns.
- At a minimum, I have found it helpful to know Owner and Admins.
  - Owner – who owns the vendor and is ultimately responsible for: access reviews, checks on vendor SLA compliance, annual refreshing of this list and any associated compliance documents.
  - Admin – who has admin access to the tool. This is helpful when trying to get someone access and as a backup for the owner.
- Optionally, you can use this section to keep track of user grants by adding all employees and additional roles. I have found this to become unwieldy if you have more than ~15 employees.
- In Part 2 of this blog, I’ll review how to automatically update these values using a script.
Contract Renewal & Last Review Date: Helps in keeping the administrative aspects of the vendor relationship up to date. Reviews of this data and refresh of the assurance documents should happen at least annually. You can schedule each vendor review separately or update all records in the same time peried – i.e. March is vendor review month.
Reviewer: Email of the person conducting the latest assessment; the one filling out the form and updating the information.
Vendor Assurance Documents: A repository for all relevant documents related to vendor security which may include security questionnaires and/or standard framework compliance documents such as: SOC 2, ISO 27001. Don’t develop your own questionnaire but look to existing standards like Common Control Matrix (formerly CAIQ) from CSA or the Standardized Information Gathering (SIG) Questionnaire from Shared Assessments.

Getting Started with the Google Form

After you have copied over the Google form from the shared drive. you will need to click through to create the necessary subfolder which will hold any attached compliance documents.

Open the form and click on the responses tab and then “Link to Sheets”

Optional Helper Fields

To generate the extra recommended data in the responses spreadsheet open the resulting sheet and add the following three columns as the last 3 columns:

Calculated Risk Rating – Recommended risk rating based on Internal Data, Customer Data, and Compliance Data choices
Edit URL – Link that can be used to change responses for the vendor without having to scroll through all the items in the form. It also reduces the risk of creating duplicate records in the data.
Response ID – Can help with automations but is not required.

Leaving any of these columns off the responses sheet will keep them from being generated. If you ever want to have all the data regenerated, just delete the data on the sheet for those three columns.

NOTE: ANY DATA tied to directly to fields on the form CANNOT be changed in the spreadsheet. The data will NOT be refreshed in the form. You must use the Edit URL or find the record in the form in order to make changes.

To populated these fields, navigate to the script using the menu under the three ellipsis as the very top of the form page:

In the script editor (as described in the YouTube walkthrough) you select the “formSubmitted” method and click run to get the 3 extra fields populated in the spreadsheet.

If you want this script to run every time the form is submitted, use the trigger option in the left menu.

In part 2, we’ll walk through how to customize the choices on the form itself to automatically populated with data specific to your organization. You can also just change the values on the form manually for Vendor/Owner and Default Group Access fields.

Script Code

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


/*
 * Global Variables
 */

// Form information - should not change
const vendorForm = FormApp.getActiveForm()
const columnCustomer = "Customer Data"
const columnInternal = "Internal Data"
const columnCompliance = "Compliance requirements"
const sheetName = 'Form Responses 1';
const startRow = 1;
/*
 * Name of the columns to hold extra data.
 *  If you do not create the columns in your spreadsheet 
 *  with these exact names, no data will be populated!!!!!
 */
const columnNameURL = "Edit URL";
const columnNameCalcRR = "Calculated Risk Rating";
const columnNameID = "Response ID"

function formSubmitted() {
  var ssapp = SpreadsheetApp.openById(vendorForm.getDestinationId())
  var activeSheet = ssapp.getSheetByName("Form Responses 1")
  var headers = activeSheet.getRange(1, 1, 1, activeSheet.getLastColumn()).getValues(); 

  //this code block adds the edit url into the "Edit URL" column
  var columnURLIndex = headers[0].indexOf(columnNameURL);
  var data = activeSheet.getDataRange().getValues();
  for(var i = startRow; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnURLIndex] == '') {
      var timestamp = data[i][0];
      var formSubmitted = vendorForm.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      activeSheet.getRange(i+1, columnURLIndex+1).setValue(editResponseUrl);
    }
  }

  /* This code sets a risk rating based on the data types selected in the form
      Customer Data
      Internal Data
      Governance Requirements
  */
  var columnRRIndex = headers[0].indexOf(columnNameCalcRR);
  var data = activeSheet.getDataRange().getValues();
  for(var i = startRow; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnRRIndex] == '') {
      var timestamp = data[i][0];
      var formSubmitted = vendorForm.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var customerDataResponse = ''
      var internalDataResponse = ''
      var complianceResponse = ''
      // Iterates over the responses.
      // Gets the item responses from each form response.
      const itemResponses = formSubmitted[0].getItemResponses();
        // Iterates over the item responses.
      for (const itemResponse of itemResponses) {
        if (itemResponse.getItem().getTitle() == columnCustomer) customerDataResponse = itemResponse.getResponse();
        else if (itemResponse.getItem().getTitle() == columnInternal) internalDataResponse = itemResponse.getResponse();
        else if (itemResponse.getItem().getTitle() == columnCompliance) complianceResponse = itemResponse.getResponse();

        // Logs the items' questions and responses to the console.
        //console.log("Response to the question" + itemResponse.getItem().getTitle() +  " was " + itemResponse getResponse());
      }
      //set the base risk rating
      var rating = 'Informational'
      //check data in the three data columns to determine risk
      if (complianceResponse !== '' || customerDataResponse !== '' || internalDataResponse !== '') {
        //Logger.log(complianceResponse + " and " + customerDataResponse + " and " + internalDataResponse)
        if (complianceResponse.includes("PII - HIPAA | PCI") || internalDataResponse.includes("Employee Information")) {
          rating = "Critical"
          //Logger.log("rating = " + rating)
        } else {
          if (customerDataResponse.includes("Secret | Confidential") ||internalDataResponse.includes("Company Internal | IP") || complianceResponse.includes("Privacy - GDPR")) {
            rating = "High"
            //Logger.log("rating = " + rating)
          } else {
            if (customerDataResponse.includes("Publicly Available") ){
              rating = "Medium"
              //Logger.log("rating = " + rating)
            } else if (internalDataResponse.includes("Company not-sensitive")) rating = "Low";
          }
        }
      }
      //save the rating to the spreadsheet in the Calculated Risk Rating
      activeSheet.getRange(i+1, columnRRIndex+1).setValue(rating);

    }
  }

//This Code fills the response id in column named "Response ID"
  var columnResIDindex = headers[0].indexOf(columnNameID);
  var data = activeSheet.getDataRange().getValues();
  for(var i = startRow; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnResIDindex] == '') {
      var timestamp = data[i][0];
      var formSubmitted = vendorForm.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var resID = formSubmitted[0].getId();
      activeSheet.getRange(i+1, columnResIDindex+1).setValue(resID);
    }
  }      
}