CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Anaheim
Anaheim

Reputation: 41

Pulling data from an API using Google App Script

I'm hoping someone could help as I've spent the last day trying to figure out where I'm going wrong.. to no avail.

I'm trying to pull some basic information from https://nvd.nist.gov/ using their API (https://services.nvd.nist.gov/rest/json/cve/1.0/cve-id). I need to pull the data for cve-id's. cve-id's are in column A.

Example cve-id: https://services.nvd.nist.gov/rest/json/cve/1.0/CVE-2019-9763

The only information I want is the description, cvssV3baseScore, cvss3vectorString. Very basic you might think but I'm lost after doing tons of research.

I've tried doing this using the following code in Google Apps Script:

function fetchData() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("CVEs");

  var sheetData = sheet.getDataRange().getValues();


  var i, len = sheetData.length, row = [];

  for (i = 1; i < len; i++) {


    if (sheetData[i][0] == "" || sheetData[i][15] != "")
      continue;


    // sheetData[i][8] - here 8 represents column I as column A = 0. Column 23 = x, this is where sheet ends (at new Date())

    let url = 'https://services.nvd.nist.gov/rest/json/cve/1.0/' + sheetData[i][0];

    try {
      var id = json.result.CVE_items.cve.CVE_data_meta.ID;
      var idStr = '';

      var response = UrlFetchApp.fetch(url).getContentText();
      row.push([
        idStr,
        json.result.CVE_items.cve.CVE_data_meta.ID,
        json.result.CVE_items.cve.description.description_data.value,
          json.result.CVE_Items.impact.baseMetricV3.cvssV3.vectorString,
        json.result.CVE_Items.impact.baseMetricV3.cvssV3.baseScore,
      ]);

      //Here (middle number) 10 number denotes the exact column number from where we need to write data. 10 = J
      sheet.getRange(i + 1, 2, 1, row[0].length).setValues(row);
    }
    catch (e) {
      continue;
    }
  }
}

function lookupByNthValue(search_key, sourceColumn, targetColumn, n) {
  if(arguments.length < 4){
    throw new Error( "Only " + arguments.length + " arguments provided. Requires 4." );
  }
  var count = 1;
  for(var i = 0; i < sourceColumn.length; i++){
    if(sourceColumn[i] != search_key){
      continue;
    }
    if(count == n){
      return targetColumn[i];
    }
    count++;
  }
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Actions")
    .addItem("Fetch Data", 'fetchData')
    .addToUi();
}

I would much appreciate if someone could help, link to the Google sheet: https://docs.google.com/spreadsheets/d/18ilCE1udUM87c4YtxXAB52Fm3mEzDk8UggcH96S3JLA/edit?usp=sharing

Thank you in advance.

Upvotes: 0

Views: 442

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15308

There are a few problems: len equals 1000 because you have data on row 1000! therefore the script will be too long. You write CVE_items instead of CVE_Items with a capital letter! CVE_Items is not a parent, it is an array, even with a single occurrence. You never parse the response of url fetch. Try this

function fetchData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("CVEs");
  var sheetData = sheet.getDataRange().getValues();
  var i, len = sheet.getLastRow(), row = [];
  Logger.log(len)
  for (i = 1; i < len; i++) {
    try{
      let url = 'https://services.nvd.nist.gov/rest/json/cve/1.0/' + sheetData[i][0];
      var json =  JSON.parse(UrlFetchApp.fetch(url).getContentText())
      var row=[]
      row.push([
        json.result.CVE_Items[0].cve.CVE_data_meta.ID,
        json.result.CVE_Items[0].cve.CVE_data_meta.ID,
        json.result.CVE_Items[0].cve.description.description_data.value,
          json.result.CVE_Items[0].impact.baseMetricV3.cvssV3.vectorString,
        json.result.CVE_Items[0].impact.baseMetricV3.cvssV3.baseScore,
      ]);
      Logger.log(row)
      sheet.getRange(i + 1, 2, 1, row[0].length).setValues(row);
    }catch(e){}
  }
}

Upvotes: 2

Related Questions