如何自动将列值在谷歌表转换成基于该值的超链接?

Question

我有一个表，其中一列包含Jira票据的ID。

我想自动转换为一个链接到票,我进入基于价值。

。我将在列中输入SD-1234，然后我希望它变成一个可点击的链接，链接到https://demo.atlassian.net/browse/SD-1234/，但不是在单元格中显示URL，而是我输入的原始值(SD-1234)。

如果有用的话，列总是E。有人能给我一个良好的开端，如何在脚本编辑器这个脚本?

Answer 1

例如，当单元格"E1"有SD-1234，使用=HYPERLINK("https://demo.atlassian.net/browse/"&E1, E1)，取值为SD-1234，超链接为https://demo.atlassian.net/browse/SD-1234。但是在这种情况下，结果不能直接显示在单元格"e1"中。当您要直接转换单元格"E1"中的SD-1234时使用https://demo.atlassian.net/browse/SD-1234的超链接到SD-1234，下面的示例脚本如何?

示例脚本:

请复制粘贴以下脚本，并在脚本编辑器中运行该函数。在使用此功能之前，请设置工作表名称。

这个示例脚本从"E"列中的SD-1234转换为to=HYPERLINK("https://demo.atlassian.net/browse/SD-1234", "SD-1234").

function sample1() {
const sheetName = "Sheet1"; // Please set the sheet name.
const baseUrl = "https://demo.atlassian.net/browse/";
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const range = sheet.getRange("E1:E" + sheet.getLastRow());
const values = range.getValues().map(([e]) => [`=HYPERLINK("${baseUrl}${e}", "${e}")`]);
range.setFormulas(values);
}

这个示例脚本从"E"列中的SD-1234转换为使用https://demo.atlassian.net/browse/SD-1234的超链接到SD-1234。在本例中，不使用公式。

function sample2() {
const sheetName = "Sheet1"; // Please set the sheet name.
const baseUrl = "https://demo.atlassian.net/browse/";
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const range = sheet.getRange("E1:E" + sheet.getLastRow());
const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl + e).build()]);
range.setRichTextValues(values);
}

注意:

• 如果您的电子表格有第一行标题，请将"E1:E" + sheet.getLastRow()修改为"E2:E" + sheet.getLastRow()。

引用:

• map ()
类RichTextValueBuilder
• setRichTextValues(值)

添加1:

从你下面的评论中，

我必须设置工作表名称吗?我有几个工作表，我总是希望这个函数应用于(我目前有超过20个工作表在文件中)。

另外，我已经将此输入脚本编辑器进行测试，我从这里做什么?我看不到任何挽救……它也没在我的床单上跑。如何将此应用于工作表，以便在单元格中输入值时自动运行?

当上述情况反映在脚本中时，它变成如下:

示例脚本:

请将以下脚本复制粘贴到脚本编辑器中并保存。并请编辑专栏";E"床单的。这样，单元格就有了带有输入文本的超链接。

function onEdit(e) {
const sheetNames = ["Sheet1", "Sheet2",,,]; // Please set the sheet names you want to run the script.
const column = 5; // Column E. From your question, this script run for the column "E".
const {range} = e;
const sheet = range.getSheet();
const baseUrl = "https://demo.atlassian.net/browse/";
if (!sheetNames.includes(sheet.getSheetName()) || range.columnStart != column) return;
const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl + e).build()]);
range.setRichTextValues(values);
}

如果要对所有工作表运行脚本，可以使用下面的脚本:

function onEdit(e) {
const column = 5; // Column E. From your question, this script run for the column "E".
const {range} = e;
const sheet = range.getSheet();
const baseUrl = "https://demo.atlassian.net/browse/";
if (range.columnStart != column) return;
const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl + e).build()]);
range.setRichTextValues(values);
}

添加2:

如果要将脚本运行到列"E"在一个脚本运行的所有表中，您还可以使用以下脚本。

示例脚本:

这个示例脚本为特定的表运行。

function myFunction() {
const sheetNames = ["Sheet1", "Sheet2",,,]; // Please set sheet names you want to run the script.
const baseUrl = "https://demo.atlassian.net/browse/";
SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
if (!sheetNames.includes(sheet.getSheetName())) return;
const range = sheet.getRange("E1:E" + sheet.getLastRow());
const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl + e).build()]);
range.setRichTextValues(values);
});
}

这个示例脚本在所有的工作表上运行。

function myFunction() {
const baseUrl = "https://demo.atlassian.net/browse/";
SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
const range = sheet.getRange("E1:E" + sheet.getLastRow());
const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl + e).build()]);
range.setRichTextValues(values);
});
}