Tuesday, March 18, 2014

Google Apps Script: Auto-Query FortiGuard Category List

In case someone is curious, here's a little Google Apps Script I put together for querying FortiGuard's Web Filtering Service site category list from a spreadsheet. It takes a URL from the selected cell in a Google Spreadsheet, and FortiGuard's category for that URL in the cell to the right of it.

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "pasteCategory", functionName: "pasteCategory"},
                      {name: "Say Hello", functionName: "sayHello"} ];
  sheet.addMenu("FortiGuard", menuEntries);
}

function pasteCategory() { //you can also set this to loop for the number of rows
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = SpreadsheetApp.getActiveRange();
  var newRange = range.offset(0, 1); //the cell to the right
  var site = range.getValue();//from the currently selected cell
  var category = getCategory(site);
  newRange.setValue(category) //paste in the category
  var newSelection = range.offset(1, 0);
  newSelection.activate();
}

function sayHello() {
  Browser.msgBox("Hello");
}

function getCategory(site) {
  var urlToFetch = "http://www.fortiguard.com/ip_rep/index.php?data="+site
  var html = UrlFetchApp.fetch(urlToFetch).getContentText();
  var startTag = 'Category: ';
  var endTag = '</h3>';
  var startIndex = html.indexOf(startTag) + 10; // add 10 to the index to get rid of 'Category: '
  var endIndex = html.indexOf(endTag);
  var category = html.slice(startIndex,endIndex);
  return(category);
}

1 comment:

David Hay said...

To loop through the rows, you could include a while loop around the code inside the pasteCategory function. Either while i < 10 where you manually set the number to be one more than the number of rows you want to parse or https://developers.google.com/apps-script/reference/spreadsheet/sheet#getLastRow()

And don't forget to i++; to increment the i variable. http://www.w3schools.com/js/js_loop_while.asp