Work with Google Sheets

How install plugin

Before installing plugin, create your service account in your google account, for this:
  1. 2.
    Click to credentials->+create credentials->service account
3. Adding role "Editor" for new account
​
​
4. After creating service account, visit section "Service account", find you account, and click to Manage key:
​
​
5. Adding new key pairs
​
​
​
Choose "JSON" format:
​
​
After creating key, google send json file to your computer, please open (!) and copy json string from this file.

Insert JSON service account key to plugin

  1. 1.
    Go to plugin section
  2. 2.
    Find "Google sheets" in "Scenario step" section
  3. 3.
    Insert you json key from google account to fielWithJSON field, for example:

How use plugin in SDK?

Basic methods

1
//we could creating document
2
var xls = $D.sheet.create("test")
3
//or you could open existing document
4
//var xls = "https://docs.google.com/spreadsheets/d/XXX-XXX-SSS/edit"
5
​
6
//now, we could read this xls document first sheet (first tab in XLS)
7
var data = $D.sheet.read(xls, "Sheet1")
8
//output: data = [ [1,2,3] ]
9
​
10
​
11
//also, we could write document as
12
$D.sheet.write(xls, "Sheet1", [ [1,2,3], [4,5,6] ])
13
​
Copied!

Additional methods, similar as Google Scripts

1
var ss = $D.sheet.open(xls) //xls link like this: "https://docs.google.com/spreadsheets/d/XXX-XXX-SSS/edit"
2
​
3
//get first page in the sheets
4
var sheet = ss.getSheets()[0];
5
//show getLastRow and getLastColumn in sheet
6
$D.console.log("sheet.getLastRow:" + sheet.getLastRow())
7
$D.console.log("sheet.getLastColumn:" + sheet.getLastColumn())
8
​
9
//read values
10
var values = sheet.getRange('B2:D4').getValues()
11
for(var r = 0; r<values.length; r++) {
12
var row = values[r]
13
for(var c = 0; c<row.length; c++) {
14
var value = row[c]
15
$D.console.log("row:" + r + " c:" + c + " value:" + value)
16
}
17
}
18
​
19
//write as google scripts
20
var values = [
21
[ "2.000", "1,000,000", "$2.99" ]
22
];
23
var range = sheet.getRange("B2:D2");
24
range.setValues(values);
Copied!
​