google spreadsheet - How to convert IP Address to Country Name -

google spreadsheet - How to convert IP Address to Country Name -

url - https://docs.google.com/spreadsheets/d/1lvmwhqinzkxjkgzjmdakntfdo2r_lrfy2gud-ohca2a/edit#gid=1237083423

as can see there column displays ip address. survey form (jotform) responses real-time updated on google spreadsheets.

based on ip address available, how can find respective country name.

i got 1 solution

=query( importhtml("http://whatismyipaddress.com/ip/" & g4, "table", 2), "select col2 col1 = 'country:' ", 0 )

but in facing 1 issue. using jotforms , integrating google spreadsheets. gives me location of responses received till date. used drag downwards feature , dragged cell no 100. as new response received, info stored @ cell no 101 , other have entered formula left blank.

any workaround this? array formula do?

is there other way it?

your formula seems working well. unfortunately, have not found easier way using arrayformula(). question regarding how create update auto-magically doable. next assume have placed formula in cell g2. (you might find want utilize in other places too, in case create different functions create work , set them in onchange()).

open script editor. tools->script editor

add next code end of script editor:

function updatecountry(){ var mysheet = spreadsheetapp.getactivespreadsheet(); var sheet = mysheet.getsheetbyname("submissions"); //sheet name "submissions" var sourcecell = sheet.getrange(2,7,1,1); //row 2, column g, first row in g, 1 column in range var targetrows = sheet.getrange(3,7,sheet.getlastrow()-2, 1); //row 3, column g, lastly row populated, 1 column in range sourcecell.copyto(targetrows); //will re-create formula sourcecell targetrows. } function onchange(){ updatecountry(); //run on every change. }

save edited script file "cci responses".

now within script editor go resources->current projects triggers. utilize pull downs produce next settings , save:

now see happens when new ip added column g.

google-spreadsheet jotform