在Google工作表中创建函数以获取我的外部IP地址 [英] Create function in Google sheet to get my external IP address

问题描述

我需要在Google工作表中创建一个函数来获取我的外部(公共)IP地址

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

我尝试使用功能=IMPORTXML("https://api.myip.com","//body")，但是此方法显示的是不同的IP地址，而不是我的外部IP地址

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

推荐答案

以下解决方案利用了自定义菜单-

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]);
}

您可以根据需要随意修改脚本，以将所述IP放置在工作表中的任何位置.

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

此外，我使用的是IPv6地址，而不是IPv4，但是如果您要将其切换到IPv4，请将URL从代码替换为https://api.ipify.org?format=json-您可能会发现此资源

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.

添加一种使用自定义菜单将外部IP插入特定单元格(准确地说是当前单元格)的方法-

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)
}

使用此方法，会将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)

这篇关于在Google工作表中创建函数以获取我的外部IP地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！