-
Notifications
You must be signed in to change notification settings - Fork 7
/
gsheet.html
executable file
·193 lines (168 loc) · 7.18 KB
/
gsheet.html
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
<script type="text/javascript">
RED.nodes.registerType('GSheet',{
category: 'function',
color: '#0da95f',
defaults: {
creds: {value:"", type:"gauth"},
method: {value : ""},
action : {value : ""},
sheet : {value : ""},
cells : {value : ""},
flatten : {},
name : {value:""}
},
inputs:1,
outputs:1,
icon: "font-awesome/fa-table",
label: function() {
return this.name||"GSheet - " + this.method.charAt(0).toUpperCase() + this.method.slice(1);;
}
});
</script>
<script type="text/x-red" data-template-name="GSheet">
<div class="form-row">
<label for="node-config-input-creds">creds</label>
<input type="text" id="node-input-creds" >
</div>
<div class="form-row">
<label for="node-input-method">Method</label>
<select type="text" id="node-input-method" style="width:70%;">
<option value="get">Get Cells</option>
<option value="append">Append Row</option>
<option value="update">Update Cells</option>
<option value="clear">Clear Cells</option>
</select>
</div>
<div class="form-row">
<label for="node-input-sheet">SpreadsheetID</label>
<input type="text" id="node-input-sheet" placeholder="Spreadsheet ID">
</div>
<div class="form-row">
<label for="node-input-cells">Cells</label>
<input type="text" id="node-input-cells" placeholder="Sheet1!A1:B2">
</div>
<div class="form-row">
<label for="node-input-flatten"><i class="icon-tag"></i>Flatten Matrix</label>
<input type="checkbox" id="node-input-flatten">
</div>
<div class="form-row">
<label for="node-input-name"><i class="icon-tag"></i> Name</label>
<input type="text" id="node-input-name" placeholder="label">
</div>
</script>
<script type="text/x-red" data-help-name="GSheet">
<p> Read and Write Google Sheets data.</p>
<h3> Details </h4>
<h4> SpreadsheetID</h4>
The sheet ID can be found in the URL of your google sheet, for example in <code>https://docs.google.com/spreadsheets/d/1UuVIH2O38XK0TfPMGHk0HG_ixGLtLk6WoBKh4YSrDm4/edit#gid=0</code>
The ID would be <code>1UuVIH2O38XK0TfPMGHk0HG_ixGLtLk6WoBKh4YSrDm4</code>
If you want to specify the tab of the worksheet (eg Sheet1, Sheet2 etc.) This is part of the cells config.
<h4> Cells </h4>
Google sheets uses the following syntax to reference a tab and cells of the worksheet
The format is <code>Sheet1!A1:C3</code>
Where Sheet1 is the Sheet name followed by a ! then the grid of the first cell eg A1 then a : and finally the grid of the last cell eg C3
A range of cells can be a Row at A1:A5, a Column A1:E1 or even a block such as A1:C3
<h4> Flatten Matrix </h4>
If you fetch multiple rows and columns you end up with a 'matrix' that is an array of arrays, or even if you fetch a single column you end up with an array of single value arrays,
The easiest way to see it is to fetch a column like A1:A5, without flatten enabled you will get back [[A1],[A2],[A3],[A4],[A5]] instead of [A1,A2,A3,A4,A5].
<h3> Inputs </h3>
<dl class="message-properties">
<dt>payload
<span class="property-type">string | array</span>
</dt>
<dd> The data to be written to the sheet at the specified cells, a string will write to a single cell an array will write a row and a matrix will write multiple rows </dd>
<dt>sheet
<span class="property-type">string </span>
</dt>
<dd> If the Sheet ID is not specified in the config it can be set here. </dd>
<dt>cells
<span class="property-type">string</span>
</dt>
<dd> If the Cells are not specified in the config they can be set here </dd>
</dl>
<h3> Output </h3>
<h4> Get Cells </h4>
For a Get Cells method the payload will contain the requested cell data as follows:
If a single cell is requested <code>msg.payload</code> will contain the value of that cell,
If a single row or column is requested <code>msg.payload</code> will contain an array of values for that row/column
If both row and columns are request msg.payload will contain nested arrays (a matrix) as columns eg for A1:C3 :
<code>[["A1","B1","C1"],["A2","B2","C2"],["A3","B3","C3"]]</code>
<h4> Append Row or Update Cells </h4>
For update or append methods the payload contains data about the cells that have been updated
<dl class="message-properties">
<dt>spreadsheetId
<span class="property-type">string</span>
</dt>
<dd> The ID of the Spreadsheet that has been updated </dd>
<dt>updatedRange
<span class="property-type">string </span>
</dt>
<dd> The Tab and Cells that have been updated </dd>
<dt>updatedRows
<span class="property-type">int</span>
</dt>
<dd> Number of Rows Updated </dd>
<dt>updatedColumns
<span class="property-type">int</span>
</dt>
<dd> Number of Columns Updated </dd>
<dt>updatedCells
<span class="property-type">int</span>
</dt>
<dd> Number of Cells Updated </dd>
</dl>
<h4> Clear Cells </h4>
For a Clear method the response just contains the SpreadsheetID and Range that has been cleared
<dl class="message-properties">
<dt>spreadsheetId
<span class="property-type">string</span>
</dt>
<dd> The ID of the Spreadsheet that has been updated </dd>
<dt>clearedRange
<span class="property-type">string </span>
</dt>
<dd> The Tab and Cells that have been cleared </dd>
</dl>
</script>
<script type="text/javascript">
RED.nodes.registerType('gauth',{
category: 'config',
credentials: {
creds: {},
},
defaults: {
name : {}
},
label: function() {
return this.name || "Unknown"
},
oneditsave: function (){
var creds = document.getElementById('node-config-input-creds').value
try{
var auth = JSON.parse(creds);
this.name = auth.client_email;
}
catch{
this.name = "Unknown"
}
}
});
</script>
<script type="text/x-red" data-template-name="gauth">
<div class="form-row">
<label for="node-config-input-authtoken">Creds</label>
<textarea rows="15" cols="120" id="node-config-input-creds" placeholder="Google Service Accounts Credentials JSON"></textarea>
<input type="hidden" id="node-input-name">
</div>
</script>
<script type="text/x-red" data-help-name="gauth">
<p>Google Creds</p>
<h3>Details</h3>
<p>Copy JSON object for a Google Service Account Here.</p>
<p>
Create a new service account from <a target="new" href="https://console.cloud.google.com/iam-admin/serviceaccounts?_ga=2.184919274.-272657095.1578084478"> This Link</a><br>
Download a JSON credentials object for the service account.
Give that account access to the sheets API.
Share your sheet with the email address of the service account eg <code>nodered@nodered-12345.iam.gserviceaccount.com</code>
</p>
</script>