DeepL REST API per VBA und PQ

Moderator: ModerationP

DeepL REST API per VBA und PQ

Beitragvon DerHoepp » 07. Mär 2022, 14:40

Moin allerseits,

weil mich der folgende Beitrag im Access-Teil des Forums interessiert hat (viewtopic.php?t=876460) habe ich mich mal mit einer automatisierten API-Abfrage von DeepL in VBA und in PowerQuery beschäftigt. DeepL ist nach eigener Angabe der Marktführer für die automatisierte Übersetzung mit sogenannter künstlicher Intelligenz. Bevor das Nachfolgende funktioniert, muss man sich einen kostenlosen API-Key besorgen, der monatlich das übersetzen von bis zu 500.000 Zeichen erlaubt (das geht auf der oben verlinkten Seite).
Das schöne an der API ist, dass sie in der einfachsten Version fast keine Parameter kennt (außer ggf. Quellsprache, ggf. Zielsprache und natürlich den zu übersetzenden Text) und außer dem API-Key keine weitere Authentifizierung notwendig ist (man spart sich also den Umgang mit Cookies und Session-IDs.). Man muss lediglich darauf achten, dass die Quelltexte UTF-8-codiert sind. PowerQuery übernimmt die Codierung selbstständig. Für VBA habe ich in der Beispiellösung die Umwandlung per ADO-Stream gewählt. Lange Rede, kurzer Sinn. In VBA geht es so (Dabei steht der zu übersetzende Text in Zelle A1 des Blattes, in dessen Codebehind der Code steht, ausgabe zwei Zellen darunter; der API-Key ist eine Const):
Code: Alles auswählen
Option Explicit
Private Const APIKEY As String = "hier dann der API Key"
'API-Documentation: https://www.deepl.com/docs-api
Sub testen()
    Dim response As String
   
    Dim utf8text
   
    With CreateObject("ADODB.STREAM")
        .Open
        .Type = 2 'adTypeText
        .Charset = "iso-8859-1"
        .WriteText Me.Cells(1, 1).Text
        .Position = 0
        .Charset = "utf-8"
        utf8text = .readtext(9999)
        .Close
    End With
   
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "POST", "https://api-free.deepl.com/v2/translate?auth_key=" & APIKEY & "&source_lang=DE&target_lang=NL&text=" & utf8text
        .SetRequestHeader("Host") = "api-free.deepl.com"
        .SetRequestHeader("Content-Type") = "application/x-www-form-urlencoded"
        .Send
        response = .responsetext
    End With
   
    Me.Cells(2, 1) = Split(Split(response, """text"":""")(1), """")(0)
End Sub

Weil eine einfache Übersetzung ein sehr simples JSON-Objekt zurückgibt, habe ich das Ergebnis einfach mit Split getrennt.

Aus interesse habe ich das gleiche noch einmal in PowerQuery umgesetzt. Datenbasis ist hier eine Strg+T-Tabelle tbSources, in der der APIKEY, die Quellsprache SOURCELANG, die Zielsprache TARGETLANG sowie der zu übersetzende Text SOURCETEXT enthalten sind. Diese Tabelle kann einfach in Powerquery als abfrage eingelesen und um eine benutzerdefinierte Spalte ergänzt werden, die die folgende Function aufruft:
Code: Alles auswählen
(apikey as text,sourcelang as text,targetlang as text,totranslate as text) as record =>
let
    Quelle = Json.Document(Web.Contents("https://api-free.deepl.com/v2/translate?auth_key=" & apikey & "&source_lang=" & sourcelang &
                                        "&target_lang=" & targetlang & "&text=" & totranslate,
                                         [Headers=[#"User-Agent"="PowerQuery",  ContentType="application/x-www-form-urlencoded"]])),
    translations = Quelle[translations],
    translations1 = translations{0}
in
    translations1

Der Aufruf in der Ursprungsabfrage sieht dabei recht simpel so aus:
Code: Alles auswählen
let
    Quelle = Excel.CurrentWorkbook(){[Name="tbSources"]}[Content],
    #"Aufgerufene benutzerdefinierte Funktion" = Table.AddColumn (Quelle, "DeepL", each fnGetDeepL([APIKEY], [SOURCELANG], [TARGETLANG], [SOURCETEXT])),
    #"Erweiterte DeepL" = Table.ExpandRecordColumn(#"Aufgerufene benutzerdefinierte Funktion", "DeepL", {"text"}, {"DeepL.text"})
in
    #"Erweiterte DeepL"


Vielleicht kann das ja noch jemand gebrauchen.

Viele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 10441
Registriert: 14. Mai 2013, 11:08

Re: DeepL REST API per VBA und PQ

Beitragvon Ralf A » 07. Mär 2022, 22:24

...ich brauche es zwar momentan nicht, lese aber Deine qualitativ hochwertigen Beiträge immer wieder gern. Da kann man auch mal Danke sagen.

Also: Danke an @DerHoepp !
Ciao, Ralf
Wer glaubt, für ihn persönlich würde der Bremsweg nicht als Funktion proportional zum QUADRAT der Geschwindigkeit steigen, der ist halt nicht „frei“, sondern ein Narr.
Ralf A
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1276
Registriert: 17. Mär 2018, 11:47

Re: DeepL REST API per VBA und PQ

Beitragvon Zwenn » 08. Mär 2022, 18:40

Sehr gutes Posting derHöpp^^ Danke für die Info. Ich verwende meistens DeepL, habe mich aber noch nie mit der API beschäftigt.
Zwenn
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 114
Registriert: 16. Mär 2019, 11:18

Re: DeepL REST API per VBA und PQ

Beitragvon RTausD » 22. Jun 2022, 10:09

Hallo DerHoepp und alle anderen,

ich möchte mich dem Lob anschließen.

Ich habe das Makro getestet und festgestellt, dass DeepL in der kostenlosen Version ein Problem mit dem Zeichen & hat und den übersetzten Text bei 138 Zeichen abschneidet.
Außerdem hat mein Excel ein Problem mit dem Split-Befehl
Ich habe auch noch die Sprachen variabel gestaltet.

Anbei mein entsprechend geänderter Code

Code: Alles auswählen
    Quellsprache = "IT"
    Zielsprache = "DE"
   
    Mailtext = Replace(Range("E" & ActiveCell.Row).Text, "&", "[und]")
   
    With CreateObject("ADODB.STREAM")
        .Open
        .Type = 2 'adTypeText
        .Charset = "iso-8859-1"
        .WriteText Mailtext
        .Position = 0
        .Charset = "utf-8"
        Mailtext = .readtext(9999)
        .Close
    End With
   
    While InStr(Mailtext, Chr(10)) > 0
        With CreateObject("MSXML2.XMLHTTP.6.0")
            .Open "POST", "https://api-free.deepl.com/v2/translate?auth_key=" & API_Key & "&source_lang=" & Quellsprache & "&target_lang=" & Zielsprache & "&text=" & Left(Mailtext, InStr(Mailtext, Chr(10)) - 1)
            .SetRequestHeader("Host") = "api-free.deepl.com"
            .SetRequestHeader("Content-Type") = "application/x-www-form-urlencoded"
            .Send
            Übersetzung_Zeile = Replace(.responsetext, "[und]", "&")
        End With
        Übersetzung_Zeile = Left(Übersetzung_Zeile, InStr(Übersetzung_Zeile, Chr(34) & "}]}") - 1)
        Übersetzung_Zeile = Right(Übersetzung_Zeile, Len(Übersetzung_Zeile) - InStr(Übersetzung_Zeile, Chr(34) & "text" & Chr(34) & ":" & Chr(34)) - 7)
        Übersetzung = Übersetzung & Trim(Übersetzung_Zeile) & Chr(10)
        Mailtext = Right(Mailtext, Len(Mailtext) - InStr(Mailtext, Chr(10)))
    Wend
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "POST", "https://api-free.deepl.com/v2/translate?auth_key=" & API_Key & "&source_lang=" & Quellsprache & "&target_lang=" & Zielsprache & "&text=" & Mailtext
        .SetRequestHeader("Host") = "api-free.deepl.com"
        .SetRequestHeader("Content-Type") = "application/x-www-form-urlencoded"
        .Send
        Übersetzung_Zeile = Replace(.responsetext, "[und]", "&")
    End With
    Übersetzung_Zeile = Left(Übersetzung_Zeile, InStr(Übersetzung_Zeile, Chr(34) & "}]}") - 1)
    Übersetzung_Zeile = Right(Übersetzung_Zeile, Len(Übersetzung_Zeile) - InStr(Übersetzung_Zeile, Chr(34) & "text" & Chr(34) & ":" & Chr(34)) - 7)
    Übersetzung = Übersetzung & Trim(Übersetzung_Zeile) & Chr(10)
'    Me.Cells(2, 1) = Split(Split(Übersetzung, """text"":""")(1), """")(0)
    Range("F" & ActiveCell.Row) = Übersetzung


Man könnte das sicher noch optimieren. Aber so finde ich es für Hobbyprogrammierer wie mich übersichtlicher.

Viele Grüße

Rolf
RTausD
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 349
Registriert: 24. Feb 2007, 10:51
Wohnort: 35683 Dillenburg

Re: DeepL REST API per VBA und PQ

Beitragvon d'r Bastler » 25. Jun 2022, 14:53

Moin allerseits,

auch von mir herzlichen Dank an den Höpp! Aber:
Ich habe eben diesen Text per doppeltem Strg+c zu DeepL geschickt und das hier zurückerhalten:

I just double ctrl+c'ed this text to DeepL and got this back:

Okay, in Makros einen DblClick zu programmieren ist lästig, aber wann sonst ist die API denn sinnvoll?

Grüße
d'r Bastler

Selbst Heiden glauben an min. 10% Mystik in der EDV! Gilt auch für mein Win 10 pro & Office 2019, Win 11 pro & Office 2021 und alle VBAsteleien ... Aktueller Trollfutter-Vorrat: NULL
Benutzeravatar
d'r Bastler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 727
Registriert: 23. Jan 2021, 22:36
Wohnort: VBAsteln makes the world go around

Re: DeepL REST API per VBA und PQ

Beitragvon DerHoepp » 26. Jun 2022, 13:47

Hallo Bastler,

deinen Einwurf hab ich nicht verstanden. @RT: Das kaufmännische und musst du wahrscheinlich encodieren, damit es als kaufmännisches und erkannt wird und nicht als Trennzeichen für Parameter in der Post-Abfrage: https://stackoverflow.com/questions/166 ... and-in-url

Viele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 10441
Registriert: 14. Mai 2013, 11:08

Re: DeepL REST API per VBA und PQ

Beitragvon d'r Bastler » 26. Jun 2022, 14:12

Moin Höpp,

Also noch mal als Kurzfassung: Wozu braucht man für XL die API, wenn (nach Installation der DeepL-App) in allen Win-Apps ein STRG+c+c genügt, um den markierten Text zu übersetzen?
Deine Arbeit nach wie vor in allen Ehren, aber warst es nicht Du, der mir malgeschrieben hat, man müsste nicht alles zu Fuß machen? Mir erscheint Dein (sehr lehrreicher! Danke dafür!) Aufwand zu groß, wenn es doch einen ShortCut gibt.
Deswegen mein lästerlicher Einwurf.

Liebe Grüße
d'r Bastler

Selbst Heiden glauben an min. 10% Mystik in der EDV! Gilt auch für mein Win 10 pro & Office 2019, Win 11 pro & Office 2021 und alle VBAsteleien ... Aktueller Trollfutter-Vorrat: NULL
Benutzeravatar
d'r Bastler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 727
Registriert: 23. Jan 2021, 22:36
Wohnort: VBAsteln makes the world go around

Re: DeepL REST API per VBA und PQ

Beitragvon DerHoepp » 26. Jun 2022, 14:17

Moin,

achso. Jetzt hab ich es verstanden. Man kann es zum Beispiel als function einsetzen, um eine Liste mit mehr als einem Eintrag zu übersetzen.

Viele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 10441
Registriert: 14. Mai 2013, 11:08

Re: DeepL REST API per VBA und PQ

Beitragvon RTausD » 26. Jun 2022, 15:04

Genau dafür habe ich es benutzt.

Ich habe ein Postfach mit über 100 eMails in italienischer Sprache, deren Texte ich so schnell übersetzen konnte.
Und jetzt nutze ich es für eine automatische Übersetzung, wenn neue eMails eingehen.

Viele Grüße an alle

Rolf
RTausD
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 349
Registriert: 24. Feb 2007, 10:51
Wohnort: 35683 Dillenburg

Re: DeepL REST API per VBA und PQ

Beitragvon d'r Bastler » 26. Jun 2022, 20:17

Danke Dir, Höpp, für die Erläuterung! Gut nachvollziehbar und dann schon wieder mehr als sinnvoll :)

Schönen Abend noch!
d'r Bastler

Selbst Heiden glauben an min. 10% Mystik in der EDV! Gilt auch für mein Win 10 pro & Office 2019, Win 11 pro & Office 2021 und alle VBAsteleien ... Aktueller Trollfutter-Vorrat: NULL
Benutzeravatar
d'r Bastler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 727
Registriert: 23. Jan 2021, 22:36
Wohnort: VBAsteln makes the world go around

Re: DeepL REST API per VBA und PQ

Beitragvon duaffentier » 27. Jun 2022, 06:00

Hallo,

kann man dies auch nutzten, um z.B. in vorgegebenen Zellen den Text von DE in ENG zu übersetzten?

Wenn ich eine Datei habe, wo ich entsprechend die ganzen Zeilen vorgebe, kann man dies wo einbinden in den Code?
duaffentier
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 360
Registriert: 29. Jul 2008, 16:35
Wohnort: DD

Re: DeepL REST API per VBA und PQ

Beitragvon RTausD » 27. Jun 2022, 07:44

Hallo duaffentier,

ich habe in meinem Code in den ersten beiden Zeilen die Quellsprache und die Zielsprache definiert.
Wenn Du entsprechende DE und EN einträgst, sollte das funktionieren.

Gruß

Rolf
RTausD
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 349
Registriert: 24. Feb 2007, 10:51
Wohnort: 35683 Dillenburg

Re: DeepL REST API per VBA und PQ

Beitragvon duaffentier » 28. Jun 2022, 07:02

Hallo,

eine Anmeldung benötigt man für das Free?
Wenn mehrere Benutzer die Datei nutzten, würde ja ein API-Key schnell "verbraucht" Sein?
duaffentier
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 360
Registriert: 29. Jul 2008, 16:35
Wohnort: DD

Re: DeepL REST API per VBA und PQ

Beitragvon RTausD » 28. Jun 2022, 14:44

Hallo duaffentier,

für die kostenlose Version muss man sich registrieren und hat dann 500.000 Zeichen pro Monat frei.
Die Registrierung läuft auf eine eMailadresse. Du kannst daher für jeden User einen Lizenzkey einrichten.
Mit Environ("USERNAME") kannst Du ja abfragen, wer das Makro gerade nutzt und dann den entsprechenden Lizenzkey verwenden.

Ich habe vom DeepL-Support auch Code erhalten, mit dem man den aktuellen Verbrauch abfragen kann. Ich habe aber noch nicht die Zeit gehabt, das auszuprobieren. Ich fürchte auch, dass das mit VBA leider nur wenig zu tun hat.
Vielleicht kann DerHoepp uns hier helfen.... :wink:

Code: Alles auswählen

curl -H "Authorization: DeepL-Auth-Key YOUR_AUTH_KEY" https://api-free.deepl.com/v2/usage
 

GET /v2/usage HTTP/1.0
Host: api-free.deepl.com
User-Agent: YourApp
Accept: */*
Authorization: DeepL-Auth-Key YOUR_AUTH_KEY



Viele Grüße

Rolf
RTausD
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 349
Registriert: 24. Feb 2007, 10:51
Wohnort: 35683 Dillenburg


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: MiMu2736, steve1da und 7 Gäste