-
Notifications
You must be signed in to change notification settings - Fork 22
/
PQGoogleSpreadsheet.pq
111 lines (88 loc) · 3.77 KB
/
PQGoogleSpreadsheet.pq
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
// This file contains your Data Connector logic
section PQGoogleSpreadsheet;
// Constants
client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "https://preview.powerbi.com/views/oauthredirect.html";
windowWidth = 1200;
windowHeight = 1000;
[DataSource.Kind="PQGoogleSpreadsheet", Publish="PQGoogleSpreadsheet.UI"]
shared PQGoogleSpreadsheet.Contents = Value.ReplaceType(PQGoogleSpreadsheetCore.Contents, type function (#"Google Spreadsheet url" as Uri.Type) as any);
shared PQGoogleSpreadsheetCore.Contents = (url as text) =>
let
finalUrl = Text.BeforeDelimiter(url, "/", {0, RelativePosition.FromEnd}) & "/export?format=xlsx",
content = Web.Contents(finalUrl),
excel = Excel.Workbook(content, null, true)
in
excel;
// Data Source Kind description
PQGoogleSpreadsheet = [
Authentication = [
OAuth = [
StartLogin = StartLogin,
FinishLogin = FinishLogin,
Logout = Logout,
Refresh = Refresh,
Label = "Google Spreadsheet Auth"
]
],
Label = "Google Spreadsheet Connector"
];
StartLogin = (resourceUrl, state, display) =>
let
AuthorizeUrl = "https://accounts.google.com/o/oauth2/v2/auth?" & Uri.BuildQueryString([
scope = "https://www.googleapis.com/auth/drive.readonly",
access_type = "offline",
include_granted_scopes = "true",
client_id = client_id,
state = state,
redirect_uri = redirect_uri,
response_type = "code"])
in
[
LoginUri = AuthorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = windowHeight,
WindowWidth = windowWidth,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
Parts = Uri.Parts(callbackUri)[Query]
in
TokenMethod(Parts[code]);
TokenMethod = (code) =>
let
Response = Web.Contents("https://www.googleapis.com/oauth2/v4/token", [
Content = Text.ToBinary(Uri.BuildQueryString([
client_id = client_id,
client_secret = client_secret,
code = code,
grant_type = "authorization_code",
access_type = "offline",
redirect_uri = redirect_uri])),
Headers=[#"Content-type" = "application/x-www-form-urlencoded", #"Accept" = "application/json"],
ManualStatusHandling = {400}
]),
Body = Json.Document(Response),
Result = if Record.HasFields(Body, {"error", "error_description"}) then
error Error.Record(Body[error], Body[error_description], Body)
else
Body
in
Result;
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => "https://accounts.google.com/o/oauth2/revoke?token=" & token;
// Data Source UI publishing description
PQGoogleSpreadsheet.UI = [
Beta = true,
Category = "Other",
ButtonText = { "Google Spreadsheet Connector", "Google Spreadsheet Connector Help" },
LearnMoreUrl = "http://skolenipowerbi.cz/",
SourceImage = PQGoogleSpreadsheet.Icons,
SourceTypeImage = PQGoogleSpreadsheet.Icons
];
PQGoogleSpreadsheet.Icons = [
Icon16 = { Extension.Contents("PQGoogleSpreadsheet16.png"), Extension.Contents("PQGoogleSpreadsheet20.png"), Extension.Contents("PQGoogleSpreadsheet24.png"), Extension.Contents("PQGoogleSpreadsheet32.png") },
Icon32 = { Extension.Contents("PQGoogleSpreadsheet32.png"), Extension.Contents("PQGoogleSpreadsheet40.png"), Extension.Contents("PQGoogleSpreadsheet48.png"), Extension.Contents("PQGoogleSpreadsheet64.png") }
];