英文:
Google Sheets Auto HyperLink to Google Earth Web?
问题
I understand your request. Here's the translated content without the code:
我在想是否有一种简单的方法可以将包含地址的列转换为Google Earth Web上的超链接。
如果您尝试访问https://earth.google.com/web/search/New York，它将显示New York，这意味着我需要以某种方式使用https://earth.google.com/web/search/和单元格中的self_cell_value，当然，超链接的文本可以是单元格内的文本（地址）。
有什么简便的方法可以做到这一点吗？Arrayformula等需要放在另一列/单元格上，这不是我想要的。只需将地址列转换为如上所述的超链接。在添加新地址时，它应该自动发生。
谢谢。
英文:
Was wondering if there's simple way to convert column which is full with addresses to hyperlink for Google Earth web.
If you try https://earth.google.com/web/search/New York it will show New York, which means
that I need https://earth.google.com/web/search/ & self_cell_value somehow, of course the text of the hyperlink can be whatever the text (address) is inside the cell.
Any tips how to do this easy ? Arrayformula and stuff need to be placed onto another column/cells, which is something that I don't want. Just the column of addresses converted into hyperlinks as mentioned. Upon adding new addresses it should be automatically happening.
Thanks
答案1
得分: 1
这是一个示例脚本，当编辑 Sheet1 中的列 A 时，将触发脚本，并将值中的空格替换为 +。
所以 https://earth.google.com/web/search/New York 将自动变为 https://earth.google.com/web/search/New+York。
您可以根据需要编辑列号或工作表名称。
英文:
This is a sample script that will trigger when column A in Sheet1 is edited, it will take the values and replace the space with +.
So https://earth.google.com/web/search/New York will change to https://earth.google.com/web/search/New+York automatically.
You can edit the column number or the sheet name as required.
function onEdit(e) {
// get the range where the edit once done
let range = e.range
// get the column number where the changes is done
let working_range = range.getColumn()
// the Sheet name where the change is done
let sheet = e.source.getSheetName();
// if statement, if the change was done in Sheet1
//and column 1 (column A), the script will trigger
if (sheet == 'Sheet1' & working_range==1){
// get the range value in column A
let cell_value = range.getValue().toString();
// replace the space with a +
let new_cell_value = cell_value.replace(' ', '+')
//sets the hyperlink
let richValue = SpreadsheetApp.newRichTextValue()
.setText(new_cell_value)
.setLinkUrl(new_cell_value)
.build();
range.setRichTextValue(richValue);
}
}
This is how it works:
<h3> Update: </h3>
If you add, just one word instead of a URL, you can make this changes to the code to create the hyperlink:
function onEdit(e) {
// get the range where the edit once done
let range = e.range
// get the column number where the changes is done
let working_range = range.getColumn()
// the Sheet name where the change is done
let sheet = e.source.getSheetName();
// if statement, if the change was done in Sheet1
//and column 1 (column A), the script will trigger
if (sheet == 'Sheet1' & working_range==1){
// get the range value in column A
let cell_value = range.getValue().toString();
// replace the space with a +
let new_cell_value = cell_value.replace(' ', '+')
//sets the hyperlink with the current value in the column
// and the URL to Google Earth withut the space
let richValue = SpreadsheetApp.newRichTextValue()
.setText(cell_value)
.setLinkUrl("https://earth.google.com/web/search/" + new_cell_value)
.build();
range.setRichTextValue(richValue);
}
}
Reference:
答案2
得分: 0
你可以尝试以下公式和下拉菜单：
="https://earth.google.com/web/search/"&C2
或者
你可能想要使用范围：
=INDEX(IF(B6:B="",,$A$5&B6:B))
无论如何，具有多个单词的位置都必须使用+符号连接。
（根据您的范围和区域）请调整公式。
英文:
You can try the following formula and a drop-down
="https://earth.google.com/web/search/"&C2
OR
You may want to use a range
=INDEX(IF(B6:B="",,$A$5&B6:B))
In any case, locations having more than 1 word must be joined using the + sign
(Do adjust the formula according to your ranges and locale)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论