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 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();
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:
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
Post a Comment