Create function in Google sheet to get my external IP address

Question

I need to create a function in Google sheet to get my external (public) IP address

I tried use function =IMPORTXML("https://api.myip.com","//body"), but this method shows diffrint IP address not my external IP address

well the reason the IP is different is because you are getting the IP of Google Sheets location — player0, Sep 30 '19 at 11:22
@player0 you are correct. Could you please write it as an answer? — Jescanellas, Sep 30 '19 at 11:37
Is there another way to get my external IP address like creating a new formula through vbs .. Thanks for your interest — ammaeln, Sep 30 '19 at 12:21
Would a custom menu or a button work? Found this resource (https://www.ipify.org/) that should help with these solutions. — Sourabh Choraria, Oct 03 '19 at 08:44
@ammaeln - did you get a chance to review my code? Was wondering if it suited your use case or if you found a better / different way to achieve this. — Sourabh Choraria, Oct 08 '19 at 15:26
@SourabhChoraria - Thank you so much for your interest ... Your answer is the best I got so far .. If this method could be done automatically in a specific cell (A1) every minute — ammaeln, Oct 08 '19 at 17:08
@ammaeln - sadly, that's the challenge. Any automation would require Google's servers to trigger the function and you'd not be able to get the "external" IP address :( To clarify here, the process can be automated (easily so) but given the restrictions of the platform, you would NOT get the external IP, without an intervention from the client-side of the code (i.e. via the browser). A rather dummy hack could be for a browser macro to keep clicking a custom menu every minute, such that the IP gets updated in cell A1 accordingly (this part should be easy to code as well). — Sourabh Choraria, Oct 08 '19 at 17:23

score 5 · Answer 1 · answered Sep 30 '19 at 11:51

5

the reason the IP is different is because you are getting the IP of Google Sheets location not your IP

answered Sep 30 '19 at 11:51

player0

124,011
12
67
124

1

Thanks for your interest ... Is there another way to get my ip external address and show the result in the google sheet – ammaeln Oct 01 '19 at 21:38
I am not aware of any, but I am interested in solution too so I will bounty your question – player0 Oct 02 '19 at 17:15
@player0 - how about your needs; would a custom menu or a button work? Have asked the same on OP too. – Sourabh Choraria Oct 03 '19 at 08:52
@SourabhChoraria not sure about OP needs, but I guess plain working script/function would be sufficient (at least for me). but feel free to present a custom menu or a button or both if you know to solve this OP's question – player0 Oct 03 '19 at 12:13
@player0 - thanks :) Have tested & shared my answer now. – Sourabh Choraria Oct 03 '19 at 13:49

Sourabh Choraria · Accepted Answer · 2019-10-03T15:07:50.443

The following solution makes use of a custom menu in the Spreadsheets -

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('My Menu')
      .addItem('Get IP', 'getIP')
      .addToUi();
}

function getIP() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().appendRow([JSON.parse(UrlFetchApp.fetch('https://api6.ipify.org?format=json')).ip]);
}

You're free to modify the script to place said IP anywhere in the sheet, as required.

Also, I'm making use of the IPv6 address, as opposed to IPv4 but should you want to switch it to IPv4, replace the URL from the code to https://api.ipify.org?format=json - you may find this resource here.

I've asked out & around and this cannot (in any way) be achieved via a custom formulae, as such formulas run within a wrapper of sorts (that do not interact with any client-side elements). Hope this helps.

Edit note

Adding a way to insert external IP using custom menu to the specific cell (current cell, to be precise) -

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu('My Menu')
  .addItem('Get IP', 'getIP')
  .addToUi();
}

function getIP() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var currentCell = sheet.getCurrentCell();
  var ip = JSON.parse(UrlFetchApp.fetch('https://api6.ipify.org?format=json')).ip;
  currentCell.setValue(ip)
}

By using this method, the IP would be added to the cell that has been selected.

You may wonder why current cell was chosen instead of active cell - well, the answer to that is because the document prefers us to do so :) I bet it would work even if we were to use active cell (haven't tested that though but I don't see a reason why it wouldn't)

"this cannot (in any way) be achieved via a custom formulae, as such formulas run within a wrapper of sorts (that do not interact with any client-side elements)" - by that you mean that this can't be called without the Menu? I obviously tried it: https://i.stack.imgur.com/0ZhjM.png and got the error but not sure if that error is the result of your last sentence... anyway my question is (if I may) could this be modded in such way that it would inject the IP into **selected** cell instead of "last free cell after dataset of column A". — player0, Oct 03 '19 at 14:29
example: I select D9 go to My Menu > Get IP > "ip gets injected into D9 cell" — player0, Oct 03 '19 at 14:29
@player0 - So, a function can be called without the menu (not the one I've shared in my answer as it has been modelled to `appedRow` but a general custom function can still be invoked but it wouldn't give you the external IP address. My response however can be modelled to inject the IP in the specified cell (say, D9) on further customisation - let me do that too and append it to my original response. — Sourabh Choraria, Oct 03 '19 at 14:59
@player0 - have updated my answer to accommodate your example scenario. — Sourabh Choraria, Oct 03 '19 at 15:08

score 1 · Answer 3 · answered Oct 03 '19 at 14:03

It's not possible to use a Google Sheets custom function or Google Apps Script server side address to get you external IP because the related code is executed on the server side and Google Apps Script services doesn't include methods to get that but you could use client-side code to the get the external IP address. Additional, if it is required to send the IP address to an spreadsheet, the you could use do that by using google.script.run or the Google Sheets API.

NOTE: The closest Google Apps Script classes are Class Session and Class User.

How to get client's IP address using JavaScript?

References

https://developers.google.com/apps-script/reference

Create function in Google sheet to get my external IP address

3 Answers3

Edit note

Linked