Building a Daily Chatbot Transcript Archiver with Power Automate

How to automatically export Copilot conversation transcripts from Dataverse to SharePoint as a daily CSV.

Overview

This Power Automate cloud flow ("Daily [Assistant] Transcripts to SharePoint") runs once a day, pulls the previous day's chatbot conversation transcripts out of Dataverse, flattens each message into a CSV row, and drops a single dated .csv file into a SharePoint document library. It's a clean pattern for archiving conversational AI logs for analytics or compliance.

Flow Architecture

Recurrence (daily 6:00 AM)
   v
Initialize variable  -> csvContent (CSV header)
   v
List rows            -> Dataverse: conversationtranscripts (yesterday only)
   v
Apply to each        -> over each transcript record
   |- Compose         -> extract bot name
   \- Condition (If)   -> only "[Assistant Name]" transcripts
        |- Filter array -> keep only "message" activities
        |- Select       -> map each message to a CSV row
        \- Append to string variable -> add rows to csvContent
   v
Create file          -> SharePoint: ChatTranscripts_<yesterday>.csv

Step 1 - Trigger: Recurrence

A scheduled trigger fires the flow once per day.

Setting	Value
Interval	1
Frequency	Day
Time zone	Pacific Standard Time
At these hours	6
At these minutes	0

Preview: Runs at 6:00 every day.

{
  "type": "Recurrence",
  "recurrence": {
    "interval": 1,
    "frequency": "Day",
    "timeZone": "Pacific Standard Time",
    "schedule": { "hours": [ "6" ], "minutes": [ 0 ] }
  }
}

Step 2 - Initialize variable (CSV header)

A string variable csvContent is seeded with the CSV header row. decodeUriComponent('%0A') injects a real newline character so the next rows start on a fresh line.

• Name: csvContent
• Type: String
• Value (fx):

@concat('AgentName,Session,ConversationStartTime,Role,MessageText,Timestamp', decodeUriComponent('%0A'))

Step 3 - List rows (Dataverse)

Retrieves transcript records from the Dataverse conversationtranscripts table, scoped to yesterday using an OData $filter.

• Table name: conversationtranscripts
• Filter rows (fx):

conversationstarttime ge @{startOfDay(addDays(utcNow(),-1))} and conversationstarttime lt @{startOfDay(utcNow())}

startOfDay(addDays(utcNow(),-1)) = start of yesterday; startOfDay(utcNow()) = start of today. Together they form a clean 24-hour window for the prior day.

{
  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "entityName": "conversationtranscripts",
      "$filter": "conversationstarttime ge @{startOfDay(addDays(utcNow(),-1))} and conversationstarttime lt @{startOfDay(utcNow())}"
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps",
      "connection": "[REDACTED - connection reference]",
      "operationId": "ListRecords"
    }
  }
}

Step 4 - Apply to each (per transcript)

Loops over every record returned by List rows: @outputs('List_rows')?['body/value'].

4a - Compose (extract bot name)

Pulls the friendly (formatted) name of the bot tied to the transcript:

@items('Apply_to_each')?['_bot_conversationtranscriptid_value@OData.Community.Display.V1.FormattedValue']

4b - Condition (If)

Only process transcripts that belong to the target assistant:

@equals(outputs('Compose'), '[Assistant Name]')

If true, the three actions below run. If false, nothing happens for that record.

Filter array - keep only chat messages

The transcript content is JSON; parse it, grab the activities array, and keep only items whose type is message (dropping system/event entries).

• From (fx): @json(items('Apply_to_each')?['content'])?['activities']
• Where (fx): @equals(item()?['type'], 'message')

Select - map each message to a CSV row

Builds one quoted, comma-separated CSV line per message. Note the careful quote-escaping (" -> ""), role mapping, timestamp conversion from Unix epoch seconds, and newline stripping inside message text.

• From (fx): @body('Filter_array')
• Map / select (fx):

@concat(
  '"', replace(outputs('Compose'), '"', '""'), '","',
  replace(string(items('Apply_to_each')?['conversationtranscriptid']), '"', '""'), '","',
  formatDateTime(items('Apply_to_each')?['conversationstarttime'], 'dd MMM yyyy HH:mm'), '","',
  if(equals(string(item()?['from']?['role']), '1'), 'User', 'Bot'), '","',
  replace(replace(replace(coalesce(item()?['text'], ''), '"', '""'), decodeUriComponent('%0D'), ''), decodeUriComponent('%0A'), ' '), '","',
  formatDateTime(addSeconds('1970-01-01T00:00:00Z', int(string(item()?['timestamp']))), 'dd MMM yyyy HH:mm:ss'), '"'
)

What the formula does, field by field:

• AgentName - bot name from Compose, double-quotes escaped.
• Session - conversationtranscriptid, double-quotes escaped.
• ConversationStartTime - formatted dd MMM yyyy HH:mm.
• Role - message from/role; "1" -> User, otherwise Bot.
• MessageText - coalesce(..., '') guards nulls; quotes escaped; carriage returns (%0D) removed and line feeds (%0A) replaced with a space so each message stays on one CSV line.
• Timestamp - Unix epoch seconds converted to a date via addSeconds('1970-01-01T00:00:00Z', ...), formatted to the second.

Append to string variable - accumulate rows

Joins the row array with newlines and appends to csvContent; the if(empty(...)) guard avoids adding a stray blank line when a transcript has no messages.

• Name: csvContent
• Value (fx):

@if(empty(body('Select')), '', concat(join(body('Select'), decodeUriComponent('%0A')), decodeUriComponent('%0A')))

Step 5 - Create file (SharePoint)

Writes the fully assembled CSV to a SharePoint library with a date-stamped filename.

Parameter	Value
Site Address	[REDACTED - SharePoint site URL]
Folder Path	[REDACTED - document library path]
File Name (fx)	ChatTranscripts_@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}.csv
File Content (fx)	@{variables('csvContent')}

Transfer mode is Chunked to support larger files.

{
  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "dataset": "[REDACTED - SharePoint site URL]",
      "folderPath": "[REDACTED - document library path]",
      "name": "ChatTranscripts_@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}.csv",
      "body": "@{variables('csvContent')}"
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline",
      "connection": "[REDACTED - connection reference]",
      "operationId": "CreateFile"
    }
  },
  "runtimeConfiguration": { "contentTransfer": { "transferMode": "Chunked" } }
}

Key Takeaways

• Time-windowing with startOfDay + addDays gives a reliable "yesterday only" query without hardcoding dates.
• Build CSV by hand with Select + concat when you need precise control over quoting and escaping; always escape " -> "" and strip %0D/%0A from free-text fields.
• coalesce + if(empty(...)) guards prevent null errors and stray blank lines.
• Unix-epoch timestamps convert cleanly via addSeconds('1970-01-01T00:00:00Z', int(...)).
• Chunked transfer mode on Create file keeps large exports reliable.