Блог

У меня есть лист, где в одной колонке содержится идентификатор билета Jira.

Я хотел бы автоматически преобразовать это в ссылку на билет на основе введенного мной значения.

Например, я введу SD-1234 в столбец, и я хотел бы, чтобы затем он превратился в кликабельную ссылку на https://demo.atlassian.net/browse/SD-1234/, но не показывать URL-адрес в ячейке, а исходное значение, которое я ввел (SD-1234).

Колонка всегда E, если это помогает. Кто-нибудь может дать мне представление о том, как написать это в редакторе сценариев?

Например, когда используется ячейка «E1» SD-1234 , =HYPERLINK("https://demo.atlassian.net/browse/"amp;E1, E1) значение SD-1234 имеет гиперссылку https://demo.atlassian.net/browse/SD-1234 . Но в этом случае результат не может быть непосредственно показан в ячейке «E1». Если вы хотите напрямую преобразовать SD-1234 ячейку «E1» в SD-1234 с гиперссылкой https://demo.atlassian.net/browse/SD-1234 , как насчет следующего примера сценария?

Пример сценария:

Пожалуйста, скопируйте и вставьте следующий сценарий и запустите функцию в редакторе сценариев. Прежде чем использовать это, пожалуйста, укажите имя листа.

Этот пример сценария преобразуется из SD-1234 столбца «E» в =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);
}
 

Этот пример сценария преобразуется из SD-1234 столбца «E» в SD-1234 с гиперссылкой https://demo.atlassian.net/browse/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);
}
 

Примечание:

• Если в вашей электронной таблице есть 1-я строка заголовка, пожалуйста, измените "E1:E" sheet.getLastRow() ее на "E2:E" sheet.getLastRow() .

Рекомендации:

• карта()
• Класс RichTextValueBuilder
• setRichTextValues(значения)

Добавлено 1:

Из ваших следующих комментариев,

Нужно ли мне указывать имя листа? У меня есть несколько листов, к которым я всегда хочу применять эту функцию (в настоящее время в файле более 20 листов).

Кроме того, я ввел это в редактор сценариев для тестирования, и что мне теперь делать? Кроме этого, я ничего не вижу… и это не написано на моем листе. Как я могу применить это к своему листу, чтобы автоматически запускаться всякий раз, когда значение вводится в ячейку?

Когда описанные выше ситуации отражены в сценарии, он становится следующим.

Пример сценария:

Пожалуйста, скопируйте и вставьте следующий сценарий в редактор сценариев и сохраните его. И, пожалуйста, отредактируйте колонку «Е» листа. При этом в ячейке появляется гиперссылка с введенным текстом.

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