Listele SharePoint reprezintă un mecanism avansat de colectare și stocare a datelor din cadrul proceselor economice curente. De multe ori recomandăm renunțarea la colectarea datelor prin Excel și implementarea de liste de colectare date și raportarea acestora în Excel. Pentru cei care au mai lucrat cu liste indiferent de format, acestea au capacitatea de a exporta în timp real datele din liste în fișiere Excel pentru consultare și raportare centralizată. Doar că integrarea listelor SharePoint cu Excel este unidirecțională, datele pot fi doar citite fără actualizarea acestora în sensul Excel-SharePoint. Acest articol, propune cititorilor un model de implementare a operațiilor CRUD (Create-Read-Update-Delete) din Excel asupra unei lista SharePoint on-line prin utilizarea fluxurilor de lucru Power Automate.
Menționăm că această metodă este valabilă doar în subscripțiile de Office 365. Pentru cei care doresc să încerce varianta cu soluții locale vă reamintesc un articol din urmă cu ceva timp: Sincronizare date între Excel 2013 și SharePoint 2013.
Din punct de vedere tehnic o variantă de lucru cu Excel și cu fluxurile de lucru este descrisă în documentația Microsoft la aceste adrese:
- Integrarea cu Flow: https://powerautomate.microsoft.com/nl-nl/blog/introducing-microsoft-flow-integration-in-excel/
- Aici un alt exemplu: https://docs.microsoft.com/en-us/business-applications-release-notes/april18/microsoft-flow/build-run-flows-excel
Din păcate (sau fericire) mulți administratori au dezactivat opțiunea de instalare a add-in-urilor din Office Online Store ceea ce nu le permite prea facil utilizatorilor să aibă acces la acest instrument.
În sursa video: Trigger a Flow from an Excel Button apare o versiune ingenioasă de a utiliza lansarea unui flux de lucru Power Automate de pe un „buton” din Excel. Pornind de la acest video am adăugat câteva funcționalități de actualizare a listelor direct din Excel.
În continuarea articolului voi prezenta fiecare operațiune așa cum apare în Excel și particularitățile fluxului de lucru specific. Voi reveni zilele următoare cu un update Video pe canalul https://www.youtube.com/valygreavu
1. Sincronizarea listei SharePoint cu Excel (READ)
Datele în listele SharePoint pot ajunge prin Export din tabele Excel sau prin completarea element cu element sau prin formulare sau pot fi scrise de fluxuri.
În imagine avem un tabel cu produs, care prin funcția de Export to Excel poate fi consultată și prelucrată tabelar. În același timp, fișierul iqy care se descarcă la export are și funcție de „legare” a listei cu Excel ceea ce permite ca la un simplu Refresh în Excel să se importe automat noile înregistrări.
Lista exportată în Excel și buton de Refresh înregistrări prin preluare unidirecțională din SharePoint:
În această demonstrație coloana ID este foarte importantă pentru că ea este utilizată de fluxuri pentru a actualiza sau șterge înregistrări. ID-ul este o metadată de sistem de tip autonumber și nu poate fi refolosită de alte înregistrări.
2. Crearea de înregistrări din Excel în SharePoint (CREATE)
Pentru crearea de înregistrări din Excel, am creat o nouă foaie de calcul, am definit aceeași structură a capului de tabel cu cea a listei, am definit ca table cu numele tblCreate după care am creat fluxul în Power Automate de tip HTTP request ca să pot invoca fluxul ca link de pe butonul din Excel.
Fișierul Excel l-am salvat în biblioteca Documente din SharePoint ca să poate fi accesibil de flux pe baza acelorași credențiale. Restul actualizărilor le fac direct pe fișierul salvat Online.
În imagine: Definirea unui flux de tip HTTP request
Pașii fluxului:
Prima acțiune folosește metoda GET iar în momentul în care se salvează fluxul generează HTTP GET URL care se folosește ca link în Excel pe „butonul” Adăugare produse.
În pasul 2 este definit fișierul Excel și tabelul de date:
În pasul 3 creez un tabel HTML care va fi trimis în ultimul pas prin email în vederea confirmării finalizării fluxului. Am preluat în tabel doar trei coloane.
În pasul 4 am folosit o acțiune de tip Create item care să genereze pentru fiecare linie din tabelul Excel (value) câte o nouă înregistrare în lista Produse din SharePoint.
La finalul fluxului am folosit opțiunea de trimitere a unui mesaj de email cu tabelul generat în pasul 3.
Pentru a verifica dacă datele au ajuns corect în listă, o putem accesa din Browser sau putem da refresh în Excel.
În cazul în care nu există un mecanism de control (cheie primară pe coduri) înregistrările pot fi dublate la fiecare rulare a fluxului.
Acest model de flux poate fi folosit în cazul în care doriți să faceți bulk insert într-o listă deja existentă de SharePoint.
3. Actualizarea înregistrărilor SharePoint prin invocare din Excel (UPDATE)
Pentru operațiunile de actualizare a datelor avem nevoie de un tabel Excel nou, în cazul meu denumit tblUpdate, care să permită introducerea valorilor din tabel, esențial fiind în acest model coloana ID din SharePoint.
În tabelul Excel am introdus și două coloane de căutare (VLOOKUP) pentru a prelua din tabelul de stocuri actual din SharePoint valorile vechi ale stocului și prețului, limitând exemplu la faptul că doar cele două se schimbă mai des.
Modelul de actualizare se bazează pe completarea ID-ului de SharePoint și datelor în formatul lor de tabel.
Fluxul de actualizare este aproape identic cu cel de creare, cu mențiunea că la pasul 4 din flux pentru fiecare linie din Excel trebuie introdusă o acțiune de preluare a datelor din lista Produse din SharePoint pe baza ID-ului din Excel (Filter Query).
Pentru fiecare linie din SharePoint identificată, se execută acțiunea de Update item cu valorile preluate din Excel, fără a afecta ID-ul. Restul valorilor pot fi schimbate.
La operațiunile de update și creare apar mici probleme la crearea fluxului legate de tipul datelor. În constructorul de flux, unele valori numerice preluate din Excel nu sunt preluate în celula aferentă. Un mic detaliu sâcâitor care se poare rezolva prin trecerea valorii numerice dinamice într-o casetă text și Cut/Paste a câmpului în celula corectă, în cazul meu: CodProd, Stoc, PretInregistrare.
La finalizarea fluxului acesta trimite email cu tabelul cu valorile noi și vechi.
4. Ștergerea înregistrărilor SharePoint prin invocare din Excel (DELETE)
Pentru ștergere am procedat prin crearea unui alt tabel care să conțină ID-ul înregistrării. Fluxul este foarte asemănător cu UPDATE doar că în secțiunea a doua din pasul 4 înlocuim operațiunea de update cu operațiunea de ștergere.
În imagine, pasul de ștergere din fluxul dedicat.
În cazul în care eliminăm din greșeală anumite elemente de listă se pot recupera ușor din Recycle Bin în perioada de timp configurată de administrator.
Cam atât. Sper să fie util cuiva!
Voi reveni zilele următoare cu un update Video pe canalul https://www.youtube.com/valygreavu
Clip video demonstrații articol: https://youtu.be/tgolXR5foxk
Valy Greavu - Live Blog 