How to convert IP Address to Country Name

Question

URl - https://docs.google.com/spreadsheets/d/1lvmwHqINzKxjKgZjmDAkntFDo2R_lrfy2GUd-ohCA2A/edit#gid=1237083423

As you can see there is a column which displays IP Address. Basically it is a survey form (JotForm) whose responses are real-time updated on Google spreadsheets.

Based on IP Address available, how can I find its respective country name.

I got one solution

=query( importhtml("http://whatismyipaddress.com/ip/" & G4, "table", 2), "select Col2 where Col1 = 'Country:' ", 0 )

But in this I am facing one issue. I am using jotforms and integrating it with Google Spreadsheets. It gives me location of all responses received till date. I used drag down feature and dragged it to cell no 100. As as soon the new response is received, the information is now stored at cell no 101 and all the other where I have entered formula are left blank.

Any workaround for this? Will any array formula will do?

Is there any other way to do it?

Answer 1

Your formula seems to be working well. Unfortunately, I have not found an easier way for this using arrayformula(). But your question regarding how to make this update auto-magically is very doable. The following will assume you have placed your formula in cell G2. (You might find that you will want to use this in other places too, in which case you will just create different functions to make it work and put them in onChange()).

1. Open up your script editor. Tools->Script Editor

2. Add the following code to the end of the script editor:

   function updateCountry(){
     var mysheet = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = mysheet.getSheetByName("Submissions");  //Sheet Name is "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, Last row currently populated, 1 Column in Range
     sourceCell.copyTo(targetRows); //Will copy the formula from sourceCell to targetRows.
   
   }
   
   function onChange(){
     updateCountry(); //Run this on every change.
   }
   

3. Save the edited script file as "CCI Responses".

4. Now inside the script editor go to Resources->Current Projects Triggers. Use the pull downs to produce the following settings and save:

5. Now see what happens when a new IP is added to column G.