I understand one of Coda’s new focus areas is Enterprise customers.
One integration that would go a very long way with these customers is integration with Microsoft Power Query, i.e. making any Coda table a potential data source. This unlocks extremely convenient read connectivity of Coda tables to Excel and Power BI, which are the bread and butter of corporate data processing and reporting (I would guess over 90% of larger (and smaller) enterprises already use excel).
BTW I was just trying Coda connector for Copilot to use Coda as a permissions-aware knowledge source and it seems to work really well - some sync errors but overall very promising!
@Eric_Koleda @shishir
Thanks for the suggestion @Jean_Marc_Dieu. We historically haven’t invested a ton in integrating with the Microsoft suite, but I agree that there is likely a good opportunity there. Given the other priorities we’re working on, I wouldn’t expect us to build an integration with Power Query in the near term, but it looks like they have an open SDK:
Perhaps you or others in the community could prototype a connector using the Coda API and see how feasible / useful it is.
1 Like
Hi @Jean_Marc_Dieu and @Eric_Koleda.
I’m very much interested in that discussion, since most of my clients indeed use Excel all the time - and then they tend to use Power Query (and of course Coda) once we get to work together 
. So we get to discuss the connectivity between Coda tables and Excel / Power BI pretty often.
I’ve already developped two things that I can share here if anyone’s interested:
- functions and queries in Power Query that read Coda tables (well, actually, views, which is much more convenient and may be I’ll explain why later),
- a VBA macro that refreshes all those queries in an Excel file “properly”.
The first piece of code is useful whether you use Power Query with Power BI or Excel, and the second one obviously only if you use Excel without Power BI.
It works quite well, and the ability to query views instead of tables makes it quite a versatile solution, since you can set up a view in Coda for what you want to work with in Excel, and modify it later without needing to modify you query in Power Query. This is for instance something I had never managed to do with Notion (but could be feasible, I haven’t looked into it in a while).
HOWEVER, the big drawback is of course security, since this currently only works with harcoded API credentials in Power Query parameters… which prevents me from using that solution in most cases…
Developing a connector might work for some cases, but from what I understand from the discussion linked below, it wouldn’t work for Excel and would require additional work to publish reports online:
That discussion also mentions another approach but that requires an additional server, and I’d rather keep things as simple as possible.
So I’m aware I’m not bringing the full solution to the table, but I’d be happy to further discuss that topic!
3 Likes
does this pack not work for you?
1 Like
This pack @Christiaan_Huizer works only one way (from Excel to Coda).
The capabillity I am looking for is to pull data from Coda into Excel (2-way sync would be a nice to have but not required for my use cases).
@Troy_Larson has developed exactly that with this pack 
For now it only works for the org admin on M365 but I understand Coda Magic is going through the Microsoft trusted provider hoops to make it work for anyone.
Note the pack uses Microsoft Graph API so only works with Excel files stored in OneDrive for Business, Sharepoint or M365 Group drive.
1 Like
Salut Bertrand,
I am very interested in your solution! Sharing is caring 
For the connection with Excel, try out this pack from @Troy_Larson
1 Like
Merci Jean-Marc !
…
but for the life of me I can’t open your link to Troy’s pack, and can’t find it on his website either! 
Has it been removed in the meantime?
Anyway, I guess I should do a standalone and thorough post about it, explain some stuff, give some context, credit and additional tips - but since I suspect life will get in the way, I’ll just dump the whole thing here for the time being.
Just be aware of the two main caveats:
- As already mentioned, the token is clearly visible in Power Query as a parameter, which is quite a security concern,
- This works with a “dump all / replace all” logic. It’s not lean at all, but for my scenario it did the job so I didn’t try to optimize anything.
In Power Query, to easily retrieve data from Coda, the end game is to have one simple query for each table (or preferably each view) that looks like this:
let
DocId = "xxxxxxxxxx",
TableId = "table-yyyyyyyyyy", // this can actually be the ID of a table OR a view
DevValues = GetTable(DocId,TableId)
// anything else you need, especially type definition etc.
in
DevValues
In order to achieve that, we need:
- a text parameter for the API token → MyToken
- four functions that will retrieve the data from Coda, manage pagination and retrieve column names: GetPage, GetAllRowsInTable, GetColumnNames and finally GetTable.
GetPage
(docId as text, tableId as text, pageToken as nullable text) =>
let
BaseUrl = "https://coda.io/apis/v1/docs/" & docId & "/tables/" & tableId & "/rows",
Token = "Bearer " & MyToken,
Url = if pageToken = null then BaseUrl else BaseUrl & "?pageToken=" & pageToken,
Source = Json.Document(Web.Contents(Url, [Headers=[Authorization=Token]])),
Rows = Source[items],
NextPageToken = try Source[nextPageToken] otherwise null,
DebugData = Source,
Result = [Rows=Rows, NextPageToken=NextPageToken, DebugData=DebugData]
in
Result
GetAllRowsInTable
(docId as text, tableId as text) =>
let
FirstPage = GetPage(docId, tableId, null),
GetAllRows = List.Generate(
()=> [PageData = FirstPage, Rows = FirstPage[Rows]],
each [PageData] <> null,
each
let
NextPageToken = [PageData][NextPageToken],
NewPageData = if NextPageToken <> null then GetPage(docId, tableId, NextPageToken) else null
in
[PageData = NewPageData, Rows = if NewPageData <> null then NewPageData[Rows] else {}],
each [Rows]
),
AllRowsTable = Table.FromList(List.Combine(GetAllRows), Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
AllRowsTable
GetColumnNames
(docId as text, tableId as text) =>
let
BaseUrl = "https://coda.io/apis/v1/docs/" & docId & "/tables/" & tableId & "/columns",
Token = "Bearer " & MyToken,
Source = Json.Document(Web.Contents(BaseUrl, [Headers=[Authorization=Token]])),
Columns = Source[items],
ColumnNamesTable = Table.FromList(Columns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColumns = Table.ExpandRecordColumn(ColumnNamesTable, "Column1", {"id", "name"}, {"ColumnID", "ColumnName"})
in
ExpandedColumns
GetTable
(docId as text, tableId as text) =>
let
AllRows = GetAllRowsInTable(docId,tableId),
DevTable = Table.ExpandRecordColumn(AllRows, "Column1", {"id", "values"}, {"id", "values"}),
ColumnNamesTable = GetColumnNames(docId, tableId),
DevValues = Table.ExpandRecordColumn(DevTable, "values", ColumnNamesTable[ColumnID], ColumnNamesTable[ColumnName])
in
DevValues
It’s a bit late now here in France so we’ll get to the VBA bit next time
Hope that helps!
2 Likes
Re Troy’s pack: Oh boy, maybe it has not been published publicly yet… lol… it is coming!
Re Your code: thanks for sharing!!! I will review it when I have a minute and get back to you if I find any ways to optimize or secure it (I am not a professional coder but I do some coding from time to time…)
1 Like
Definitely interested in this integration as well. I managed to bring my Coda data into Power Query pretty easily but am looking for a way to bring in Dataflows directly into Coda. I’m currently using the MS 365 option to bring in data from a Dataflow into excel but it’s still a manual process as I have to manually sync the data (Microsoft drawback). If there was a way to connect dataflow/powerquery directly into Coda that would be a day to celebrate!
1 Like
Re Troy’s pack: good to know, now I’m looking forward to testing it!
Re my code: you’re welcome! When it comes to optimizing of course it depends on you use case but with 1200 lines and 13 columns I really didn’t feel the need. And once again, since we can work with views we could use a first one with only the UUIDs to see what’s been deleted, created or updated since our last update.
1 Like