Load Google Sheet via Published CSV Link in Power Query M

Loads data from a Google Sheet that's been published to the web as a CSV, without needing an API connection.

File HandlingBeginnergoogle sheetscsvwebconnector

Power Query M code

let
    Source = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/pub?output=csv"), [Delimiter = ",", Encoding = 65001]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
in
    PromotedHeaders

Convert CSV to JSON

Notes

In Google Sheets, use File > Share > Publish to web, choose CSV, and paste that URL here.

Requirements

A Google Sheet published to the web as CSV.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Load Google Sheet via Published CSV Link.