MS Forms -palveluun 13. tammikuuta 2025 voimaan tuleva muutos on saattanut jäädä monilta huomaamatta. Mikäli käsittelet MS Forms:in vastaukset aina Power Automatella, olet turvassa. Mikäli taas hyödynnät MS Formsin muodostamaa Excel-tiedostoa, jonne vastaukset automaattisesti tallentuvat, voit olla pulassa.

Miksi kukaan käyttäisi tätä Excel-tiedostoa? Todennäköisesti aika moni. Vastausten automaattista käsittelyä ja/tai tallennusta muualle jatkokäsittelyä varten ei aina tarvita. Usein riittää, että kyselyn vastaukset löytyvät Excel-tiedostosta

Nämä Excel-tiedostot ovat myös mainio pohja Power BI -raporteille. Ryhmän omistamien MS Forms -lomakkeiden vastaustiedostot löytyvät kaikki samasta SharePointin dokumenttikirjastosta.

Näin on ollut mahdollista rakentaa Power BI -raportti, joka sisältää tietolähteenään kyseisen kansion Excel-tiedostot. Parhaimmillaan uusien lomakkeiden vastaukset ovat valuneet raportille ilman mitään toimenpiteitä.

Mikä muuttuu?

Jatkossa vastaukset sisältävä Excel-tiedosto muodostuu vasta kun se avataan MS Forms -palvelusta ensimmäisen kerran. Eikä tätä voi tehdä ennen kuin lomakkeella on lähetetty vähintään yksi vastaus.

Vastaustiedoston muodostus edellyttää siis jatkossa manuaalista toimenpidettä.

Kaiken kukkuraksi uudet vastaukset siirtyvät Excel-tiedostoon jatkossa ainoastaan silloin kuin se avataan. Tiedosto tulee vieläpä avata Excel Onlinella. Excelin työpöytäversio ei vastausten synkronointiin (vielä) kykene.

Olet siis pulassa, mikäli käytät näitä Excel-tiedostoja Power BI:ssä tietolähteinä. Aiemmin ne ovat päivittyneet automaattisesti. Jatkossa näin ei ole.

Mitä voin tehdä?

Haluan Power BI -raporttini tietolähteinä olevien tiedostojen sisältöjen päivittyvän jatkossakin automaattisesti. Mitä vaihtoehtoja minulla on?

Voin toteuttaa Power Automate Desktopilla (RPA) botin, joka avaa päivittäin kaikki vastauksia sisältävät Excel-tiedostot. Ei erityisen hankalaa, mutta kuulostaahan tämä nyt aivan toopelta.

Entä jos hyödyntäisimme samaa rajapintaa, jota Microsoft käyttää päivittäessään Excel-tiedostoon MS Formsilla lähetetyt vastaukset? Avataan kokeeksi yksi tällainen tiedosto ja tutkitaan mitä vastausten synkronointi tekee.

Käytännössä synkronointi hakee MS Forms -palvelusta tiedostoa vastaavan MS Formsin vastaukset ja lisää ne Exceliin.

Voisimmeko hyödyntää samaa tekniikkaa? Todetaan heti alkuun forms.office.com/formapi/api/:n olevan dokumentoimaton rajapinta, jota Microsoft ei tue. Toopea touhua siis senkin käyttäminen. Mutta yritetään silti. Rajataan ratkaisun ensimmäinen versio ainoastaan lähettämään herätteitä tilanteessa, kun ryhmän lomakkeisiin on tullut uusia vastauksia ja kun uuden lomakkeen vastaustiedosto voidaan luoda.

Autentikointi

MS Formsin API:a ei voi käyttää Power Automaten vakioyhdistimillä. Aloitamme siis hankkimalla tokenin, jolla rajapintaa voi käyttää. Mikäli aihepiiri on täysin vieras, voit aloittaa lukemalla tämän.

Tätä varten rekisteröidään Entra ID:hen uusi sovellus, jolla on delegoidut Forms.Read ja Responses.Read.All oikeudet Microsoft Forms -palveluun.

Tämän jälkeen luomme flow’n, jonka alussa muodostetaan käyttäjälle tätä rekisteröityä sovellusta hyödyntäen token.

Jotta homma toimii, on seuraavat arvot oltava oikein.

scope=https://forms.office.com/.default
resource=https://forms.cloud.microsoft

Lisätään saatu token compose-toimintoon, jotta sitä on helpompi käyttää jatkossa.

O365 ryhmän MS Forms -lomakkeiden hakeminen

Haetaan ensin kaikki IT:n (Office 365 -ryhmä) MS Forms -lomakkeet.

Tämä onnistuu seuraavasti

GET https://forms.cloud.microsoft/formapi/api/{tenantid}/groups/{groupid}/forms

Flow’ssa tämä näyttää seuraavalta. Autentikoinnissa (alin kohta) käytetään ensimmäisen http-kutsun palauttamaa tokenia.

Kutsu palauttaa neljän lomakkeen tiedot. Nimen, luontipäivän, tekijän jne. Meitä kiinnostaa erityisesti sdx alkuiset kentät.

Nämä nimittäin puuttuvat, mikäli lomakkeelle ei ole vielä luotu vastaukset sisältävää Excel-tiedostoa.

Lomakkeiden vastausten hakeminen

Yhden lomakkeen vastaukset saa haettua seuraavasti.

GET https://forms.office.com/formapi/api/{tenantid}/groups/{groupid}/forms(%27{formid}%27)/xl/responses

Flow’ssa haemme yksitellen kaikkien ryhmän omistamien lomakkeiden vastaukset.

Vastaukset palautuvat geneerisessä sanomassa, jonka answers-osuus sisältää lomakkeen eri kenttiin syötetyt vastaukset. Alla lomakkeen kaksi vastausta.

Excel-tiedosto odottaa luomista

Mikäli lomakkeella on vastauksia, mutta sitä vastaavaa Excel-tiedostoa ei ole olemassa, lähetämme vastuuhenkilölle sähköpostin, jossa pyydämme häntä luomaan tämän tiedoston MS Forms -palvelussa.

Käyttämämme ehto on seuraava.

length(body('HTTP_-_Get_Responses')?['value']) is greater than 0 AND
empty(items('Apply_to_each')?['sdxWebUrl']) is equal to true

Mikäli ehto toteutuu, lähetetään vastuuhenkilölle sähköposti.

Sähköposti näyttää tältä.

Sähköpostissa oleva linkki avaa sivun, josta Excel-tiedosto luodaan (Open results in Excel).

Kaikki tämä vaiva, jotta joku käy tarvittaessa luomassa vastaustiedoston raportointia varten. Tiedoston, joka aiemmin on syntynyt automaattisesti.

Onko lomakkeilla lähetetty uusia vastauksia?

Tämä ei kuitenkaan riitä. Uudet vastaukset ilmestyvät Excel-tiedostoon vasta kun se avataan. Täydennetään ratkaisua siten, että uusista vastauksista lähetetään tieto vastuuhenkilölle, joka riemukseen saa luvan käydä avaamassa vastaavat Excel-tiedostot. Jotta raportit pysyvät ajantasalla.

Mistä tiedämme onko lomakkella lähetetty uusia vastauksia? Voisimme tutkia jokaisen lomakkeen kohdalla, onko vastauksia lähetetty (submitDate) edellisen tarkistuskerran jälkeen. Tällöin meidän tulee tietää, milloin tarkistus on tehty edellisen kerran. Yksinkertaista.

Toteutetaan tarkistus kuitenkin toisella tapaa. Tarkistetaan ensin mikä on Excel-tiedoston tallennettu viimeisin vastaus ja tarkistetaan sen jälkeen onko se viimeinen myös MS Forms -palvussa.

Excel-taulukon viimeinen rivi

Dokumenttikirjastossa olevan Excelin taulukon viimeisen rivin Id-kentän arvon voi selvittää Graph API:lla seuraavasti.

GET https://graph.microsoft.com/v1.0/sites/{site-id}/drives/{drive-id}/items/{item-id}/workbook/tables/{tablename}/columns/Id/range/lastRow

Tarvitsemme siis Office 365 -ryhmää vastaavan SharePoint-sivuston id:n (site-id). Ajetaan seuraava komento (haettava sivusto on IT).

GET https://graph.microsoft.com/v1.0/sites/pertilamvp.sharepoint.com:/sites/IT

Ja poimitaan vastauksesta sivuston tunniste (id).

Tarvittavat drives ja items -osuudet löytyvät lomakkeen sdxWorkbookId-ominaisuudesta.

Lomakkeen vastaukset on tallennetu aina OfficeForms.Table nimiseen taulukkoon.

Nyt voimme hakea vastausexcelien viimeisen rivin tiedot. Haku onnistuu HTTP with Microsoft Entra ID (preauthorized) -yhdistimellä.

Paluusanoma sisältää vaikka mitä, mutta meitä kiinnostaa ainoastaan viimeisen Id:n arvo. Se löytyy paluusanomasta seuraavassa muodossa.

  "values": [
    [
      9
    ]
  ]

Otetaan arvo talteen seuraavasti.

body('Invoke_an_HTTP_request')?['values'][0][0]

Haetaan tämän jälkeen lomakkeen vastaukset uudelleen. Tällä kertaa ainoastaan ne vastaukset, joiden id on suurempi kuin mitä Excelistä löysimme (filter=id gt n).

GET https://forms.office.com/formapi/api/{tenantid}/groups/{groupid}/forms(%27{formid}%27)/xl/responses?$filter=id%20gt%20{lastidfromexcel}

Mikäli lomakkeella on enemmän vastauksia kuin Excelissä, lähtetetään vastuuhenkilölle sähköpostia. Hänen on aika käydä avaamassa kyseinen Excel-tiedosto.

Yhteenveto

Jep. Ei tämä hirveän hyvältä näytä. Mikäli sinulla on yksittäisiä MS Forms -kyselyitä, joiden vastaukset sisältävät Excel-tiedostot ovat Power BI -raporttisi tietolähteinä, niin harkitsisin vastausten siirtämistä SharePoint-listalle, Dataverseen tms. Tällöin uudet vastaukset saa päivitettyä käytettyyn tietovarastoon Power Automatella.

Mikäli sinulla on dynaaminen MS Forms-kyselyiden vastauksia esittävä raportointiratkaisu, jossa uusia kyselyitä tulee ja menee, on tilanne monimutkaisempi. Yllä esittämälläni tavalla saadaan muodostettua ainoastaan herätteitä. Niiden pohjalta pitää jonkun käydä käsin avaamassa Excel-tiedostoja. Ehkä kuitenkin tekisin Power Automate Desktopilla ratkaisun joka käy luomassa vastaus Excelin aina ensimmäisen vastauksen jälkeen. Ja joka päivittäin kävisi avaamassa ja sulkemassa jokaisen vastaustiedoston. Tai sitten muuttaisin Power BI -raporttia siten, että se käyttää MS Formsin rajapintaa hakeakseen vastaukset.

Toki voimme Power Automatella päivittää oikeisiin Exceleihin tuoreet vastaukset. Mutta siitä se vasta sotku helposti tuleekin. Ehkä koitamme sitä seuraavassa jutussa.