Excel Power Query

Questions
1

Is it possible, and if so how, to use Excel’s Power Query to access Square’s data - specifically the transaction & items sold detail files.

I am able to download the CSV files but would like to be able to access the data directly without having to download these files.

Thanks much.

2

:wave: With our APIs you can pull in data and populate data from Square which includes the data from our CSV files from our various APIs. Do you have experience working with APIs? :slightly_smiling_face:

3

Hi Bryan

Thank you for your reply.

I have tried “playing” with API before, with limited success. If you can point me to some documentation on Square’s API I can try.

Will I be able to bring in the Square item sold detail report & the transaction report into Excel?

Thanks, Ron

4

That’s fantastic. The APIs that you are going to use to get the information is going to be the Payments API and the Orders API.

5

I guess I over-estimated my skills in this area. Is there a “simple” tutorial to guide me through the process of creating a link between Power Query in Excel and Squareup’s data?

Thanks much.
ron

6

Unfortunately, a guide to link between Power Query and Square isn’t currently available.

7

Okay, so this is a very similar situation I’ve been racking my brain over for the past week. I’ve gotten as far as trying to use existing custom connector templates I’ve found on the internet with Square data, but I’ve had no luck, including a variety of inconsistent errors. Has anyone since succeeded with connecting Square to Excel or Power BI? Here’s my code:

section SquareConnector;
// SquareConnector OAuth2 values
client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
token_uri = "https://connect.squareup.com/oauth2/token?grant_type=client_credentials";
authorize_uri = "https://connect.squareup.com/oauth2/authorize";
logout_uri = "https://www.squareup.com/logout";
// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;
[DataSource.Kind = "SquareConnector", Publish = "SquareConnector.Publish"]
shared SquareConnector.Contents = (url as text) =>
let
source = Json.Document(Web.Contents(url))
in
source;
// Data Source Kind description
SquareConnector = [
TestConnection = (dataSourcePath) => {"SquareConnector.Contents", dataSourcePath},
Authentication = [
OAuth = [
StartLogin = StartLogin,
FinishLogin = FinishLogin,
Refresh = Refresh,
Logout = Logout
]
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
SquareConnector.Publish = [
Beta = true,
Category = "Other",
ButtonText = {Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp")},
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = SquareConnector.Icons,
SourceTypeImage = SquareConnector.Icons
];
// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri
& "?"
& Uri.BuildQueryString(
[
response_type = "code",
client_id = client_id,
redirect_uri = redirect_uri,
content_type = "application/x-www-form-urlencoded",
state = state,
Accept = "application/json"
]
)
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
// parse the full callbackUri, and extract the Query string
parts = Uri.Parts(callbackUri)[Query],
// if the query string contains an "error" field, raise an error
// otherwise call TokenMethod to exchange our code for an access_token
result =
if (Record.HasFields(parts, {"error", "error_description"})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod("authorization_code", "code", parts[code])
in
result;
Refresh = (resourceUrl, refresh_token) =>
TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => logout_uri;
// see "Exchange code for access token: POST /oauth/token" at https://cloud.ouraring.com/docs/authentication for details
TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
grant_type = "client_credentials",
redirect_uri = redirect_uri,
client_id = client_id,
client_secret = client_secret
],
queryWithCode = Record.AddField(queryString, tokenField, code),
tokenResponse = Web.Contents(
token_uri,
[
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]
),
body = Json.Document(tokenResponse),
result =
if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;
Value.IfNull = (a, b) => if a <> null then a else b;
GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
let
prefix = Value.IfNull(scopePrefix, ""),
addPrefix = List.Transform(scopes, each prefix & _),
asText = Text.Combine(addPrefix, " ")
in
asText;
SquareConnector.Icons = [
Icon16 = {
Extension.Contents("SquareConnector16.png"),
Extension.Contents("SquareConnector20.png"),
Extension.Contents("SquareConnector24.png"),
Extension.Contents("SquareConnector32.png")
},
Icon32 = {
Extension.Contents("SquareConnector32.png"),
Extension.Contents("SquareConnector40.png"),
Extension.Contents("SquareConnector48.png"),
Extension.Contents("SquareConnector64.png")
}
];