Excel-Tabelle mit Powershell hochladen und Smap aktualisieren.

[Diren Teke]

Ich habe ein Skript erstellt welches über PowerShell und der REST-API, Daten aus einer Excel-Tabelle holt, in ein Smap einfügt und diesen Smap aktualisiert. 

Als Dankeschön an SmapOne veröffentliche ich diesen Skript.

<#
    .SYNOPSIS
        Nimmt Daten aus einer Excel-Datei und lädt sie auf SmapOne in einen Datenauswahlblock hoch und aktualisiert den entsprechenden Smap.

    .DESCRIPTION
        Dieses PowerShell-Skript, ist darauf ausgelegt, Daten aus einer Excel-Datei mit Hilfe der Rest-API von Smapone in einen Datenauswahlblock zu füllen und anschliessend ein Update der Smap durchzuführen.

    .PARAMETER excelPath
        Gibt den Pfad zur Excel-Datei an, die mit den hochzuladenden Daten.
        Aufbau der Smap muss alle Spalten der Exceltabelle enthalten.
        Benötigt Modul "Import-Excel" siehe .notes

    .PARAMETER smapID
        Gibt die ID der smapOne App an, die aus der Browser-URL der App entnommen werden kann. 
            SmapOne API -> Intern und AccessToken eingeben -> Datasource -> [GET] /DataSource/{dataSourceId} -> dataSourceID eingeben -> Try it out!

    .PARAMETER dataSourceID
        Gibt die Data Source ID an, die mit dem Smap verbunden ist. 
            SmapOne API -> Intern und AccessToken eingeben -> Datasource -> [GET] /DataSource -> Try it out!

    .PARAMETER AccessToken
        Gibt das Login-Token des smapOne Accounts an. 
            Smapone-Plattform -> Mein Profil -> Rest-API -> Token verwalten.

    .INPUTS
        Daten aus einer Excel-Datei.

    .OUTPUTS
        Ergebnisse der API-Aufrufe und des Datenupload-Prozesses.

    .EXAMPLE
        Ausführen dieses Skriptes führt zu einem Hochladen der Daten aus der Exceltabelle an die gewünschte Smap. Anschliessend wird ein Update an die Nutzer des Smaps durchgeführt.
        Parameter des Smaps müssen angepasst werden/sein.

    .LINK
        Weitere Informationen zu SmapOne: https://www.smapone.com/
        Platform zum erstellen der Smaps: https://platform.smapone.com/
        Swagger zur SmapOne API: https://platform.smapone.com/Backend/swagger/ui/index
        PowerShell Invoke-RestMethod: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/invoke-restmethod

    .NOTES
        Datum: 05.01.2024
        Autor: Diren Teke
        Version: 1.0
        Systemvoraussetzungen: 
        Das Skript kann ab PowerShell Version 5.1.X betrieben werden, bei Verionen unterhalb dieser ist die Funktionsweise eventuell nicht gewährleistet.
            ->Mit $PSVersionTable.Version kann in der PowerShell Console die aktuelle Version eingesehen werden.
        
        Die vorinstallierte Version ab Windows 10 Systemen sollte 5.1.X sein.
        Vor der Benutzung des Skrips muss sichergestellt werden, dass das Modul Import-Excel herruntergeladen wurden. 
            -> install-module -Name ImportExcel -Force
#>

<#
--------------------------------   
Eingabe-Parameter
--------------------------------
#>
    #Parameter für die Kommunikation mit der API setzen
        param(
            # Pfad zur Excel-Datei
                [string]$excelPath = "Eingeben_Pfad",

            # SmapID der Smap
                [string]$smapID = "Eingeben_smapID",

            # DataSourceID des Smaps
                [string]$dataSourceID = "Eingabe_DataSource",

            # Access-Token des Creator SmapAccounts
                [string]$AccessToken = "Eingabe_AccessToken"
            )

<#
--------------------------------   
Headers-Definition
--------------------------------
#>
    # Angaben zum Header als Array
        $headers = @{
            "Content-Type" = "application/json"
            "Accept" = "application/json"
            'Authorization' = "Basic $AccessToken"
        }

<#
--------------------------------   
Auslesen-Letzte-Smap-Version
--------------------------------
#>
    # URL- REST-API-Informationen Informationen einer Smap abfragen.
        $apiInformation = "https://platform.smapone.com/Backend/intern/DataSource/$($dataSourceID)?accesstoken=$($AccessToken)"

    # Holen der Informationen des Smaps.
        $SmapInformation = Invoke-RestMethod -Uri $apiInformation -Method Get -Headers $headers

    # Letzte Version extrahieren.
        $SmapVersion = $SmapInformation.latestVersion

    # Ausgabe letzte Version.
        Write-Output "Letzte Version $SmapVersion"

    # Ausgabe des API-URL's welches die aktuelle Version des Smaps beinhalten muss.
        Write-Output "URL Endpunkt: $apiEndpunkt"

<#
--------------------------------   
Excel-Auslesen und umwandeln zu Json
--------------------------------
#>
    # Excel-Daten mit dem Modul Import-Excel auslesen.
        $excelData = Import-Excel $excelPath 

    # Ausgabe der Excel-Daten
        $excelData | Format-Table

    # Konvertierung der Daten von Excel zu Json. Array wird mit "," "@" zwingend definiert.
        $jsonData =,@($excelData) | ConvertTo-Json

    # Ausgabe des zu übermittelnden Json-Bodys. Muss ein Array sein!
        Write-Output "Json-Body (Array) aus Jsondata an API: $jsonData"

<# 
--------------------------------
REST-API-Aufruf
--------------------------------
#>
    # URL- REST-API-Endpunkt für das Hochladen der Daten an die API.
        $apiEndpunkt = "https://platform.smapone.com/Backend/intern/DataSource/$($dataSourceID)/Versions/$($SmapVersion)/Definition/Values?accesstoken=$($AccessToken)"

    # URL- REST-API-Endpunkt-Update für das Aktualisieren des Smaps.
        $apiEndpunktUpdate = "https://platform.smapone.com/Backend/intern/Smaps/$($smapID)/Versions/Current/DataSources/Update?updateEditVersion=true&accesstoken=$($AccessToken)"

    # Ausgabe Rest-API Kommunikation startet als nächstes.
        Write-Host "Vor Invoke-RestMethoden"

    # Übermitteln des Json-Body (Array) mit den Daten aus der Excel an die API, unbedingt mit Unicode utf-8.
        $datenAnAPI = Invoke-RestMethod -Uri $apiEndpunkt -Method Put -Body $jsonData -Headers $headers -ContentType "application/json; charset=utf-8"

    # Ausgabe nach Übermittlung an die API.
        Write-Output "Daten nach übermitteln an Smap: $datenAnAPI"

    # Aktualisiern der Smap.
        $updateSmap = Invoke-RestMethod -Uri $apiEndpunktUpdate -Method Put -Headers $headers -ContentType "application/json; charset=utf-8"

    # Ausgabe nach Aktualisierung der API.
        Write-Output  "Nach update des Smaps: $updateSmap"

    # Ausgabe das die Rest API kommunikation beendet ist.
        Write-Host "Nach Invoke-RestMethoden"
 

[Manuel Rühl]

Danke für's Teilen, davon lebt die Community!

 

[Bülent Erbas]

Wow! Vielen Dank fürs Teilen.

[Moritz]

Abgefahren, danke! 🥰

[Julia Herbst]

Hallo an die Community, 

hat das Skript jemand produktiv im Einsatz? 

 

Beste Grüße 

[Andreas Ermantraut]

So  lebt die Community!

[Johanna Becker]

@Diren Teke vielleicht?

Am 27.5.2024 um 11:37 schrieb Julia Herbst:

Hallo an die Community, 

hat das Skript jemand produktiv im Einsatz? 

 

Beste Grüße 

 

[Diren Teke]

Hallo Zusammen

Dieses Skript ist leider nicht in Einsatz. Aufgrund dessen das es ein Schulprojekt war.

LG Diren

[Bülent Erbas]

Ein sehr interessantes Schulprojekt 😁

 

[Julia Herbst]

Falls es doch jemand produktiv hat würde ich mich über eine Nachricht freuen. 

Bei mir läuft das Skript zum Ende auf einen Fehler beim Invoke-Befehl. Grundsätzlich scheint es daran zu liegen, dass eine Ressource hier (# URL- REST-API-Endpunkt-Update für das Aktualisieren des Smaps.) nicht auffindbar ist . 

Bearbeitet 28. Mai von Julia Herbst

[Bülent Erbas]

Hallo @Julia Herbst

probiere mal diese Version.

Ich selbst habe es allerdings noch nicht getestet.

 

 

<#

.SYNOPSIS

    Nimmt Daten aus einer Excel-Datei und lädt sie auf SmapOne in einen Datenauswahlblock hoch und aktualisiert den entsprechenden Smap.

 

.DESCRIPTION

    Dieses PowerShell-Skript, ist darauf ausgelegt, Daten aus einer Excel-Datei mit Hilfe der Rest-API von Smapone in einen Datenauswahlblock zu füllen und anschliessend ein Update der Smap durchzuführen.

 

.PARAMETER excelPath

    Gibt den Pfad zur Excel-Datei an, die mit den hochzuladenden Daten.

    Aufbau der Smap muss alle Spalten der Exceltabelle enthalten.

    Benötigt Modul "Import-Excel" siehe .notes

 

.PARAMETER smapID

    Gibt die ID der smapOne App an, die aus der Browser-URL der App entnommen werden kann. 

        SmapOne API -> Intern und AccessToken eingeben -> Datasource -> [GET] /DataSource/{dataSourceId} -> dataSourceID eingeben -> Try it out!

 

.PARAMETER dataSourceID

    Gibt die Data Source ID an, die mit dem Smap verbunden ist. 

        SmapOne API -> Intern und AccessToken eingeben -> Datasource -> [GET] /DataSource -> Try it out!

 

.PARAMETER AccessToken

    Gibt das Login-Token des smapOne Accounts an. 

        Smapone-Plattform -> Mein Profil -> Rest-API -> Token verwalten.

 

.INPUTS

    Daten aus einer Excel-Datei.

 

.OUTPUTS

    Ergebnisse der API-Aufrufe und des Datenupload-Prozesses.

 

.EXAMPLE

    Ausführen dieses Skriptes führt zu einem Hochladen der Daten aus der Exceltabelle an die gewünschte Smap. Anschliessend wird ein Update an die Nutzer des Smaps durchgeführt.

    Parameter des Smaps müssen angepasst werden/sein.

 

.LINK

    Weitere Informationen zu SmapOne: https://www.smapone.com/

    Platform zum erstellen der Smaps: https://platform.smapone.com/

    Swagger zur SmapOne API: https://platform.smapone.com/Backend/swagger/ui/index

    PowerShell Invoke-RestMethod: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/invoke-restmethod

 

.NOTES

    Datum: 05.01.2024

    Autor: Diren Teke

    Version: 1.0

    Systemvoraussetzungen: 

    Das Skript kann ab PowerShell Version 5.1.X betrieben werden, bei Verionen unterhalb dieser ist die Funktionsweise eventuell nicht gewährleistet.

        ->Mit $PSVersionTable.Version kann in der PowerShell Console die aktuelle Version eingesehen werden.

    

    Die vorinstallierte Version ab Windows 10 Systemen sollte 5.1.X sein.

    Vor der Benutzung des Skrips muss sichergestellt werden, dass das Modul Import-Excel herruntergeladen wurden. 

        -> install-module -Name ImportExcel -Force

#>

 

 

<#

--------------------------------   

Eingabe-Parameter

--------------------------------

#>

# Parameter für die Kommunikation mit der API setzen

param(

    # Pfad zur Excel-Datei

    [string]$excelPath = "Eingeben_Pfad",

 

    # SmapID der Smap

    [string]$smapID = "Eingeben_smapID",

 

    # DataSourceID des Smaps

    [string]$dataSourceID = "Eingabe_DataSource",

 

    # Access-Token des Creator SmapAccounts

    [string]$AccessToken = "Eingabe_AccessToken"

)

 

<#

--------------------------------   

Headers-Definition

--------------------------------

#>

# Angaben zum Header als Array

$headers = @{

    "Content-Type" = "application/json"

    "Accept" = "application/json"

    'Authorization' = "Basic $AccessToken"

}

 

<#

--------------------------------   

Auslesen-Letzte-Smap-Version

--------------------------------

#>

# URL- REST-API-Informationen Informationen einer Smap abfragen.

$apiInformation = "https://platform.smapone.com/Backend/intern/DataSource/$($dataSourceID)?accesstoken=$($AccessToken)"

 

# Holen der Informationen des Smaps.

$SmapInformation = Invoke-RestMethod -Uri $apiInformation -Method Get -Headers $headers

 

# Letzte Version extrahieren.

$SmapVersion = $SmapInformation.latestVersion

 

# Ausgabe letzte Version.

Write-Output "Letzte Version $SmapVersion"

 

<#

--------------------------------   

Excel-Auslesen und umwandeln zu Json

--------------------------------

#>

# Prüfen ob das Modul Import-Excel installiert ist, wenn nicht, dann installieren.

if (-not (Get-Module -ListAvailable -Name ImportExcel)) {

    Install-Module -Name ImportExcel -Force

}

 

# Excel-Daten mit dem Modul Import-Excel auslesen.

$excelData = Import-Excel $excelPath 

 

# Ausgabe der Excel-Daten

$excelData | Format-Table

 

# Konvertierung der Daten von Excel zu Json. Array wird mit "," "@" zwingend definiert.

$jsonData = @($excelData) | ConvertTo-Json

 

# Ausgabe des zu übermittelnden Json-Bodys. Muss ein Array sein!

Write-Output "Json-Body (Array) aus Jsondata an API: $jsonData"

 

<# 

--------------------------------

REST-API-Aufruf

--------------------------------

#>

# URL- REST-API-Endpunkt für das Hochladen der Daten an die API.

$apiEndpunkt = "https://platform.smapone.com/Backend/intern/DataSource/$($dataSourceID)/Versions/$($SmapVersion)/Definition/Values?accesstoken=$($AccessToken)"

 

# URL- REST-API-Endpunkt-Update für das Aktualisieren des Smaps.

$apiEndpunktUpdate = "https://platform.smapone.com/Backend/intern/Smaps/$($smapID)/Versions/Current/DataSources/Update?updateEditVersion=true&accesstoken=$($AccessToken)"

 

# Ausgabe Rest-API Kommunikation startet als nächstes.

Write-Host "Vor Invoke-RestMethoden"

 

# Übermitteln des Json-Body (Array) mit den Daten aus der Excel an die API, unbedingt mit Unicode utf-8.

$datenAnAPI = Invoke-RestMethod -Uri $apiEndpunkt -Method Put -Body $jsonData -Headers $headers -ContentType "application/json; charset=utf-8"

 

# Ausgabe nach Übermittlung an die API.

Write-Output "Daten nach übermitteln an Smap: $datenAnAPI"

 

# Aktualisieren der Smap.

$updateSmap = Invoke-RestMethod -Uri $apiEndpunktUpdate -Method Put -Headers $headers -ContentType "application/json; charset=utf-8"

 

# Ausgabe nach Aktualisierung der API.

Write-Output "Nach update des Smaps: $updateSmap"

 

# Ausgabe das die Rest API kommunikation beendet ist.

Write-Host "Nach Invoke-RestMethoden"

[Julia Herbst]

Guten Morgen @Bülent Erbas, 

ein Traum! 

War das hier das Problem:  

# Ausgabe des API-URL's welches die aktuelle Version des Smaps beinhalten muss.
        Write-Output "URL Endpunkt: $apiEndpunkt"

Oder habe ich eine andere Änderung übersehen? 

Das automatische Installieren des Excel-Input Moduls ist sehr komfortabel. 

Mit deinem Skript hab übrigens ein neuer Fehler, der mir dann aufgezeigt hat, dass etwas mit meiner Datenquelle auch nicht stimmt. Hier werden ich nochmal suchen müssen. 

 

Vielen dank für die Hilfe!

[Bülent Erbas]

Guten Morgen @Julia Herbst

freut mich sehr das es funktioniert. 😃

Ja, deine Vermutung war tatsächlich richtig, die Verwendung der API-Endpunkte waren nicht einheitlich.

Auch ein paar Parameterdeklarationen waren nicht ganz sauber.

Aber alles in allem hielt sich es in Grenzen. Der Grundaufbau von @Diren Teke ist schon echt super.

Bzgl. der Datenquelle kannst du ja noch mal mitteilen, welcher Fehler da auftaucht, falls du nicht weiterkommen solltest.

 

 

[Gunnar Mahnke-Sun]

Hallo zusammen,

kann mir bitte jemand kurz beschreiben, wie ich damit arbeiten kann? Ich bin bis jetzt so vorgegangen:

1. PowerShell als Admin öffnen
2. Import-Excel installieren
3. 4 Parameter im param-Block ergänzen
4. Skript ausführen

Dabei kommt das hier heraus, von dem ich leider nicht genug verstehe:

Viele Grüße

Gunnar

[Bülent Erbas]

Hallo @Gunnar Mahnke-Sun

prüfe bitte mal vorab ob die PowerShell Version passt.
In Powershell einfach eingeben: $PSVersion

Die Version sollte über 5.1.x liegen

Ich gehe davon aus, dass es passt... aber sicher ist sicher 😀

[Gunnar Mahnke-Sun]

vor 9 Minuten schrieb Bülent Erbas:

Hallo @Gunnar Mahnke-Sun

prüfe bitte mal vorab ob die PowerShell Version passt.
In Powershell einfach eingeben: $PSVersion

Die Version sollte über 5.1.x liegen

Ich gehe davon aus, dass es passt... aber sicher ist sicher 😀

Ich habe Version 5.1

[Bülent Erbas]

Das sollte eigentlich auch passen.

Hab das Gefühl das das Skript mit der smap nicht klar kommt.

Er meckert diese Zeile an:
$SmapInformation = Invoke-RestMethod -Uri $apiInformation -Method Get -Headers $headers

Darüber holt das Skript sich erst einmal die Header-Informationen und versucht sich zu autorisieren.

Ggf. müsstest du die Parameter-Daten noch mal prüfen.

[Gunnar Mahnke-Sun]

Das hat nicht geholfen. Ich vermute, es liegt an meiner Datei, die ich importieren möchte. Wie muss diese eigentlich aussehen und ist es ein Problem, dass hier 2 Blätter (Data Records + RepeatGroup) vorhanden sind?

[Gunnar Mahnke-Sun]

Gibt es ein Video zum Thema Datenimport in der smapCademy?

[Paul Bumann]

@Andrea Urbschat Dis question is made for you 😄

[Bülent Erbas]

vor 2 Stunden schrieb Gunnar Mahnke-Sun:

Das hat nicht geholfen. Ich vermute, es liegt an meiner Datei, die ich importieren möchte. Wie muss diese eigentlich aussehen und ist es ein Problem, dass hier 2 Blätter (Data Records + RepeatGroup) vorhanden sind?

Das habe ich leider nicht getestet.

@Julia Herbst wie bist du hier vorgegangen?

[Julia Herbst]

Guten Morgen  @Gunnar Mahnke-Sun, 

ich habe nur eine ganz simple Tabelle genutzt in der es 3 Spalten und ein paar Zeilen gibt. 

Den Fehler in der ersten Datenquelle konnte ich nicht finden -ich habe sie einfach nur nochmal erstellt und dann lief es sauber. Da kann ich leider nicht viel Hilfestellung geben. 

Von dem was Skript zurückgibt bin ich aber bei @Bülent Erbas und der Vermutung, dass etwas bei den Parametern nicht zu 100% stimmt. Zumindest konnte ich mich beim Testen immer auf die Rückmeldung in PS verlassen. 

[Philip Alvermann]

vor 16 Stunden schrieb Gunnar Mahnke-Sun:

Wie muss diese eigentlich aussehen

Am besten du lädst dir die Datei aus deinem bestehenden DSAB herunter und benutzt die dann für deinen Import.
Die Datei kannst du dann beliebig erweitern. Solange du nicht die Struktur änderst sollte alles funktionieren.

Deine Fehlermeldung sagt weiterhin, das deine Erweiterung der Exceldatei nicht unterstützt wird.

Wenn du die Datei heruntergeladen hast speichere die als *.xlsx ab und dann schauen wir weiter.

Den Pfad solltest du auch richtig angeben z.B. "C:/Users/irgendwer/Documents/xxxx.xlsx".

Also einen Slash(/) und keinen Backslash(\) verwenden

[Philip Alvermann]

Hier ist noch ein Beispiel wenn man mehrere DSAB's abgleichen möchte. Es wird nur der token, die Id des DSAB's und die Excel-Datei benötig.
Die smaps die ein Update benötigen werden aus den DSAB-Infos geholt.

Falls man den DSAB in mehreren smaps benutzt bekommen diese dann auch ein Update, das wurde in dem ursprünglichen Script nicht berücksichtigt.

Fehlerbehandlung kann sich ja jeder nach Gusto selbst reinbauen, wenn er/sie es brauchen.

# Created By: Philip Alvermann
# Version 1.0
#
# Filename: dsabSync-demo.ps1
#
# Der hier enthaltene Code dient als Beispiel. 
# Ich übernehme keine Garantie, Gewährleistung oder Support für den Code oder Bestandteile. 
# Verwendung des Codes erfolgt auf eigene Gefahr.
# 
# Benötigt wird für die Ausführung das Modul Import-Excel
# die Importfiles müssen in dem Ordner import des Verzeichnisses liegen in dem das Script ausgeführt wird

$config = @(
    [PSCustomObject]@{
        dsabId     = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
        importFile = "$PSScriptRoot\import\test-dsab_neu.xlsx";
        token      = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
    }
    #,[PSCustomObject]@{
    #dsabId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
    #importFile = "$PSScriptRoot\import\test-dsab.xlsx";
    #token = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
    #}
) 

function getRequest ($uriString) {
    
    try {
        $resp = Invoke-WebRequest $uriString        
        return $resp;
    }
    catch {
        return "";
    }
}

function UpdateSmap($dsabConfig, $smapId) {
    $httpString = "https://platform.smapone.com/Backend/intern/Smaps/$($smapID)/Versions/Current/DataSources/Update?updateEditVersion=true&accesstoken=$($dsabConfig.token)"
    $smap = Invoke-RestMethod -Uri $httpString -Method Put
    Write-Output "new sampVersion : $($smap.version) for smap $($smapID)"
}

function UpdateDsab($dsabConfig) {
    $httpString = "https://platform.smapone.com/Backend/intern/DataSource/$($dsabConfig.dsabId)?accesstoken=$($dsabConfig.token)"
    $dsab = getRequest -uriString $httpString;
    $jsonDsab = $dsab.Content | ConvertFrom-Json
    
    $excelData = Import-Excel $($dsabConfig.importFile)
    $excelData | Format-Table
    $newData = @($excelData) | ConvertTo-Json

    $httpString = "https://platform.smapone.com/Backend/intern/DataSource/$($dsabConfig.dsabId)/Versions/$($jsonDsab.latestVersion)/Definition/Values?accesstoken=$($dsabConfig.token)"
    $newDsab = Invoke-RestMethod -Uri $httpString -Method Put -Body $newData -ContentType "application/json; charset=utf-8"
    Write-Output "dsab latestVersion: $($jsonDsab.latestVersion) dsab newVersion $($newDsab.version)"

    foreach ($smaps in $jsonDsab.usedInSmaps) {
        UpdateSmap -dsabConfig $dsabConfig -smapId $($smaps.smapID)
    }
}
function StartSync($Config) {
    foreach ($element in $Config) {
        UpdateDsab($element)
    }
}

StartSync($config)

 

Bearbeitet 30. Mai von Philip Alvermann

[Gunnar Mahnke-Sun]

Vielen Dank, @Philip Alvermann! Ich teste das morgen gleich mal aus.