Как извлечь URL из ссылки в Google Таблицах, используя формулу?

Я скопировал с веб-сайта ряд гиперссылок и вставил их в лист google. Значения отображаются как связанный текст, а не гиперссылки, и все еще связаны правильно. Для каждой строки я пытаюсь извлечь URL ТОЛЬКО (не дружественный текст) и вставить его в соседний столбец. Как это можно осуществить с помощью формулы?

Например:

=SOMEFUNCTION(cellThatHoldsLink, returnedURLOnly)

Этот и подобные сценарии не применяются, поскольку вставленные данные не являются формулами. Я приму решение script (GAS) или любое решение в этом отношении, но предпочел бы, можно ли это сделать с помощью формулы. Я нашел десятки скриптов манипуляции HYPERLINK, но ничего в этом конкретном сценарии или даже о том, как получить доступ к свойству, содержащему этот URL-адрес. Спасибо.

Ответ 1

Вы можете использовать importxml, чтобы вытащить всю таблицу данных, и она содержит следующее:

=IMPORTXML(A1,"//tr")

рядом с ним мы потянули строки url в тегах

=IMPORTXML(A1,"//tr/td[1]/a/@href")

и, наконец, конкатенировать строки с исходным доменом для создания ваших гиперссылок

=ARRAYFORMULA("http://www.bnilouisiana.com/"&INDIRECT("A2:A"&COUNTA(A2:A)))

Ответ 2

Если вы счастливы использовать Google Apps Script, то используйте функцию ниже, чтобы получить гиперссылку из текста. Когда вы передаете ячейку, вам следует отправить двойную кавычку. Например: =GETURL("A4"), чтобы получить гиперссылку A4.

function GETURL(input) {
  var range = SpreadsheetApp.getActiveSheet().getRange(input);
  var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];
  return url;
}

Обратитесь здесь, например.

Изменить: игнорировать этот ответ. Это будет работать, только если url связан в ячейке.

Ответ 3

Нашел ответ, который работает в Google Groups (от Troy):

Опубликуйте свою электронную таблицу (скопируйте URL-адрес)
Создайте новую таблицу и используйте функцию IMPORTXML (замените URL-адрес URL-адресом, который вы получите на шаге 1)
Вы получите данные - вы можете скопировать и вставить значения, в которых они вам нужны.
Отменить публикацию электронной таблицы (если вы не хотите ее публиковать)

Может быть сделано Script, но у меня нет времени.:)

Ответ 4

Похоже, что встроенная служба SpreadsheetApp не поддерживает извлечение таких URL-адресов, а служба "Расширенные" Sheets.

Включите услугу Advanced Sheets в соответствии с инструкциями Google, а затем попробуйте этот код:

function onOpen() {
  var menu = SpreadsheetApp.getUi().createMenu("Extract URLs");
  menu.addItem("Process =EXTRACT_URL(A1) formulas", "processFormulas");
  menu.addToUi();
}

function EXTRACT_URL() {
  return SpreadsheetApp.getActiveRange().getFormula();
}

function processFormulas() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var rows = sheet.getDataRange().getFormulas();
  for (var r = 0; r < rows.length; r++) {
    var row = rows[r];
    for (var c = 0; c < row.length; c++) {
      var formula = row[c];
      if (formula) {
        var matched = formula.match(/^=EXTRACT_URL\((.*)\)$/i);
        if (matched) {
          var targetRange = matched[1];
          if (targetRange.indexOf("!") < 0) {
            targetRange = sheet.getName() + "!" + targetRange;
          }
          var result = Sheets.Spreadsheets.get(spreadsheet.getId(), {
            ranges: targetRange,
            fields: 'sheets.data.rowData.values.hyperlink'
          });
          try {
            var value = result.sheets[0].data[0].rowData[0].values[0].hyperlink;
            sheet.getRange(r + 1, c + 1).setValue(value);
          } catch (e) {
            // no hyperlink; just ignore
          }
        }
      }
    }
  }
}

Это создает пользовательскую функцию с именем EXTRACT_URL, которую вы должны вызывать со ссылкой на ячейку, содержащую ссылку; например, =EXTRACT_URL(B3).

К сожалению, это не работает сразу, потому что сервис Advanced Sheets не может напрямую использоваться пользовательскими функциями. Таким образом, этот сценарий добавляет меню "Извлечение URL" в строку меню электронной таблицы с одним элементом меню, помеченным как "Process = EXTRACT_URL (A1) формул". Когда вы щелкните по нему, он заменит все виды использования функции EXTRACT_URL на сам URL.

Ответ 5

Я смог решить эту проблему для Jotform очень простым способом.

Я хотел включить ссылку "Изменить" в запрос, но она выдаст мне только текст "Изменить представление".

Однако я заметил, что у меня есть идентификатор формы в столбце R. Затем я смог скопировать ссылку JotForm и объединить ее со ссылкой на ячейку " https://www.jotform.com/edit/ " & R2.