Get country name from IP range with google script sheets and paste the result into another column
In my google spreadsheet, in column H, I have a list of IP addresses. What do I need for a script that will take these numbers and get the country name. I need this to be a script as the spreadsheet is being populated from a web form. I have a formula that does this perfectly, but I need to copy the formula manually each time the form is submitted. Since I receive a lot of messages a day, it is tedious to do it manually. I need a script to trigger an update of a spreadsheet. My formula is:
=query( importhtml("http://whatismyipaddress.com/ip/" & H1526, "table", 2), "select Col2 where Col1 = 'Country:' ", 0 )
The last filled line is H1526. I tried "ARRAYFORMULA" but the array formulas don't seem to work with my query function. The column where I have the IP has an H and I need the country name in column I.
source to share
You can write a script when submitting a google form. Also you need to set a trigger for the same. Hope this helps.
function OnSubmit(e){
var sheetDatabase = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheetDatabase.getRange(sheetDatabase.getLastRow(), 8).setFormula('=query(importhtml("http://whatismyipaddress.com/ip/"&I'+sheetDatabase.getLastRow()+', "table", 2), "select Col2 where Col1 = \'Country:\' ", 0 )');
}
source to share