Frage Wie extrahiere ich eine URL aus einem Link in Google Tabellen mithilfe einer Formel?


Ich habe von einer Website eine Reihe von Hyperlinks kopiert und in ein Google-Blatt eingefügt. Die Werte werden als verknüpfter Text und nicht als Hyperlinkformeln angezeigt und sind weiterhin korrekt verknüpft. Für jede Zeile versuche ich, die URL NUR (nicht den freundlichen Text) zu extrahieren und sie in die angrenzende Spalte einzufügen. Wie könnte dies mit einer Formel erreicht werden?

Beispielsweise:

=SOMEFUNCTION(cellThatHoldsLink, returnedURLOnly)

Dies und ähnliche Szenarien nicht anwenden, da die eingefügten Daten keine Formeln sind. Ich werde eine Skript (GAS) -Lösung oder irgendeine Lösung für diese Angelegenheit akzeptieren, würde es aber bevorzugen, wenn es mit einer Formel getan werden könnte. Ich habe dutzende von HYPERLINK-Manipulationsskripten gefunden, aber nichts zu diesem speziellen Szenario oder gar wie man auf die Eigenschaft zugreift, die diese URL enthält. Vielen Dank.


12
2018-02-05 18:10


Ursprung


Antworten:


Sie können importxml verwenden, um die gesamte Datentabelle und ihren Inhalt mit folgenden Informationen abzurufen:

=IMPORTXML(A1,"//tr")

daneben haben wir die URL-Strings in die Tags eingefügt

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

und schließlich verkettete die Zeichenfolgen mit der ursprünglichen Domäne, um Ihre Hyperlinks zu erstellen

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

0
2018-02-06 20:53



Wenn Sie Google Apps Script verwenden möchten, verwenden Sie die Funktion unten, um den Hyperlink von einem Text zu erhalten. Wenn Sie die Zelle übergeben, sollten Sie mit Doppelzitat senden. Z.B: =GETURL("A4") um den A4-Hyperlink zu erhalten.

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

Verweisen Hier beispielsweise.

Bearbeiten: Ignoriere diese Antwort. Dies funktioniert nur, wenn die URL in der Zelle verlinkt ist.


2
2018-02-06 13:25



Gefunden eine Antwort, die funktioniert in Google Groups (von Troja):

  1. Veröffentlichen Sie Ihre Tabelle (kopieren Sie die URL)
  2. Erstellen Sie eine neue Tabelle und verwenden Sie die IMPORTXML-Funktion (ersetzen Sie die URL durch die URL, die Sie in Schritt 1 erhalten haben)
  3. Sie erhalten die Daten - Sie können die Werte dann kopieren und einfügen, wo Sie sie benötigen
  4. Aufheben der Veröffentlichung der Tabelle (wenn sie nicht öffentlich sein soll)

Könnte mit Script gemacht werden, aber ich habe jetzt keine Zeit. :)


1
2017-08-13 15:54



Das eingebaute TabellenApp Service scheint nicht zu unterstützen, solche URLs herausziehen, aber die "Fortgeschrittene" Blätter Dienst tut es.

Aktivieren Sie den Advanced-Tabellen-Dienst gemäß Google's Anweisungenund dann versuchen Sie diesen Code:

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

Dies schafft ein benutzerdefinierte Funktion namens EXTRACT_URL, die Sie mit einem Verweis auf die Zelle aufrufen sollten, die den Link enthält; beispielsweise, =EXTRACT_URL(B3).

Leider funktioniert es nicht sofort, denn der Advanced-Sheets-Service kann nicht direkt verwendet werden durch benutzerdefinierte Funktionen. Dieses Skript fügt also ein Menü mit dem Namen "URLs extrahieren" zur Menüleiste des Arbeitsblattes hinzu, mit einem Menüeintrag, der mit "Process = EXTRACT_URL (A1) formules" beschriftet ist. Wenn Sie darauf klicken, ersetzt es alle Verwendungen des EXTRACT_URL Funktion mit der URL selbst.


1
2018-03-22 06:43