Excel-Tabelle mit Powershell hochladen und Smap aktualisieren.

Diren Teke · April 25

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 · April 25

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

Bülent Erbas · April 25

Wow! Vielen Dank fürs Teilen.

_Moritz_ · April 26

Abgefahren, danke! 🥰

Julia Herbst · May 27

Hallo an die Community,

hat das Skript jemand produktiv im Einsatz?

Beste Grüße

Andreas Ermantraut · May 28

So lebt die Community!

Johanna Becker · May 28

@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 · May 28

Hallo Zusammen

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

LG Diren

Bülent Erbas · May 28

Ein sehr interessantes Schulprojekt 😁

Julia Herbst · May 28

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 .

Edited May 28 by Julia Herbst

Bülent Erbas · May 28

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 · May 29

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 · May 29

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 · May 29

Hallo zusammen,

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

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

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

Viele Grüße

Gunnar

Bülent Erbas · May 29

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 · May 29

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 · May 29

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 · May 29

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 · May 29

Gibt es ein Video zum Thema Datenimport in der smapCademy?

Paul Bumann · May 29

@Andrea Urbschat Dis question is made for you 😄

Bülent Erbas · May 29

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 · May 30

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 · May 30

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 · May 30

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)

Edited May 30 by Philip Alvermann

Gunnar Mahnke-Sun · May 30

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

Excel-Tabelle mit Powershell hochladen und Smap aktualisieren.

Recommended Posts

Diren Teke

Manuel Rühl

Bülent Erbas

_Moritz_

Julia Herbst

Andreas Ermantraut

Johanna Becker

Diren Teke

Bülent Erbas

Julia Herbst

Bülent Erbas

Julia Herbst

Bülent Erbas

Gunnar Mahnke-Sun

Bülent Erbas

Gunnar Mahnke-Sun

Bülent Erbas

Gunnar Mahnke-Sun

Gunnar Mahnke-Sun

Paul Bumann

Bülent Erbas

Julia Herbst

Philip Alvermann

Philip Alvermann

Gunnar Mahnke-Sun

Please sign in to comment

Forums

Activity

smapCademy